Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Snaefellsnes
Helper I
Helper I

On-premises data gateway-Network Request Returned Unexpected Error-Configuration client returned nul

Hello,

 

This is a similar problem to the one exposed in this thread:

https://community.powerbi.com/t5/Service/On-premises-data-gateway-Network-Request-Returned-Unexpecte...

 

We are not using a proxy, hence the proxy script solution will not achieve anything for us. Also, our server access the internet thru the corporate VPN.

 

Long story short - drive problem Wednesday morning led up to a restart of our sql server on which the on-premises data gateway resides. However, PowerApps and datasets were not connecting to the gateway afterwards. We updated the gateway version on the server (it was running the november update) and aftwerwards, the gateway configurator does not even allow us to get in. We are stuck on the sign-in page and nothing happens, after about one minute, the error message is displayed:

 

Network request returned unexpected error.

System.ArgumentNullException: Value cannot be null.
Parameter name: source
at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)
at Microsoft.PowerBI.DataMovement.GatewayCommon.ConfiguratorClientExtensions.<DiscoverEmailProperties>d__8.MoveNext()

 

The export logs button is available. What really is puzzling is that the GatewayProperties is empty. No property are set. Nothing. That gateway was operational until 5h30 am Wednesday morning and now all trace of its configuration are gone.

 

In case someone has an idea, please let me know 🙂

 

Here's the full log from the gateway configurator:

EnterpriseGatewayConfigurator.exe Information: 0 : Starting EnterpriseGatewayConfigurator, Version 3000.72.6+2674fee9c3bdefd5e206353ed91f11042ddb3a1a
EnterpriseGatewayConfigurator.exe Information: 0 : Initialize EnterpriseGatewayMainViewModel
EnterpriseGatewayConfigurator.exe Information: 0 : GetConfiguratorStateAsync
EnterpriseGatewayConfigurator.exe Information: 0 : Configuration client returned null
EnterpriseGatewayConfigurator.exe Information: 0 : GetDataDirectory: 'C:\Users\PBIEgwService\AppData\Local\Microsoft\On-premises data gateway'
EnterpriseGatewayConfigurator.exe Information: 0 : Sign In
EnterpriseGatewayConfigurator.exe Error: 0 : Exception: Microsoft.PowerBI.DataMovement.GatewayCommon.EmailDiscoveryServiceException: Network request returned unexpected error. ---> System.ArgumentNullException: Value cannot be null.
Parameter name: source
at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)
at Microsoft.PowerBI.DataMovement.GatewayCommon.ConfiguratorClientExtensions.<DiscoverEmailProperties>d__8.MoveNext()
--- End of inner exception stack trace ---
at Microsoft.PowerBI.DataMovement.GatewayCommon.ServiceEnvironmentDetailsFactory.GetEnvironment(String emailAddress, Uri endpoint, String preferredCloud)
at Microsoft.PowerBI.DataMovement.GatewayUiCommon.WizardSignInViewModel.StartSignIn(IServiceEnvironmentDetailsFactory serviceEnvironmentDetailsFactory, ConfiguratorAzureGeo selectedGeo)
at Microsoft.PowerBI.DataMovement.GatewayUiCommon.WizardSignInViewModel.DoNext(Object param)
at Microsoft.PowerBI.DataMovement.GatewayUiCommon.WizardViewModelBase.DoNextWorker(Object param)
at Microsoft.PowerBI.DataMovement.GatewayUiCommon.RelayCommand.<>c__DisplayClass19_0.<Execute>b__0()
EnterpriseGatewayConfigurator.exe Information: 0 : Update checks
EnterpriseGatewayConfigurator.exe Information: 0 : Create web request for Uri: http://www.msftncsi.com/ncsi.txt
EnterpriseGatewayConfigurator.exe Information: 0 : Response status: OK
EnterpriseGatewayConfigurator.exe Information: 0 : Create httpClient request for Uri: http://www.msftncsi.com/ncsi.txt
EnterpriseGatewayConfigurator.exe Information: 0 : Response status: OK
EnterpriseGatewayConfigurator.exe Information: 0 : Checking for PbiEgwService
EnterpriseGatewayConfigurator.exe Information: 0 : Fetch state for PbiEgwService
EnterpriseGatewayConfigurator.exe Information: 0 : Service state: Running
EnterpriseGatewayConfigurator.exe Information: 0 : Create httpclient request for Uri: http://www.msftncsi.com/ncsi.txt
EnterpriseGatewayConfigurator.exe Information: 0 : Response status: OK

9 REPLIES 9
lbendlin
Super User
Super User

You'll want to distinguish between continuity and recovery.

 

For continuity purposes you absolutely will want to have more than one cluster member.

For recovery purposes it is critically important that you do not remove a broken anchor member from a gateway until AFTER you have recovered its function with another cluster member.  Failure to do that will result in loss of all gateway connection details that are stored in the Azure cloud.

 

In short - you can recover much quicker if you have a spare VM ready to go at a minute's notice to take over a broken anchor member. Or you can avoid the downtime altogether (in most cases) when the other members take over.  (There are corner cases where the entire gateway will not work until the anchor member's management console has been logged into)

lbendlin
Super User
Super User

I'll be interested to hear if it is covered in my list, and if not I will gladly add it.

lbendlin
Super User
Super User

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

  1. Open an Administrator Powershell script
  2. 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

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

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

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

 

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

 

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

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.

 

 

Many thanks for sharing Ibendlin! I will follow-up on the solution we will have used to solve our situation.

A quick follow-up on our situation. We opened a ticket with Microsoft and last Thursday, two technicians spent an hour reviewing the server config, network access and grabbibg our logs file for further analysis. No solution found, the gateway can't be configure anymore on that server. If Microsoft finds a solution, I will update this thread.

 

Meanwhile, we configure a new gateway on a new vm and did the takeover. Works flawlessly:

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

 

Important lesson learned from our problem is that we will never have a single gateway server from now on. Always have at least two in a cluster, as described in lbendlin troubleshooting guide:

 

"A single 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"

lbendlin
Super User
Super User

I have compiled a document with all the issues and their solutions we have encountered so far in our environment (multiple gateways with multiple cluster members). I'm not saying I have seen it all but I certainly have suffered through a lot of issues with our gateway setups (and I have lots of questions for the gateway product team). If you like I can share that document with you, maybe it can give you some pointers.

Hello,

 

yes if you want to share, it could help us and maybe other users on this forum too.

lbendlin
Super User
Super User

"our sql server on which the on-premises data gateway resides"

There's your first big mistake.  Gateway cluster members are extremely resource hungry. It is highly advisable not to have anything else run on that machine, especially not something like SQL server that will compete for the RAM and disk speed.

 

Have you considered downgrading to 68.15  for testing?

Thanks Ibendlin,

 

Downgrading to the previous release did not do anything. We simply can't use the Gateway Configurator anymore. You are right about having the gateway on the same mahcine as SQL Server, the gateway is going to be installed on another vm.

 

What do you mean by "first big mistake"? Are you able to deduct other problems by the log? If so, please let me know so we can make sure our next install is appropriate.

 

Regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors