Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
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. This article also tells you how to remove a data source, use it with scheduled refresh or DirectQuery, and manage user access.
You can also do most data source management operations by using APIs. For more information, see REST APIs (Gateways).
If you don't have a gateway installed, install an on-premises data gateway to get started.
From the page header in the Power BI service, select the Settings icon, and then select Manage connections and gateways.
Select New at the top of the screen to add a new data source.
On the New connection screen, select On-premises, provide the Gateway cluster name you want to create the connection on, provide a Connection name, and select the Data Source Type. For this example, choose SQL Server.
Enter information about the data source. For SQL Server, provide the Server and Database.
Note
To use the data source for Power BI reports and dashboards, the server and database names must match between Power BI Desktop and the data source you add to the gateway.
Select an Authentication method to use when connecting to the data source: Basic, Windows, or OAuth2. For SQL Server, choose Windows or Basic (SQL Authentication). Enter the credentials for your data source.
If you selected OAuth2 authentication method:
If you selected Windows authentication method, make sure that account has access on the machine. If you're not sure, add NT-AUTHORITY\Authenticated Users (S-1-5-11) to the local machine Users group.
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 on-premises data 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:
For more information about Use SSO via Microsoft Entra ID for DirectQuery queries, see Microsoft Entra single sign-on (SSO) for data gateway.
Note
SSO for Import queries is available only for the SSO data sources that use Kerberos constrained delegation.
Under General > Privacy level, optionally configure a privacy level for your data source. This setting doesn't apply to DirectQuery.
Select Create. Under Settings, you see Created new connection if the process succeeds.
You can now use this data source to include data from SQL Server in your Power BI dashboards and reports.
You can remove a data source if you no longer use it. If you remove a data source, any dashboards and reports that rely on that data source stop working.
To remove a data source, select the data source from the Connections screen in Manage Connections and Gateways, and then select Remove.
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.
Important
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.
You can also give users and security groups administrative access to the gateway.
Note
Users with access to the data source can associate datasets to the data source, and connect, based on either the stored credentials or SSO you selected while creating a data source.
From the page header in the Power BI service, select the Settings icon, and then select Manage connections and gateways.
Select the data source where you want to add users.
Select Manage users from the top ribbon
On the Manage users screen, enter the users and/or security groups from your organization who can access the selected data source.
Select the new user name, and select the role to assign: User, User with resharing, or Owner.
Select Share, and the added member's name is added to the list of people who can publish reports that use this data source.
Remember that you need to add users to each data source that you want to grant access to. Each data source has a separate list of users. Add users to each data source separately.
On the Manage users tab for the data source, you can remove users and security groups that use this data source.
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.
For information about which data sources the on-premises data gateway supports, see Power BI data sources.
Note
MySQL is not supported on the personal on-premises data gateway.
More questions? Try the Power BI Community.
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayTraining
Module
Manage semantic models in Power BI - Training
With Microsoft Power BI, you can use a single semantic model to build many reports. Reduce your administrative overhead even more with scheduled semantic model refreshes and resolving connectivity errors.
Certification
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.
Documentation
On-premises data gateway - Power BI
This article provides an overview of the on-premises data gateway and its functionality in Microsoft cloud services.
Guidance for deploying a data gateway for the Power BI service - Power BI
Learn best practices and considerations for deploying a gateway for the Power BI service in your network environment.
Use a personal gateway in Power BI - Power BI
See information about the Power BI on-premises data gateway (personal mode) that you can use for connecting to on-premises data.