On-premises data gateway FAQ

General

Do I need a gateway for cloud data sources, such as Azure SQL Database?

No, services can generally connect to cloud data sources without a gateway. However, you might need a data gateway if your data sources are behind a firewall, require a VPN, or are on virtual networks.

Why do I get the errors "InvalidConnectionCredentials" or "AccessUnauthorized" when accessing cloud data sources using OAuth2 credentials even though the credentials are updated recently.

When using OAuth2 credentials, the gateway currently doesn't support refreshing tokens automatically when access tokens expire (one hour after the refresh started). This limitation for long running refreshes exists for VNET gateways and on-premises data gateways.

What are the requirements for the gateway?

Review the requirements section in the installation article.

Does the gateway have to be installed on the same machine as the data source?

No, the gateway connects to the data source by using the provided connection information. In this sense, consider the gateway as a client application. The gateway just needs to connect to the specified server.

How many releases of the on-premises data gateway does Microsoft actively support?

Currently, Microsoft actively supports only the last six releases of the on-premises data gateway. We release a new update for data gateways every month.

Are there any licensing prerequisites required to install gateways?

There are no licensing restrictions for installing and registering a gateway. But any cloud service might have licensing restrictions on how gateways are used within that service. For Power BI, a premium license is required. All Fabric licenses support the gateway. Other services might have licensing restrictions of their own as well.

Are there any requirements for network bandwidth?

Check that your network connection has good throughput. Each environment is different, and throughput depends on the amount of data that is sent. To ensure a minimum level of throughput between your on-premises data source and Azure datacenters, use Azure ExpressRoute. To help measure your throughput, you can use the Azure Speed Test app.

Where are my credentials stored?

The credentials that you enter for a data source are encrypted and stored in the gateway cloud service. The credentials are decrypted at the gateway on premises. For more information about credential encryption in Power BI, go to Power BI security whitepaper.

What is the actual Windows service called?

On your local computer, in the Services app, the service is called "On-premises data gateway service". In Task Manager, on the Services tab, the service name is "PBIEgwService". By default, the Windows service uses "NT SERVICE\PBIEgwService" as the Service SID (SSID).

Can the gateway Windows service run with a Microsoft Entra ID account?

No, the Windows service needs a valid Windows account.

Are there any inbound connections to the gateway from the cloud?

No, the gateway uses outbound connections to Azure Relay.

What if I block outbound connections? What do I need to open?

Do I need to unblock the Azure Datacenter IP list? Where do I get the list?

If you block outbound IP traffic, you might need to unblock the Azure Datacenter IP list. The gateway communicates with Azure Relay by using an IP address and a fully qualified domain name. The Azure Datacenter IP list is updated weekly. For more information, go to Enable outbound Azure connections.

What is the latency for running queries from the gateway to a data source? What is the best architecture?

To avoid network latency, install the gateway as close as possible to the data source. If you can install the gateway on the actual data source, this closer location minimizes latency.

Also, consider the proximity to the Azure datacenters. For example, if your service uses the West US datacenter, and you have SQL Server hosted in an Azure virtual machine, you might also want your Azure VM in the West US region. This configuration minimizes latency and avoids egress charges on the Azure VM.

How are results sent back to the cloud?

The results are sent through Azure Relay. For more information, go to On-premises data gateway architecture.

Can I place the gateway in a perimeter network (also known as DMZ, demilitarized zone, and screened subnet)?

The gateway requires connectivity to the data source. So, if the data source isn't reachable in your perimeter network, the gateway might not have access.

For example, assume your SQL Server computer isn't in your perimeter network. Also, assume you can't connect to that computer from the perimeter network. If you place the gateway in your perimeter network, the gateway can't reach the SQL Server computer.

If the server on which the gateway is installed is only using an IPv6 address and connects with a data source (for example, Azure SQL/File Server/Database on Azure VM) using an IPv6 address only, is it possible to communicate from Power BI service to those data sources through the gateway?

Yes.

If the server on which the gateway is installed is using dual stack to be assigned both IPv4 and IPv6 addresses, would IPv4 or IPv6 be the priority for gateway communication?

This priority depends on Windows or the relevant data source drivers. This behavior is configurable in both Windows and various drivers, but isn't under the gateway's control.

Does the gateway support cross-tenant Microsoft Entra ID access?

No, cross-tenant Microsoft Entra ID access isn't supported. A Power BI semantic model in a tenant can't connect to data sources from a different tenant using a Microsoft Entra ID account. This limitation applies whether the data source uses an on-premises data gateway or not. To overcome this limitation, use an authentication type other than OAuth.

Can I force the gateway to use HTTPS traffic with Azure Relay instead of TCP?

Yes, for more information, go to Force HTTPS communication with Azure Relay. Turning on this feature has little effect on performance.

Are the on-premises data gateway and Data Management Gateway, which is used by Azure Machine Learning Studio and Azure Data Factory, the same thing?

No, they're different products. To get more information about Data Management Gateway, which is now called Self-hosted Integration Runtime, go to Create and configure a self-hosted integration runtime.

Can the person who sets up the gateway in the Azure portal be different from the one who installs that gateway?

Yes, you must use PowerShell to add other owners to the same gateway. These users can create the gateway in the Azure portal. However, they should connect to the portal and the gateway by using the same tenant.

Does Azure ExpressRoute eliminate the need for a gateway?

No. A gateway is still required when connecting to on-premises data sources.

How can I keep my data within the same region while using an on-premises data gateway?

High availability and disaster recovery

Are there any plans for enabling high-availability scenarios with the gateway?

To help avoid a single point of failure, you can set up on-premises data gateways as clusters for high availability. By default, cloud services such as Power Apps and Power BI use the primary gateway and fall back to the secondary gateway if the primary is unavailable.

What options are available for disaster recovery?

When you install the gateway, you supply a recovery key. You can use the key to restore or migrate a gateway.

What is the benefit of the recovery key?

The key provides a way to add a new gateway to a cluster or to migrate, recover, or take over a gateway.

Troubleshooting

For more information, go to Troubleshoot the on-premises data gateway.

Where are the gateway logs located?

How can I tell what queries are sent to the on-premises data source?

You can enable query tracing by turning on additional logging. The logs include the queries that are sent. Remember to turn off query tracing when you're done troubleshooting. Having query tracing enabled causes the logs to be larger.

You can also look at your data source's tools for tracing queries. For example, if SQL Server and SQL Server Analysis Services are data sources, you can use SQL Server Extended Events or SQL Server Profiler to trace queries.

What do I need to do if I've reached the maximum limit of 1000 data sources per user, and how do I avoid reaching this limit?

Users are limited to 1000 data sources per user.

If you've reached the maximum number of data sources limit, verify that the number of data sources per user hasn't surpassed the limit. To resolve any related issues, you can manually remove the data sources from the admin center or, alternatively, use the following Gateway PowerShell script to find and bulk-delete any data sources that exceed the limit.

## https://learn.microsoft.com/powershell/module/datagateway/?view=datagateway-ps
## PowerShell version of '7.0.0' to run
## required module "DataGateway" Install-Module -Name DataGateway and sign in the same user who exceeded the 1000 limit
Connect-DataGatewayServiceAccount

## get the gateway information per the sign in person
$gatewayClusters = Get-DataGatewayCluster | where-Object {$_.Type -EQ 'Personal'}; 
foreach ($gw in $gatewayClusters)
{
  $datasources = Get-DataGatewayClusterDatasource -GatewayClusterId $gw.Id;
  foreach ($datasource in $datasources)
  {
    $datasource
    "gateway cluster id={0}, Personal Gateway={1}, datasource id={2}, datasourceType={3}, datasource connection details={4}" -f $gw.Id, $datasource.OnPremGatewayRequired, $datasource.Id, $datasource.DatasourceType, $datasource.ConnectionDetails 
   
    ## conditional logic to determine if name matches set
    ## Remove-DataGatewayClusterDatasource -GatewayClusterId $gw.Id -GatewayClusterDatasourceId $datasource.Id
  }
}

How do I avoid reaching this limit? If you're an ISV or any other Power BI Embedded app owner with many customers, use service principal profiles for multi-tenancy apps in Power BI embedded. If you're not an ISV, you might reach this limit because you're creating a new data source for every CSV or Excel file. To solve this, you might want to use the "upload file box" in Power BI Desktop to select multiple Excel files, which creates multiple data source connections. In this scenario, to ensure that only a single data source is selected, we recommend that you select the folder containing those Excel files instead.

Administration

Can I have more than one admin for a gateway?

Yes, when you manage a gateway, you can go to the administrator's tab to add more admins. You can also have security groups as admins.

Does the gateway admin need to be an admin on the machine where the gateway is installed?

No, the gateway admin manages the gateway from within the service.

Migration

If gateway migration fails, can gateway activity be continued from the old gateway server? Does this require any manual operation?

If the migration process fails on the new server, the on-premises data gateway still exists in the old server and you can still run that gateway unless the server is offline or the gateway software has been uninstalled. However, to restore the gateway on the old server, you might be asked to use your gateway recovery key.

How long does a complete on-premises data gateway migration take?

Since migration includes only two tasks, installing the gateway and restoration and configuration on new server, migration can usually be completed in 10 to 15 minutes.

If we assume that the whole migration process will take 5 minutes, for all the refreshes that occur in these 5 minutes, where will the traffic go? Will it go through the old gateway server before the migration completes?

Yes. However, there's a good chance of failures during this time, and migrations like these should be done in a downtime window. More information: Minimize migration downtime

Known Issues

Are there any known issues?

  • If an OAuth refresh that takes longer that one hour is canceled with the error "Invalid Connection Credentials" or timeout, the issue is likely that the credential expired.