Power Bi Desktop Use Gateway Data Source
Emmanuelle Riker
We've split the on-premises data gateway docs into content that's specific to Power BI and general content that applies to all services that the gateway supports. You're currently in the Power BI content. To provide feedback on this article, or the overall gateway docs experience, scroll to the bottom of the article.
Power BI supports many on-premises data sources, and each source has its own requirements. You can use a gateway for a single data source or multiple data sources. For this example, you learn how to add SQL Server as a data source. The steps are similar for other data sources.
Optionally, under Single sign-on, you can configure single sign-on (SSO) for your data source. Depending on your organization settings, for DirectQuery-based reports, you can configure Use SSO via Kerberos for DirectQuery queries, Use SSO via Kerberos for DirectQuery And Import queries or Use SSO via Microsoft Entra ID for DirectQuery queries. You can configure Use SSO via Kerberos for DirectQuery And Import queries for refresh-based reports.
If you use Use SSO via Kerberos for DirectQuery queries and use this data source for a DirectQuery-based report, the report uses the credentials of the user that signs in to the Power BI service. A refresh-based report uses the credentials that you enter in the Username and Password fields and the Authentication method you choose.
When you use Use SSO via Kerberos for DirectQuery And Import queries, you don't need to provide any credentials. If this data source is used for DirectQuery-based reports, the report uses the user mapped to the Microsoft Entra user that signs in to the Power BI service. A refresh-based report uses the dataset owner's security context.
For more information about Use SSO via Kerberos for DirectQuery queries and Use SSO via Kerberos for DirectQuery And Import queries, see Overview of single sign-on (SSO) for gateways in Power BI.
If you use Use SSO via Microsoft Entra ID for DirectQuery queries and use this data source for a DirectQuery-based report, the report uses the Microsoft Entra token of the user who signs into the Power BI service. A refresh-based report uses the credentials that you enter in the Username and Password fields and the Authentication method you choose. The Use SSO via Microsoft Entra ID for DirectQuery queries option is available only if the tenant admin allows Microsoft Entra SSO via the on-premises data gateway, and for the following data sources:
After you create the data source, it's available to use with DirectQuery connections or through scheduled refresh. You can learn more about setting up scheduled refresh in Configure scheduled refresh.
The link between your dataset and the data source in the gateway is based on your server name and database name. These names must match. For example, if you supply an IP address for the server name in Power BI Desktop, you must use the IP address for the data source in the gateway configuration. If you use SERVER\INSTANCE in Power BI Desktop, you must use the same format in the data source you configure for the gateway.
If your account is listed in the Users tab of the data source configured in the gateway, and the server and database name match, you see the gateway listed as Running under Gateway connections in the Settings for your data source. You can select Scheduled refresh to set up scheduled refresh for the data source.
If your dataset contains multiple data sources, each data source must be added in the gateway. If one or more data sources aren't added to the gateway, you won't see the gateway as available for scheduled refresh.
After you add a data source to a gateway, you give users and security groups access to the specific data source, not the entire gateway. The access list for the data source controls only who is allowed to publish reports that include data from the data source. Report owners can create dashboards and apps, and then share those items with other users.
When you add a data source to the gateway, you must provide credentials for that data source. All queries to the data source run by using these credentials. The credentials are encrypted securely with symmetric encryption, so that they can't be decrypted in the cloud. The credentials are sent to the machine that runs the on-premises gateway. This machine decrypts the credentials when the data sources are accessed.
We use our Azure tenancy to define resource groups with a number of Data Gateways to our on-premise SQL Server databases. These are currently being used for LogicApps flows and seem to work well. Now what I would like to do is re-use those Azure Data Gateways to feed data into our Power BI environment. However I cannot seem to find a pathway in the "Get Data" option in Power BI Desktop that allows me to find that Azure based gateway and pull data through it.
Can anyone give me a method/path for discovering Azure based gateway resources within the Power BI Desktop environment? When I look at Azure via the Azure Portal I see that I have been granted access to the gateway itself via my Office 365 ID and should be permitted to use the gateways. I log in to Power BI with the same credentials that I use for Azure.
All the articles online talk about using Power BI Online to Install a new gateway. I don't need to do that. We already have gateways defined and configured correctly in Azure. I just need to able to link them to Power BI Desktop to define the queries that wil power the visualisations.
I'm sure I'm not doing anything particularly special, I just can't find any hints as to how it works (or how it is intended to work) getting data through Azure Data Gateways and into Power BI Desktop.
I assume that Microsoft would not build Azure Gateways and PowerBI Desktop as products that can't talk to each other so I assume I'm asking the wrong question. Any advice as to what I should be asking or doing?
After you installed the Azure data gateway and signed in with your Power BI service account, the registered data gateway will display when you signed in Power BI service -> Manage Gateways. You can create data sources under data gateway, see: Manage your data source - SQL Server.
The data gateway doesn't work with Power BI desktop. Desktop is a Power BI authoring tool, we can get data from on-premise data sources in Power BI Desktop to create a report, then publish the report to Power BI service, we need utilize the data gateway to build connection between Power BI and on-premise data sources.
Thanks @v-qiuyu-msft for the reply regarding Power-BI Desktop not needing to access the gateway. I think I understand what you are saying. Design the reports internally using a direct connection to the internal SQL Server but re-direct to the gateway when in the Power BI Service. Cool. Got it. So I built my reports and connections to the SQL Server database. Works perfectly and I can refresh the data in PowerBI Desktop. I then publish the PBIX file to PowerBI Service.
However when I go into PowerBI Service and try to update my dataset to use the existing Azure gateway, it says "You don't have any Gateways" and only offers to Download and Install one. I can't find a way of referencing my pre-existing Azure Gateway in Power BI Service for the data my report needs.
Please sign in data gateway with your Power BI service account, then go to Manage Gateways to create a SQL Server data source use the same connection information (server name, database name) as in desktop. Then go to dataset settings, the data gateway should be available. See: Add a data source.
I was able to complete the install (not without difficulty), and now on app.powerbi.com I can go to Settings > Manage Gateways, and create a Data Source to an on-prem SQL server. (In case it matters, I'm using Basic (SQL) credentials, not passing through user credentials. I don't want the users to know this username/password.) Testing the data source shows "Connection Successful". I've added myself (and several other people) to the Users tab.
Both appear to use the technique Mike is suggesting: create the report in Power BI Desktop with a direct data source, and when publishing (I presume) it sees the matching server and database name, and associates the report with the gateway data source. From the second liink above:
Well, apparently my power user is happy (at least the one that has direct database access), it did allow him to create and publish a report that we can confirm is using the gateway. We will play with it some more.
That method sounds workable for me, since I (as a DBA) have direct access to the SQL server, but part of the objective here was to give a handful of power users the ability to create reports from a datasource they can't otherwise query directly.
I'm still not entirely sure whether that's even possible, or whether I just haven't found the right way to make that happen. I probably just need to download the Power BI Desktop and play with it, see if it allows you to choose a gateway data source while designing.
You're running into a common dilemma that DBA's face. In order for the users to build reports, they will need access to some form of the data source from Power BI desktop.
As you say I think it's a good idea that you play with Power BI desktop and get a sense of how it connects to SQL Server and how your users will interact with it.
All the best,