0

I'm an experienced developer who knows very little about Power BI. So we've hired some consultants to implement our Power BI screens. And I provided them with a read-only login to my SQL Server database.

It works okay, but when we complained that the data never updates, they are now telling us we should set up a VM to "assure that at the refreshing moment, the scheduled job is not going to fail. VM is always connected, so even during holidays, weekends, the data will be always refreshing."

They followed up with "If the database is on-premise, we need a gateway to connect power bi to the database. If the machine, where the gateway is installed is off, power bi can not connect to the database. So, we need a VM to assure that the gateway is always on."

But this makes zero sense to me. Our database is not on-premise if it's on the Internet and we've given them a connection string. They should be able to update the data at any time.

Can anyone tell me what I'm missing here? I'm starting to question these guys' knowledge. Is it this complicated for Power BI to automatically update its data?

Jonathan Wood
  • 65,341
  • 71
  • 269
  • 466

1 Answers1

1

Some data sources require a Data Gateway, even if you put them on the open internet. Data sources that are typically deployed on private networks, or data sources that require 3rd party drivers require the Power BI On-Prem Gateway for refresh. See the list here.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Thanks, but this is so confusing to me. Every column is *Yes* for SQL Server. But if a *Live Connection* is supported, then why would *Gateway (required)* also be *Yes*? Is there something about Power BI that I don't understand that prevents it from simply using the live connection? – Jonathan Wood Oct 13 '21 at 13:13
  • So it's not Azure SQL Database? – David Browne - Microsoft Oct 13 '21 at 13:15
  • No. It's simply an instance of SQL Server connected to the Internet. But maybe I could convince the company to put it on Azure if it would make crazy requirements such as a VM go away. – Jonathan Wood Oct 13 '21 at 13:15
  • It's not advisable or supported for Power BI to connect to "regular" SQL Server directly over the internet. Data sources that are typically deployed on private networks, or data sources that require 3rd party drivers require the Power BI On-Prem Gateway for refresh. Also you can put the gateway on an Azure VM and use Azure Automation to start the VM only when you need to refresh. – David Browne - Microsoft Oct 13 '21 at 13:21
  • Well, that's a surprise, and disappointing. Know of any links that would explain to me how a VM solves the problem? Because I don't see how a VM changes this restriction. – Jonathan Wood Oct 13 '21 at 13:26
  • The Power BI Gateways gets installed on a network with direct connectivity to the data source, but it makes an outbound connection to the Power BI Service. So Power BI (and the rest of the internet) doesn't have direct access to your databases. See eg https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-onprem-indepth – David Browne - Microsoft Oct 13 '21 at 13:32
  • This is not always true. We have a database extract to .CSV files that are automatically uploaded to our OneDrive workspace where PowerBi reports can see/use it without the Data Gateway which we want to avoid. – GoinOff Oct 13 '21 at 16:05
  • 1
    Not all data sources require a gateway. In general Cloud Services, like OneDrive, SharePoint Online, don't require a gateway. See the linked list of data sources for which ones require a gateway. – David Browne - Microsoft Oct 13 '21 at 16:16