Connecting to on-premises data sources with the Azure On-premises Data Gateway service

The On-premises data gateway acts as a bridge, providing secure data transfer between on-premises data sources and your Azure Analysis Services servers in the cloud. In addition to working with multiple Azure Analysis Services servers in the same region, the latest version of the gateway also works with Azure Logic Apps, Power BI, Power Apps, and Microsoft Flow. You can associate multiple services in the same region with a single gateway.

Azure Analysis Services requires a gateway resource in the same region. For example, if you have Azure Analysis Services servers in the East US 2 region, you need a gateway resource in the East US 2 region. Multiple servers in East US 2 can use the same gateway.

Getting setup with the gateway the first time is a four-part process:

  • Download and run setup - This step installs a gateway service on a computer in your organization.

  • Register your gateway - In this step, you specify a name and recovery key for your gateway, and select a region, registering your gateway with the Gateway Cloud Service.

  • Create a gateway resource in Azure - In this step, you create a gateway resource in your Azure subscription.

  • Connect your servers to your gateway resource - Once you have a gateway resource in your subscription, you can begin connecting your servers to it.

Once you have a gateway resource configured for your subscription, you can connect multiple servers, and other services to it. You only need to install a different gateway and create additional gateway resources if you have servers or other services in a different region.

To get started right away, see Install and configure on-premises data gateway.

How it works

The gateway you install on a computer in your organization runs as a Windows service, On-premises data gateway. This local service is registered with the Gateway Cloud Service through Azure Service Bus. You then create a gateway resource Gateway Cloud Service for your Azure subscription. Your Azure Analysis Services servers are then connected to your gateway resource. When models on your server need to connect to your on-premises data sources for queries or processing, a query and data flow traverses the gateway resource, Azure Service Bus, the local on-premises data gateway service, and your data sources.

How it works

Queries and data flow:

  1. A query is created by the cloud service with the encrypted credentials for the on-premises data source. It's then sent to a queue for the gateway to process.
  2. The gateway cloud service analyzes the query and pushes the request to the Azure Service Bus.
  3. The on-premises data gateway polls the Azure Service Bus for pending requests.
  4. The gateway gets the query, decrypts the credentials, and connects to the data sources with those credentials.
  5. The gateway sends the query to the data source for execution.
  6. The results are sent from the data source, back to the gateway, and then onto the cloud service and your server.

Windows Service account

The on-premises data gateway is configured to use NT SERVICE\PBIEgwService for the Windows service logon credential. By default, it has the right of Logon as a service; in the context of the machine that you are installing the gateway on. This credential is not the same account used to connect to on-premises data sources or your Azure account.

If you encounter issues with your proxy server due to authentication, you may want to change the Windows service account to a domain user or managed service account.

Ports

The gateway creates an outbound connection to Azure Service Bus. It communicates on outbound ports: TCP 443 (default), 5671, 5672, 9350 through 9354. The gateway does not require inbound ports.

We recommend you whitelist the IP addresses for your data region in your firewall. You can download the Microsoft Azure Datacenter IP list. This list is updated weekly.

Note

The IP Addresses listed in the Azure Datacenter IP list are in CIDR notation. For example, 10.0.0.0/24 does not mean 10.0.0.0 through 10.0.0.24. Learn more about the CIDR notation.

The following are the fully qualified domain names used by the gateway.

Domain names Outbound ports Description
*.powerbi.com 80 HTTP used to download the installer.
*.powerbi.com 443 HTTPS
*.analysis.windows.net 443 HTTPS
*.login.windows.net 443 HTTPS
*.servicebus.windows.net 5671-5672 Advanced Message Queuing Protocol (AMQP)
*.servicebus.windows.net 443, 9350-9354 Listeners on Service Bus Relay over TCP (requires 443 for Access Control token acquisition)
*.frontend.clouddatahub.net 443 HTTPS
*.core.windows.net 443 HTTPS
login.microsoftonline.com 443 HTTPS
*.msftncsi.com 443 Used to test internet connectivity if the gateway is unreachable by the Power BI service.
*.microsoftonline-p.com 443 Used for authentication depending on configuration.

Forcing HTTPS communication with Azure Service Bus

You can force the gateway to communicate with Azure Service Bus by using HTTPS instead of direct TCP; however, doing so can greatly reduce performance. You can modify the Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config file by changing the value from AutoDetect to Https. This file is typically located at C:\Program Files\On-premises data gateway.

<setting name="ServiceBusSystemConnectivityModeString" serializeAs="String">
    <value>Https</value>
</setting>

Frequently asked questions

General

Q: Do I need a gateway for data sources in the cloud, such as SQL Azure?
A: No. A gateway connects to on-premises data sources only.

Q: Does the gateway have to be installed on the same machine as the data source?
A: No. The gateway connects to the data source using the connection information that was provided. Consider the gateway as a client application in this sense. The gateway just needs the capability to connect to the server name that was provided.

Q: Why do I need to use a work or school account to sign in?
A: You can only use an Azure work or school account when you install the on-premises data gateway. Your sign-in account is stored in a tenant that's managed by Azure Active Directory (Azure AD). Usually, your Azure AD account's user principal name (UPN) matches the email address.

Q: Where are my credentials stored?
A: The credentials that you enter for a data source are encrypted and stored in the Gateway Cloud Service. The credentials are decrypted at the on-premises data gateway.

Q: Are there any requirements for network bandwidth?
A: It's recommend your network connection has good throughput. Every environment is different, and the amount of data being sent affects the results. Using ExpressRoute could help to guarantee a level of throughput between on-premises and the Azure datacenters. You can use the third-party tool Azure Speed Test app to help gauge your throughput.

Q: What is the latency for running queries to a data source from the gateway? What is the best architecture?
A: To reduce network latency, install the gateway as close to the data source as possible. If you can install the gateway on the actual data source, this proximity minimizes the latency introduced. Consider the datacenters too. For example, if your service uses the West US datacenter, and you have SQL Server hosted in an Azure VM, your Azure VM should be in the West US too. This proximity minimizes latency and avoids egress charges on the Azure VM.

Q: How are results sent back to the cloud?
A: Results are sent through the Azure Service Bus.

Q: Are there any inbound connections to the gateway from the cloud?
A: No. The gateway uses outbound connections to Azure Service Bus.

Q: What if I block outbound connections? What do I need to open?
A: See the ports and hosts that the gateway uses.

Q: What is the actual Windows service called?
A: In Services, the gateway is called Power BI Enterprise Gateway Service.

Q: Can the gateway Windows service run with an Azure Active Directory account?
A: No. The Windows service must have a valid Windows account. By default, the service runs with the Service SID, NT SERVICE\PBIEgwService.

High availability and disaster recovery

Q: What options are available for disaster recovery?
A: You can use the recovery key to restore or move a gateway. When you install the gateway, specify the recovery key.

Q: What is the benefit of the recovery key?
A: The recovery key provides a way to migrate or recover your gateway settings after a disaster.

Troubleshooting

Q: How can I see what queries are being sent to the on-premises data source?
A: You can enable query tracing, which includes the queries that are sent. Remember to change query tracing back to the original value when done troubleshooting. Leaving query tracing turned on creates larger logs.

You can also look at tools that your data source has for tracing queries. For example, you can use Extended Events or SQL Profiler for SQL Server and Analysis Services.

Q: Where are the gateway logs?
A: See Tools later in this topic.

Update to the latest version

Many issues can surface when the gateway version becomes outdated. As good general practice, make sure that you use the latest version. If you haven't updated the gateway for a month or longer, you might consider installing the latest version of the gateway, and see if you can reproduce the issue.

Error: Failed to add user to group. (-2147463168 PBIEgwService Performance Log Users)

You might get this error if you try to install the gateway on a domain controller, which isn't supported. Make sure that you deploy the gateway on a machine that isn't a domain controller.

Tools

Collect logs from the gateway configurer

You can collect several logs for the gateway. Always start with the logs!

Installer logs

%localappdata%\Temp\Power_BI_Gateway_–Enterprise.log

Configuration logs

%localappdata%\Microsoft\Power BI Enterprise Gateway\GatewayConfigurator.log

Enterprise gateway service logs

C:\Users\PBIEgwService\AppData\Local\Microsoft\Power BI Enterprise Gateway\EnterpriseGateway.log

Event logs

You can find the Data Management Gateway and PowerBIGateway logs under Application and Services Logs.

Fiddler Trace

Fiddler is a free tool from Telerik that monitors HTTP traffic. You can see this traffic with the Power BI service from the client machine. This service might show errors and other related information.

Telemetry

Telemetry can be used for monitoring and troubleshooting.

To turn on telemetry

  1. Check the On-premises data gateway client directory on the computer. Typically, it is %systemdrive%\Program Files\On-premises data gateway. Or, you can open a Services console and check the Path to executable: A property of the On-premises data gateway service.
  2. In the Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config file from client directory. Change the SendTelemetry setting to true.

        <setting name="SendTelemetry" serializeAs="String">
                    <value>true</value>
        </setting>
    
  3. Save your changes and restart the Windows service: On-premises data gateway service.

Next steps