Database connection via Power BI Service

[bpasol]

So I'm dealing with the following situation - I have published a PBIX consisting of a direct query connection to an on prem SQL Server 2014 database to the Power BI Service. I've setup the enteprise gateway with a connection to said SQL Server instance. Everything is working when accessing the dataset from app.powerbi.com and Power BI Desktop.

My question is - in Power BI Desktop, when getting data via this dataset published on Power BI service, is the client conecting directly to the SQL Server host, or to gateway or to the "cloud"? Basically via what path does the data reach the client.

Thank you!

[Anonymous]

Directly to the host

[bpasol]

Thank you! Do you know of any product documentation that has this explicitly covered?

[Anonymous]

The Power BI desktop is not using the Power BI Gateway nor the data stored in the Power BI Service hence if you refresh it needs to connect directly. You can see this simply by opening the report in your Power BI Desktop client and go to Data Source Settings (under Edit Query button on the toolbar). Select the data source in question and see that it's connecting directly to the database.

[bpasol]

Thanks for the reply, but I think it's not accurate in my context. The data source is the power bi service not the database itself, the options to edit the data source are greyed out.

In the meantime I found the correct answer - the client retrieves the data via the cloud, not directly from the gateway, definetly not from the db directly. 

Full explanation in the How the gateway works section of the following:

https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem

[Anonymous · Accepted solution]

Perhaps I was misunderstanding your original problem, if you are downloading the dataset (Report) from the PowerBI Portal then yes, you will be downloading the data last refreshed in the portal. If you press "Refresh" in Power BI Desktop, it will connect straight to the Sql Server Database, it will NOT go through the Gateway and it will NOT connect to Power BI Service (Cloud) and "re-download" the data from there.

The documentation for the Gateway is only relevant for Power BI Service, not Desktop.

But if you found the information you need, then that great.

[bpasol]

Spoiler

My case is about direct query mode, not import data.

Just to be clear for anybody else interested this thread - in Power BI Desktop when getting data via a direct query dataset published on power bi service, the data is retrieved via the cloud using the credentials configured for the connection in the gateway. Confirmed this today by disconnecting from the corp domain (i.e. making the on prem db host unavaible to my client machine) and creating a new report in the Power BI Desktop from the dataset on Power BI Service and it works without issues. 

[Anonymous]

If you get this to work that's excellent - problem solved.

I tried like you describe and it doesn't work for me. I don't need the exact feature so I don't think I will dig deeper, but this does go against everything I've read about this - see the link to an article below where it's clearly stated that this doesn't work this way (last paragraph). It's from 2016, perhaps there are some configurations now that invalidate this - but I haven't seen any mentioned.

https://community.powerbi.com/t5/Community-Blog/Connecting-Power-BI-to-On-Premise-data-with-Power-BI...