Manage a SQL Server data source
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.
After you install an on-premises data gateway, you can add data sources to use with the gateway. This article describes how to add a SQL Server data source to an on-premises data gateway to use for scheduled refresh or DirectQuery.
Add a data source
Follow these instructions to add a SQL Server data source to your on-premises data gateway.
Note
When you use DirectQuery, the gateway supports only SQL Server 2012 SP1 and later.
On the New connection screen, select On-premises. Enter the Gateway cluster name and new Connection name, and under Connection type, select SQL Server.
Fill in the Server and Database information for the data source.
Under Authentication Method, choose either Windows or Basic. Choose Basic if you plan to use SQL authentication instead of Windows authentication. Then enter the credentials to use for this data source.
All queries to the data source run using these credentials unless you configure and enable Kerberos single sign-on (SSO) for the data source. With SSO, datasets use the current Power BI user's SSO credentials to execute the queries.
For more information about storing and using credentials, see:
Configure the Privacy level for your data source. This setting controls how data can be combined for scheduled refresh only. The privacy level setting doesn't apply to DirectQuery. To learn more about privacy levels for your data source, see Privacy levels (Power Query).
Select Create.
You see a success message if the creation succeeds. You can now use this data source for scheduled refresh or DirectQuery against an on-premises SQL Server.
For more information about how to add a data source, see Add a data source.
Use the data source
After you create the data source, it's available to use with either DirectQuery connections or through scheduled refresh.
Server and database names must match
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 exactly.
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 SERVER\INSTANCE in the data source you configure for the gateway. This requirement holds for both DirectQuery and scheduled refresh.
Use the data source with DirectQuery connections
Make sure that the server and database names match between Power BI Desktop and the configured data source for the gateway. Also, to be able to publish DirectQuery datasets, your users must appear under Users in the data source list.
You select the DirectQuery connection method in Power BI Desktop when you first connect to data. For more information about how to use DirectQuery, see Use DirectQuery in Power BI Desktop.
After you publish reports, either from Power BI Desktop or by getting data in Power BI service, your SQL Server on-premises data connection should work. It might take several minutes after you create the data source in the gateway to be able to use the connection.
Use the data source with scheduled refresh
If your account is in the Users column of the data source configured within the gateway, and the server name and database name match, you see the gateway as an option to use with scheduled refresh.
Related content
- Connect to on-premises data in SQL Server
- Troubleshoot the on-premises data gateway
- Troubleshoot gateways - Power BI
- Use Kerberos for single sign-on (SSO) from Power BI to on-premises data sources
More questions? Try asking the Power BI Community.