On-premises data gateway
The On-premises data gateway acts as a bridge, providing quick and secure data transfer between on-premises data (data that is not in the cloud) and the Power BI, Microsoft Flow, Logic Apps, and PowerApps services.
You can use a single gateway with different services at the same time. If you are using Power BI as well as PowerApps, a single gateway can be used for both. It is dependent on the account you sign in with.
The On-premises data gateway implements data compression, and transport encryption, in all modes.
- .NET 4.6 Framework
- 64-bit version of Windows 7 / Windows Server 2008 R2 (or later)
- 8 Core CPU
- 8 GB Memory
- 64-bit version of Windows 2012 R2 (or later)
- The gateway cannot be installed on a domain controller
- If you are planning to use Windows authentication, make sure you install the gateway on a computer that is a member of the same Active Directory environment as the data source(s).
- You shouldn't install a gateway on a computer, such a laptop, that may be turned off, asleep, or not connected to the Internet because the gateway can't run under any of those circumstances. In addition, gateway performance might suffer over a wireless network.
- Analysis Services is not required to use the gateway. You can use the gateway to connect to an Analysis Services data source.
Limitations of Analysis Services live connections
You can use a live connection against tabular or multidimensional instances.
|Server version||Required SKU|
|2012 SP1 CU4 or later||Business Intelligence and Enterprise SKU|
|2014||Business Intelligence and Enterprise SKU|
|2016||Standard SKU or higher|
- Cell level Formatting and translation features are not supported.
- Actions and Named Sets are not exposed to Power BI, but you can still connect to multidimensional cubes that also contain Actions or Named sets and create visuals and reports.
List of available data source types
|Data source||Live/DirectQuery||User configured manual or scheduled refresh|
|Analysis Services Tabular||Yes||Yes|
|Analysis Services Multidimensional||Yes||Yes|
|IBM Informix Database||No||Yes|
|SharePoint list (on-premises)||No||Yes|
Download and install the On-premises data gateway
To download the gateway, select Data Gateway under the Downloads menu. Download the On-premises data gateway.
Note that updating the On-premises data gateway is achieved by reinstalling the gateway, as described in this section. When updating the gateway (by reinstalling), your existing gateways settings are retained.
Install the On-premises data gateway
The data gateway installs and runs on your computer. It is best to install the gateway on a machine that can be left running all the time.
The gateway is supported only on 64-bit Windows operating systems.
For Power BI, the first choice you have to make is the mode of the gateway.
- On-premises data gateway: Multiple users can share and reuse a gateway in this mode. This gateway can be used by Power BI, PowerApps, Flow or Logic Apps. For Power BI, this includes support for both schedule refresh and DirectQuery
- Personal: This is for Power BI only and can be used as an individual without any administrator configuration. This can only be used for on-demand refresh and schedule refresh. This selection launchs installation of the personal gateway.
There are a few things to note about insalling either mode of the gateway:
- both gateways require 64-bit Windows operating systems
- gateways can’t be installed on a domain controller
- you can install up to two On-premises data gateways on the same computer, one running in each mode (personal and standard).
- you cannot have more than one gateway running in the same mode on the same computer.
- you can install multiple On-premises data gateways on different computers, and manage them all from the same Power BI gateway management interface (excluding personal, see the following bullet point)
- You can only have one Personal mode gateway running for each Power BI user. If you install another Personal mode gateway for the same user, even on a different computer, the most recent installation replaces the existing previous installation.
Here are a few things to consider before installing the gateway.
- If you are installing on a laptop, and your laptop is turned off, not connected to the internet, or asleep the gateway won’t work and the data in the cloud service will not be synchronized with your on-premises data.
- If your machine is connected to a wireless network, the gateway may perform more slowly which will cause it to take longer to synchronize the data in the cloud service with your on-premises data.
Once the gateway is installed, you will need to sign in with your work or school account.
After you are signed in, you will have the option to configure a new gateway, or to migrate, restore, or take over an existing gateway.
Configure a new gateway
- Enter a name for the gateway
- Enter a recovery key. This has to be a minimum of 8 characters.
- Select Configure.
The recovery key will be needed if you ever need to migrate, restore or take over a gateway. Be sure to keep this key in a safe place.
Migrate, restore or take over an existing gateway
You will need to select the gateway you want to recover and supply the recovery key that was used to first create the gateway.
On-premises data gateway connected
Once the gateway is configured, you will be able to make use of it to connect to on-premises data sources.
If the gateway is for Power BI, you will need to add your data sources to the gateway within the Power BI service. This is done within the Manage gateways area. You can refer to the manage data sources articles for more information.
For PowerApps, you will need to select a gateway for a defined connection for supported data sources. For Flow and Logic Apps, this gateway is ready to be used with your on-premises connections.
Install the gateway in personal mode
The Personal version of gateway only works with Power BI.
After the personal gateway is installed, you will need to launch the Power BI Gateway - Personal Configuration Wizard.
You will then need to sign into Power BI to register the gateway with the cloud service.
You will also need to supply the windows user name and password that the windows service will run as. You can specify a different Windows account from your own. The gateway service will run using this account.
After the installation is complete, you will need to go to your datasets within Power BI and make sure credentials are entered for your on-premises data sources.
Storing encrypted credentials in the cloud
When you add a data source to the gateway, you need to provide credentials for that data source. All queries to the data source will run using these credentials. The credentials are encrypted securely, using asymmetric encryption so that they cannot be decrypted in the cloud, before they are stored in the cloud. The credentials are sent to the machine, running the gateway, on-premises where they are decrypted when the data sources are accessed.
Sign in account
Users will sign in with either a work or school account. This is your organization account. If you signed up for an Office 365 offering and didn’t supply your actual work email, it may look like firstname.lastname@example.org. Your account, within a cloud service, is stored within a tenant in Azure Active Directory (AAD). In most cases, your AAD account’s UPN will match the email address.
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 Log on as a service. This is in the context of the machine that you are installing the gateway on.
If you selected personal mode, you configure the Windows service account separately.
This is not the account used to connect to on-premises data sources. This is also not your work or school account that you sign in to cloud services with.
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. You can learn how to change the account in proxy configuration.
The gateway creates an outbound connection to Azure Service Bus. It communicates on outbound ports: TCP 443 (default), 5671, 5672, 9350 thru 9354. The gateway does not require inbound ports. Learn more
It is recommended that 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. The gateway will communicate with Azure Service Bus using the IP address along with the fully qualified domain name (FQDN). If you are forcing the gateway to communicate using HTTPS it will strictly use FQDN only, and no communication will happen using IP addresses.
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 thru 10.0.0.24. Learn more about the CIDR notation.
Here is a listing of the fully qualified domain names used by the gateway.
|Domain names||Outbound ports||Description|
|*.download.microsoft.com||80||HTTP used to download the installer.|
|*.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)|
|*.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.|
Traffic going to visualstudio.com or visualstudioonline.com are for app insights and are not required for the gateway to function.
Forcing HTTPS communication with Azure Service Bus
You can force the gateway to communicate with Azure Service Bus using HTTPS instead of direct TCP. This may have an impact on performance. To do so, modify the Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config file by changing the value from
Https, as shown in the code snippet directly following this paragraph. That file is located (by default) at C:\Program Files\On-premises data gateway.
<setting name="ServiceBusSystemConnectivityModeString" serializeAs="String"> <value>Https</value> </setting>
The value for the ServiceBusSystemConnectivityModeString parameter is case sensitive. Valid values are AutoDetect and Https.
Alternatively, you can force the gateway to adopt this behavior using the gateway user interface, beginning with the March 2017 release. In the gateway user interface select Network, then toggle the Azure Service Bus connectivity mode to On.
Once changed, when you select Apply (a button that only appears when you make a change), the gateway Windows service restarts automatically, so the change can take effect.
For future reference, you can restart the gateway Windows service from the user interface dialog by selecting Service Settings then select Restart Now.
Support for TLS 1.1/1.2
With the August 2017 update and beyond, the On-premises data gateway uses Transport Layer Security (TLS) 1.1 or 1.2 to communicate with the Power BI service by default. Previous versions of the On-premises data gateway use TLS 1.0 by default. On March 15th 2018, support for TLS 1.0 will end, including the gateway's ability to interact with the Power BI service using TLS 1.0, so by then you must upgrade your On-premises data gateway installations to the August 2017 release or newer to ensure your gateways continue to operate.
It's important to note that TLS 1.0 is still supported by the On-premises data gateway prior to November 1st, and is used by the gateway as a fallback mechanism. To ensure all gateway traffic uses TLS 1.1 or 1.2 (and to prevent the use of TLS 1.0 on your gateway), you must add or modify the following registry keys on the machine running the gateway service:
Adding or modifying these registry keys applies the change to all .NET applications. For information about registry changes that affect TLS for other applications, see Transport Layer Security (TLS) registry settings.
How to restart the gateway
The gateway runs as a windows service. You can start and stop it like any windows service. There are multiple ways to do this. Here is how you can do it from the command prompt.
On the machine where the gateway is running, launch an admin command prompt.
Use the following command to stop the service.
net stop PBIEgwService
Use the following command to start the service.
net start PBIEgwService
How the gateway works
Let’s first look at what happens when a user interacts with an element connected to an on-premises data source.
For Power BI, you will need to configure a data source for the gateway.
- A query will be created by the cloud service, along with the encrypted credentials for the on-premises data source, and sent to the queue for the gateway to process.
- The gateway cloud service will analyze the query and will push the request to the Azure Service Bus.
- The On-premises data gateway polls the Azure Service Bus for pending requests.
- The gateway gets the query, decrypts the credentials and connects to the data source(s) with those credentials.
- The gateway sends the query to the data source for execution.
- The results are sent from the data source, back to the gateway, and then onto the cloud service. The service then uses the results.
Limitations and Considerations
Tenant level administration
There is currently no single place where tenant administrators can manage all the gateways that other users have installed and configured. If you’re a tenant administrator, we recommend that you ask the users in your organization to add you as an administrator to every gateway they install. This allows you to manage all the gateways in your organization through the Gateway Settings page or through PowerShell commands.
Enabling outbound Azure connections
The On-premises data gateway relies on Azure Service Bus for cloud connectivity and correspondingly establishes outbound connections to its associated Azure region. By default, this is the location of your Power BI tenant. See Where is my Power BI tenant located? If a firewall is blocking outbound connections, you must configure the firewall to allow outbound connections from the On-premises data gateway to its associated Azure region. See Microsoft Azure Datacenter IP Ranges for details about the IP address ranges of each Azure data center.
The IP address ranges might change over time, so make sure you download the latest information on a regular basis.
If you’re having trouble when installing and configuring a gateway, be sure to see Troubleshooting the On-premises data gateway. If you think you are having an issue with your firewall, see the firewall or proxy section in the troubleshooting article.
If you think you are encountering proxy issues, with the gateway, see Configuring proxy settings for the Power BI gateways.
Manage your data source - Analysis Services
Manage your data source - SAP HANA
Manage your data source - SQL Server
Manage your data source - Oracle
Manage your data source - Import/Scheduled refresh
On-premises data gateway in-depth
On-premises data gateway (personal mode) - the new version of the personal gateway Configuring proxy settings for the On-premises data gateway
More questions? Try the Power BI Community