Power BI Gateway Guidelines

On-premises/Enterprise gateway

As described in https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem the better alternative to personal gateways are on-prem gateways running on dedicated hardware with guaranteed availability and sufficient resources.

Hardware requirements

Follow these minimum requirements when provisioning dedicated hardware or virtual machines:

Powershell 5.0 or later - KB3191564

.NET 4.8 Framework or later - KB4486105

64-bit version of Windows 7 / Windows Server 2008 R2 (or later)

Recommended:

4 Core CPU or higher

8 GB Memory or higher

64-bit version of Windows 2012 R2 (or later)

Recovery key – business continuity

When you create a gateway a recovery key is automatically generated.

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. Have a process in place that can hand the recovery key over to your replacement if you leave the team or company. Note: Gateway version after December 2019 allow you to change the recovery key if you login on the primary cluster member.

Gateway user account

When the gateway is first installed it will be configured to use the NT SERVICE\PBIEgwService  account. This account may not have access to the internal data sources, and may need to be replaced with a supplemental account.  Note that this is not a reversible process. You will need to uninstall and reinstall the gateway to get back to the PBIEgwService account if you had changed it.

You can run the gateway under any account that is listed in Active Directory. Since you need to use a supplemental account to access cloud virtual machines you may want to use the same supplemental account for the gateway. Another option would be to use a service account.

Note: Both supplemental and service accounts are still subject to password expiry policies, and you need to be prepared to change the passwords every 90 days.

Gateway operation and maintenance

Once the gateway is setup you can make sure that the service is running, and you can check the status via the service application

You will be notified when an update is available. Run the installer program again to perform the update.

Depending on the credentials type and password retention policies your gateway connections will need to be updated whenever the users’ NT password changes (usually every 90 days) or when service account passwords expire (usually every 365 days)

Gateway clusters

https://docs.microsoft.com/en-us/power-bi/service-gateway-high-availability-clusters

A singe gateway machine poses a risk for your operations. It can become inaccessible on the network, it can lock up, or it can be powered down (especially if you chose to install the gateway on your personal laptop).

A much better alternative is to create a cluster of gateway member that act as one gateway. Ideally these machines should be in different physical locations for maximum resilience. An example would be to use virtual machines that are in geographically different data centers.

The first machine in the gateway cluster acts as the master or “anchor” member. All refresh requests are handled by it first. If it is too busy it hands the requests over to the other cluster members.

This means you want to make sure your primary cluster member has enough capacity (4 or more processors, 8GB or more RAM, sufficient free disk space)

Additional Logging and performance monitoring

If you want to capture performance details of your gateway dataset refreshes you need to manually modify the settings files on each cluster member, and then periodically collect the resulting performance counters (for example by copying them to a common folder on a OneDrive). Microsoft provides a reporting template and setup instructions for this at

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-performance

Only enable this setting for trouble shooting as it will have a pretty big impact on gateway performance

Remove a gateway

If you no longer need the on-prem gateway you can run the uninstaller via the control panel.

If you have replaced the hardware it was running on,  or no longer have access to it then you need to run a sequence of powershell commands to remove the gateway .

1. Make sure the gateway installation folder is present (you may have to start a fresh installation which you can remove again after the process is done). The folder is usually

C:\Program Files\On-premises data gateway

2. Open an Administrator Powershell script
3. Run the following commands

cd “C:\Program Files\On-premises data gateway”

Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Force

Import-Module .\OnPremisesDataGatewayHAMgmt.psm1  (alternatively: Install-Module -Name OnPremisesDataGatewayHAMgmt -Scope CurrentUser )

Login-OnPremisesDataGateway

Get-OnPremisesDataGatewayClusters

4. Take note of the cluster you want to remove. The cluster contains one or more gateways, with one of the gateways sharing the same ID with the cluster (this is the primary cluster member).

       Get-OnPremisesDataClusterGateways -ClusterObjectID "<your cluster ID here>"

5. Gateways need to be removed in reverse order of installation

Remove-OnPremisesDataGateway -ClusterObjectID "<your cluster ID here>" -GatewayObjectID “<your gateway id 3 here>”

Remove-OnPremisesDataGateway -ClusterObjectID "<your cluster ID here>" -GatewayObjectID “<your gateway id 2 here>”

Etc.

For further information: https://docs.microsoft.com/en-us/power-bi/service-gateway-high-availability-clusters#powershell-support-for-gateway-clusters

https://community.powerbi.com/t5/Issues/Cannot-remove-Gateway-Cluster-in-PowerBi-Service-when-the/idi-p/332219

There is a new version of the Powershell scripts available, based on Powershell  core 6.2.2 and newer.  The basic process is the same.

Install-Module -Name DataGateway -Scope CurrentUser

Get-Command -Module DataGateway*

HTTPS and TLS 1.2

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-communication#force-https-communication-with-azure-service-bus

Install the TLS1.2 registry patch as needed.

Azure Storage TLS: Changes are coming! (…and why you care) - Microsoft Tech Community

Verify that the TLS1.2 Client settings are as follows:

Disable TLS1.0 explicitly if you see errors like : Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.)

Common and uncommon gateway issues

Cause 1:  User profile is temporary profile:  Conflict between TEMP user and pbigwservice user – they had the same ID.

Solution1: deinstall gateway, use regedit to remove pbigateway user residue from  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList

https://www.tenforums.com/tutorials/48012-fix-youve-been-signed-temporary-profile-windows-10-a.html

Cause 2: Key not valid for use in specified state

Solution 2:  Remove RSA folder

https://community.powerbi.com/t5/Issues/Error-in-re-installing-on-premises-data-gateway-personal-mode/idi-p/809677

Alternatively, ask IT support to add the personal On-premises data gateway into the whitelist of McAfee

Cause3:  Gateway service starts and immediately stops when using supplemental account.

No solution at the moment. Try adding the supplemental account as one of the users in the local machine.

Cause4: Net.TCP Listener Adapter not starting (stuck at “Start Pending”)

Raise a ticket with the cloud VM team to rebuild the network on the VM.

Alternatively, set the service to manual start.

Alternatively, modify the file c:\Windows\System32\inetsrv\config\applicationHost.config .

Make the following change by updating the file.

FROM:

<listenerAdapters>
   <add name="http" />
</listenerAdapters>

TO:

<listenerAdapters>
   <add name="http" />

   <add name="net.tcp" identity="S-1-5-80-3579033775-2824656752-1522793541-1960352512-462907086" />
</listenerAdapters>

Note: Sometimes the service does not start at all because dependencies are not satisfied. Resolve all issues with services that the Net.TCP listener depends upon.

Cause5: <redacted, company specific>

Cause 6: Cannot register new gateway cluster member

Solution 6: Enforce usage of proxy

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-proxy

Modify the following files:

<redacted>

NOTE: This needs to be reapplied after each gateway version update

Then restart the Gateway service and complete the registration. Depending on where the process failed you may need to recover (instead of register) the gateway instance.  You can check the status with powershell

Login-OnPremisesDataGateway

$g = Get-OnPremisesDataGatewayClusters

foreach ($i in $g) {

#    $i.objectId

    $j = $i.gateways | ConvertFrom-Json

#    $j | select gatewayName,isAnchorGateway,gatewayClusterStatus,gatewayVersion,gatewayMachine,gatewayObjectId

    $j | Format-Table gatewayName,isAnchorGateway,gatewayClusterStatus,gatewayVersion,gatewayMachine,gatewayObjectId -AutoSize

}

NOTE: New gateway version will only appear correctly in this script after you have logged into the configurator again on the updated cluster member.

May also need to set (in an admin prompt)

<redacted>

Cause 7: Unable to sign in to the Gateway configurator. “Failed to sign in. Please verify your credentials and try again.”

Solution 7:  Re-import the DigiCertGlobalRootCA certificate into the machine’s Trusted Root Certification Authorities store. If needed export from your PC. Also see links in TLS1.2 section.

Cause 7a: Failed to sign in. TLS 1.0 is disabled on your machine

Solution 7a: Verify all trusted sites are listed in IE. Re-run the configurator.

Cause 8: Gateway is running but unreachable

Solution 8: Re-import the DigiCert Global Root G2 certificate into the machine’s Trusted Root Certification Authorities store. Also see links in TLS1.2 section.

Cause 9: In the dataset scheduling settings the gateway is shown but not selectable. Two identical connections are shown below. An error message states “No available gateway” and all service refreshes fail

Solution 9: There is a case (spelling) mismatch between the connection definition on the Power BI Desktop and the connection definition in the gateway. Correct on the desktop, and re-upload the dataset and re-publish the app.

Frequently Asked Questions

Q: Do I always need a gateway?

A: Gateways are needed to allow the Azure service access to on-premise data sources.  They are not required when the data source is in the cloud, readily accessible to Azure. That includes data sources like SharePoint Online, OneDrive, or Azure Cloud databases.

Q: I have set up the connections, but I still do not see the gateway in the Power BI service. What am I missing?

A: Gateways will only be available for selection if ALL on-premise data sources in a dataset are declared on the gateway, and ONLY if the connection details match EXACTLY, including the authentication method.

Q: I get an error message “No available gateway” but I see the gateway with duplicate connections?

A: Gateways will only be available for selection if the connection details match EXACTLY, including the authentication method.

Q: I have made sure that the connections in my dataset are all covered by the gateway, but I am still unable to choose the gateway. What else do I need to do?

A: Make sure you have been added to the list of developers that can consume the gateway connection. Validate that all dataset connections use the same gateway cluster.

Q: Once I set up the connection, is that it?

A: Gateway connection credentials are impacted by the standard expiry policies. Connections thus require permanent maintenance (every 90 days, every 365 days etc.), namely a credential update. Take the opportunity to also check who the connection is shared to, and adjust the list of developers who can consume the connection.

Q: What if I don’t feel comfortable using my credentials for the gateway connection and then allowing other developers to utilize that connection under my name?

A: Create separate connections for each developer

Q: Do I need to share the gateway connection with all my report users?

A: Gateway connections are used for data refresh, either scheduled by the dataset owner, on demand, or via API. They are not used by report customers, only by report developers.

Q: What specs does a gateway cluster member need?

A: Gateways do not just broker connections, they also do dataset processing. It is preferable to have machines with lots of memory and available disk space. You need to monitor both on a regular basis.

Q: How can I audit gateway performance and usage?

A: Gateway meta reporting is available but at a cost (processing overhead). Further details are available on request.

Q: How can I remove a gateway?

A: It is easy to add gateways. Removing/deregistering gateways is REALLY hard, it requires a Powershell script and choreography. Refer to the section in this document that covers gateway cluster member removal

Q: I have installed the gateway, and it is working. Is that it?

A: The gateway mashup engine needs to stay in sync with the engine used in the Power BI Desktop and on the Power BI Service.  Expect monthly updates that you have to apply to all of your gateway cluster members

Q: My gateway PC crashed, and I have to reinstall the gateway. What do I need?

A: The single most important thing is the RECOVERY KEY.  You specify that when you first create the gateway (or the first cluster member).  You need this recovery key when you reinstall a gateway, or when you transfer a gateway from one machine to another. Make sure you are not the only developer who knows the recovery key.

Q: How do I document my gateway’s connections?

A: Use the Power BI REST API calls to collect the health status of your gateway connections. Further details are available on request.

Q: My (Vertica etc) connectors have disappeared from the gateway. What happened?

A: Sometimes Gateway Cluster members can stop working. While the gateway will still process refresh requests for existing connections, this may impact the ability to modify connections or create new ones, especially with custom connectors. Remedy: Reboot all cluster members.

Q: I cannot add gateway cluster members. The message says that the main gateway needs to be online

A: Not only does the main gateway member need to be online, you also need to log in to the gateway configurator on the main gateway.