It's possible for users in your organization to access on-premises data (to which they already have access authorization), but before those users can connect to your on-premises data source, an on-premises data gateway needs to be installed and configured. The gateway facilitates quick and secure behind-the-scenes communication between a user in the cloud, to your on-premises data source, and then back to the cloud.

Installing and configuring a gateway is usually done by an administrator. It may require special knowledge of your on-premises servers and in some cases may require Server Administrator permissions.

This article doesn’t provide step-by-step guidance on how to install and configure the gateway. For that, be sure to see On-premises Data Gateway. This article is meant to provide you with an in-depth understanding of how the gateway works. We’ll also go into some detail about usernames and security in both Azure Active Directory and Analysis Services, and how the cloud service uses the e-mail address a user sign in with, the gateway, and Active Directory to securely connect to and query your on-premises data.

How the gateway works

on-prem-data-gateway-how-it-works

Let’s first look at what happens when a user interacts with an element connected to an on-premises data source.

Note:

For Power BI, you will need to configure a data source for the gateway.

  1. 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.

  2. The gateway cloud service will analyze the query and will push 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 source(s) 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. The service then uses the results.

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
SQL Server Yes Yes
SAP HANA Yes Yes
Oracle Yes Yes
Teradata Yes Yes
File No Yes
Folder No Yes
SharePoint list (on-premises) No Yes
Web No Yes
OData No Yes
IBM DB2 No Yes
MySQL No Yes
Sybase No Yes
SAP BW No Yes
IBM Informix Database No Yes
ODBC No Yes

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 nancy@contoso.onmicrosoft.com. 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.

Authentication to on-premises data sources

A stored credential will be used to connect to on-premises data sources from the gateway except Analysis Services. Regardless of the individual user, the gateway uses the stored credential to connect.

Authentication to a live Analysis Services data source

Each time a user interacts with Analysis Services, the effective username is passed to the gateway and then onto your on-premises Analysis Services server. The user principal name (UPN), typically the email address you sign into the cloud with, is what we will pass to Analysis Services as the effective user. The UPN is passed in the connection property EffectiveUserName. This email address should match a defined UPN within the local Active Directory domain. The UPN is a property of an Active Directory account. That Windows account then needs to be present in an Analysis Services role to have access to the server. The login will not be successful if no match is found in Active Directory.

Analysis Services can also provide filtering based on this account. The filtering can occur with either role based security, or row-level security.

Role-based security

Models provide security based on user roles. Roles are defined for a particular model project during authoring in SQL Server Data Tools – Business Intelligence (SSDT-BI), or after a model is deployed, by using SQL Server Management Studio (SSMS). Roles contain members by Windows username or by Windows group. Roles define permissions a user has to query or perform actions on the model. Most users will belong to a role with Read permissions. Other roles are meant for administrators with permissions to process items, manage database functions, and manage other roles.

Row-level security

Row-level security is specific to Analysis Services row-level security. Models can provide dynamic, row-level security. Unlike having at least one role in which users belong to, dynamic security is not required for any tabular model. At a high-level, dynamic security defines a user’s read access to data right down to a particular row in a particular table. Similar to roles, dynamic row-level security relies on a user’s Windows username.

A user’s ability to query and view model data are determined first by the roles their Windows user account are a member of and second, by dynamic row-level security, if configured.

Implementing role and dynamic row-level security in models are beyond the scope of this article. You can learn more at Roles (SSAS Tabular) and Security Roles (Analysis Services - Multidimensional Data) on MSDN. And, for the most in-depth understanding of tabular model security, download and read the Securing the Tabular BI Semantic Model whitepaper.

What about Azure Active Directory?

Microsoft cloud services use Azure Active Directory to take care of authenticating users. Azure Active Directory is the tenant that contains usernames and security groups. Typically, the email address a user signs in with is the same as the UPN of the account.

What is my local Active Directory’s role?

For Analysis Services to determine if a user connecting to it belongs to a role with permissions to read data, the server needs to convert the effective username passed from AAD to the gateway, and onto the Analysis Services server. The Analysis Services server passes the effective username to a Windows Active Directory domain controller (DC). The Active Directory DC then validates the effective username is a valid UPN, on a local account, and returns that user’s Windows username back to the Analysis Services server.

EffectiveUserName cannot be used on a non-domain joined Analysis Services server. The Analysis Services server must be joined to a domain to avoid any login errors.

How do I tell what my UPN is?

You may not know what your UPN is, and you may not be a domain administrator. You can use the following command from your workstation to find out the UPN for your account.

whoami /upn

The result will look similar to an email address, but this is the UPN that is on your local domain account. If you are using an Analysis Services data source for live connections, this must match what was passed to EffectiveUserName from the gateway.

Mapping usernames for Analysis Services data sources

Power BI allows for mapping usernames for Analysis Services data sources. You can configure rules to map a username logged in with Power BI to a name that is passed for EffectiveUserName on the Analysis Services connection. The map user names feature is a great way to work around when your username in AAD doesn't match a UPN in your local Active Directory. For example, if your email address is nancy@contoso.onmicrsoft.com, you could map it to nancy@contoso.com, and that value would be passed to the gateway. You can learn more about how to map user names.

Synchronize an on-premises Active Directory with Azure Active Directory

You would want your local Active Directory accounts to match Azure Active Directory if you are going to be using Analysis Services live connections. As the UPN has to match between the accounts.

The cloud services only know about accounts within Azure Active Directory. It doesn’t matter if you added an account in your local Active Directory, if it doesn’t exist in AAD, it cannot be used. There are different ways that you can match your local Active Directory accounts with Azure Active Directory.

  1. You can add accounts manually to Azure Active Directory.

    You can create an account on the Azure portal, or within the Office 365 Admin Portal, and the account name matches the UPN of the local Active Directory account.

  2. You can use the Azure AD Connect tool to synchronize local accounts to your Azure Active Directory tenant.

    The Azure AD Connect tool provides options for directory and password synchronization. If you are not a tenant admin or a local domain administrator, you will need to contact your IT admin to get this configured.

  3. You can configure Active Directory Federation Services (ADFS).

    You can associate your ADFS server to your AAD tenant with the Azure AD Connect tool. ADFS makes use of the directory synchronization discussed above but allows for a single sign-on (SSO) experience. For example, if you are within your work network, when you to a cloud service, and go to sign in, you may not be prompted to enter a username or password. You will need to discuss with your IT Admin if this is available for your organization.

Using Azure AD Connect ensures that the UPN will match between AAD and your local Active Directory.

Note:

Synchronizing accounts with the Azure AD Connect tool will create new accounts within your AAD tenant.

Now, this is where the gateway comes in

The gateway acts as a bridge between the cloud and your on-premises server. Data transfer between the cloud and the gateway is secured through Azure Service Bus. The Service Bus creates a secure channel between the cloud and your on-premises server through an outbound connection on the gateway. There are no inbound connections that you need to open on your on-premises firewall.

If you have an Analysis Services data source, you’ll need to install the gateway on a computer joined to the same forest/domain as your Analysis Services server.

The closer the gateway is to the server, the faster the connection will be. If you can get the gateway on the same server as the data source, that is best to avoid network latency between the gateway and the server.

What to do next?

After you get the gateway installed, you will want to create data sources for that gateway. You can add data sources within the Manage gateways screen. For more information, see the manage data sources articles.

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

Where things can go wrong

Sometimes installing the gateway fails. Or, maybe the gateway seems to install ok, but the service is still unable to work with it. In many cases, it’s something simple, like the password for the credentials the gateway uses to sign into the data source.

In other cases, there might be issues with the type of e-mail address users sign in with, or Analysis Services’ inability to resolve an effective username. If you have multiple domains with trusts between them, and your gateway is in one and Analysis Services in another, this sometimes can cause some problems.

Rather than go into troubleshooting gateway issues here, we’ve put a series of troubleshooting steps into another article; Troubleshooting the On-premises Data Gateway. Hopefully, you won’t have any problems. But if you do, understanding how all of this works and the troubleshooting article should help.

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 nancy@contoso.onmicrosoft.com. 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.

Note:

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 into 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.

Ports

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.

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 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.
*.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.
Note:

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 AutoDetect to 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>

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.

High Availability

High availability options are available with the Power BI Premium offering. See Power BI Premium for more details.

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.

  1. On the machine where the gateway is running, launch an admin command prompt.

  2. Use the following command to stop the service.

    net stop PBIEgwService

  3. Use the following command to start the service.

    net start PBIEgwService

See also

Troubleshooting the On-premises Data Gateway
Azure Service Bus
Azure AD Connect
More questions? Try the Power BI Community