Manage data sources

Note

We recently revised the on-premises data gateway docs. We split them 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 has its own requirements. A gateway can be used for a single data source or multiple data sources. For this example, we show you how to add SQL Server as a data source. The steps are similar for other data sources.

Most data sources management operations can be performed by using APIs as well. For more information, see REST APIs (Gateways).

Add a data source

  1. In the upper-right corner of the Power BI service, select the gear icon Settings gear icon > Manage gateways.

    Manage gateways

  2. Select a gateway and then select Add data source. Or, go to Gateways > Add data source.

    Add data source

  3. Select the Data Source Type.

    Select SQL Server

  4. Enter information for the data source. For this example, it's Server, Database, and other information.

    Data source settings

  5. For SQL Server, you choose an Authentication Method of Windows or Basic (SQL Authentication). If you choose Basic, enter the credentials for your data source.

  6. Under Advanced settings, optionally configure the privacy level for your data source (doesn't apply to DirectQuery).

    Advanced settings

  7. Select Add. You see Connection Successful if the process succeeds.

    Connection successful

You can now use this data source to include data from SQL Server in your Power BI dashboards and reports.

Remove a data source

You can remove a data source if you no longer use it. Removing a data source breaks any dashboards and reports that rely on that data source.

To remove a data source, go to the data source and then select Remove.

Remove a data source

Use the data source for scheduled refresh or DirectQuery

After you create the data source, it's available to use with either DirectQuery connections or through scheduled refresh.

Note

Server and database names must match between Power BI Desktop and the data source within the on-premises data gateway.

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 in the data source configured for the gateway.

If you're listed in the Users tab of the data source configured in the gateway, and the server and database name match, you see the gateway as an option to use with scheduled refresh.

Gateway connection

Warning

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.

Limitations

OAuth is a supported authentication scheme only for custom connectors with the on-premises data gateway. You can't add other data sources that require OAuth. If your dataset has a data source that requires OAuth and this data source isn't a custom connector, you won't be able to use the gateway for scheduled refresh.

Manage users

After you add a data source to a gateway, you give users and email-enabled security groups access to the specific data source (not the entire gateway). The data source users list controls only who is allowed to publish reports that include data from the data source. Report owners can create dashboards, content packs, and apps, and then share those items with other users.

You can also give users and security groups administrative access to the gateway.

Add users to a data source

  1. In the upper-right corner of the Power BI service, select the gear icon Settings gear icon > Manage gateways.

  2. Select the data source where you want to add users.

  3. Select Users, and enter a user from your organization who you want to grant access to the selected data source. For example, in the following screen, you add Maggie and Adam.

    Users tab

  4. Select Add, and the added member's name shows up in the box.

    Add user

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.

Remove users from a data source

On the Users tab for the data source, you can remove users and security groups that use this data source.

Remove user

Store encrypted credentials in the cloud

When you add a data source to the gateway, you must provide credentials for that data source. All queries to the data source will run by using these credentials. The credentials are encrypted securely. They use symmetric encryption so that they can't be decrypted in the cloud before they're stored in the cloud. The credentials are sent to the machine that runs the gateway, on-premises, where they're decrypted when the data sources are accessed.

List of available data source types

The on-premises data gateway supports the following data sources for Power BI. In addition to on-premises data sources, sources behind a firewall, VPN, or virtual network might also need a data gateway.

Data source Live/DirectQuery Manual or scheduled refresh (user-configured)
Amazon Redshift Yes Yes
Analysis Services Yes Yes
AtScale cubes Yes Yes
Azure Active Directory No Yes
Azure Blob storage No Yes
Azure DevOps Server No Yes
Azure Table storage No Yes
BI Connector Yes Yes
Denodo Yes Yes
Dremio Yes Yes
EmigoDataSourceConnector No Yes
Essbase Yes Yes
Exasol Yes Yes
File No Yes
Folder No Yes
Paxata No Yes
IBM DB2 Yes Yes
IBM Informix Database No Yes
IBM Netezza Yes Yes
Impala Yes Yes
Jethro ODBC Yes Yes
Kyligence Enterprise Yes Yes
MarkLogic ODBC Yes Yes
Microsoft Graph Security No Yes
MySQL No Yes
ODBC No Yes
OData No Yes
OLE DB No Yes
Oracle Yes Yes
PostgreSQL No Yes
QubolePresto Yes Yes
Quick Base Connector No Yes
SAP Business Warehouse Message Server Yes Yes
SAP Business Warehouse Server Yes Yes
SAP HANA Yes Yes
SQL Server Yes Yes
SharePoint No Yes
Snowflake Yes Yes
Spark Yes Yes
SurveyMonkey No Yes
Sybase No Yes
TeamDesk.Database No Yes
Teradata Yes Yes
Vertica Yes Yes
Web No Yes
Workforce Dimensions No Yes

Next steps

More questions? Try the Power BI Community.