Use a Power BI gateway to connect to on-premises data sources

  • 2 minutes

A Power BI Gateway is a bridge that connects on-premises data sources to Power BI, Power Automate, Power Apps, and other Microsoft cloud services. On-premises data sources are those that aren't stored in the cloud. It enables secure data transfer between on-premises systems and Microsoft cloud services without moving the data to the cloud.

Overview of gateway

Two types of on-premises gateways are:

  • Standard mode - Allows multiple users to connect to multiple on-premises data sources and is suitable for complex scenarios.

  • Personal mode - Allows one user to connect to data sources. This type of gateway can only be used with Power BI. It can't be shared with other users, making it suitable for situations where you're the sole report creator in your organization. You'll install the gateway on your local computer, which needs to stay online for the gateway to work.

Note

We won't cover virtual network (VNet) data gateways in this module. VNet data gateways are used to connect to Azure Virtual Networks and are typically used in more complex scenarios.

Use an on-premises gateway

Before you can connect to your on-premises data source, you need to install the on-premises data gateway, and then configure it to suit your organizational needs. An admin in your organization usually completes this task.

When the on-premises gateway is installed and configured on a server, the admin starts the gateway and then signs in using a Microsoft 365 organization account.

Sign in to gateway

When you're working in the cloud and interacting with an element that is connected to an on-premises data source, the following actions occur:

  1. The cloud service creates a query and the encrypted credentials for the on-premises data source. The query and credentials are sent to the gateway queue for processing.

  2. The gateway cloud service analyzes the query and pushes the request to Microsoft Azure Service Bus.

  3. Service Bus sends the pending requests to the gateway.

  4. The gateway gets the query, decrypts the credentials, and then connects to one or more data sources with those credentials.

  5. The gateway sends the query to the data source to be run.

  6. The results are sent from the data source back to the gateway and then to the cloud service. The service then uses the results.

How gateway works

Add a data source to a gateway

Before you can set up scheduled refreshes for your reports which require access to on-premises data sources, you need to add the data source to the gateway. This is usually done by the admin in your organization.

In the Power BI Service, select the Settings Settings gear icon icon, and then select Manage connections and gateways.

Screenshot that shows selecting Manage connections and gateways.

Then, you can create a new data source or edit an existing one. Provide the name of the gateway cluster, a name for the connection and a connection type (for example: SQL Server). You'll also need to provide the server name and database name, and the authentication method. The authentication method can be either Basic, Windows, OAuth 2.0, or Service Principal.

Screenshot that shows creating a new connection on the gateway.

Troubleshoot service connectivity

Cloud services, such as SharePoint, do not require a gateway because the data is already in the cloud. You only need to provide your authorization credentials to set up a data source connection.

If your report fails to refresh, ensure that your data source credentials are up to date.

Screenshot showing check data source credentials.

If your data source credentials are not up to date, you'll need to take further action to investigate and resolve the issue.

For more information, see Troubleshooting refresh scenarios.

To find the current data center region that you're in, see Set the data center region.