Power BI Gateway Issue

[felipevid]

Description: We are encountering a recurring issue with our Power BI gateway. The error message we're receiving is:

Data source error: {"error":{"code":"DM_GWPipeline_Gateway_AdoNetProviderOpenConnectionTimeoutError","pbi.error":{"code":"DM_GWPipeline_Gateway_AdoNetProviderOpenConnectionTimeoutError","parameters":{},"details":[]}}} Table: GPS ALERT.

Background: This issue intermittently affects data source updates.

Request for Assistance: We need help identifying the cause and resolving this issue. The error message is not very informative, and we're looking for guidance on diagnosing and fixing the problem.

[Miinch87]

Hello,
I had a similar issue today. The solution was to update the gateway and press the button in the gateway. What I mean by that... there seems to be a display/visual bug in the service regarding this circle button. After you click the circle the submit button unlocks.

See screenshot.

[berora56]

Salut tout le monde,

Avez-vous reçu des indications des Microsoft sur les possibles solutions a ce problème, car il est devenu intermittent on a refait nos modèles et tables selon les conseils de forum, et revalidé les time out  (MashupDSRTestConnectionTimeout) pour les passer de <value>00:00:50</value> vers <value>00:02:50</value>, puis le problème persiste, est-ce que ceci peut avoir une connexion sur la configuration Proxi du serveur où se trouve la passerelle?.

Merci à l'avance pour les updates ou possibles solutions à nous partager.

{"error":{"code":"DM_GWPipeline_Gateway_AdoNetProviderOpenConnectionTimeoutError","pbi.error":{"code":"DM_GWPipeline_Gateway_AdoNetProviderOpenConnectionTimeoutError","parameters":{},"details":[],"exceptionCulprit":1}}} Table:

[BPino]

Hi everyone!

In my case, these last days everything works fine without changing anything. All refresh types (on demand, via API, scheduled, ...) are working perfectly since 13th march. 

It seems there was a bug in Power BI Service. It's the only reason that could explain that issue.

Blanca

[berora56]

Est-ce que MS a vous fourni une posible solution pour le probleme de mise a jour :

Erreur de source de données: {"error":{"code":"DM_GWPipeline_Gateway_AdoNetProviderOpenConnectionTimeoutError","pbi.error":{"code":"DM_GWPipeline_Gateway_AdoNetProviderOpenConnectionTimeoutError"

[NickMac]

Hi all,

Just following up. Getting Management approval to spend another $1800 a month on 2 additional gateways for our cluster. Bringing the total number of gateway servers in the cluters to 4.

MS is suggesting that by scaling horizontally we have enough threads to meet the demand and reduce the incidence of this issue. 

Will report back on this thread once implemented and running for some days to see if there is a measurable benefit for the additional cost.

Nick Mac

[johnh35]

Hi Nick, are you using virtual gateways?

[crhoades538]

Has anyone received resolution on this issue?  We have noticed that reducing the number of servers on the cluster has actually improved the performance of the refreshes.  We went from the largest dataset refreshes failing almost every time based on this error message… 

DM_GWPipeline_Gateway_AdoNetProviderOpenConnectionTimeoutError

To now only failing sporadically.  Would like to resolve the issue permanently.  Does anyone else have any other suggestions on how to get this to work? 

[NickMac]

This is the exact opposite of what MS has recommened. Can you please share your cluster configurations and whether these are sitting in the Azure cloud as onprem gateways or are they really on prem in your data center.

Nick

[crhoades538]

The gateway configuration we have is different then yours.  We have 3 computers setup on the cluster, they are all on-premise running the on-premise data gateway.  The only reason I said server is because that was the terminology that Microsoft uses for gateway machines.  

I tested this about a month ago… thought that it was running better on one computer.  Then pushed Microsoft for a resolution for the cluster issue.  They must be a on a 3 day SLA for responses.  Only getting a half response on the issue from them once every 3 days.  Had a call with them… they needed to get the log files.  Which we are unable to retrieve from the on premise gateway logs. The export log files after selecting the date does not work. Still trying to understand what log files they even need. Since this is a cluster the log files will need to be retrieved from all the cluster machines. 

The customer service engineer seemed to think there was an issue with one-file causing the connection timeout issue.  Not sure that one file would cause all the other dataflows and datasets to fail, with all different connections in them.

We have also received error messages below…

DM_GWPipeline_Client_AsyncOperationExpired

When we receive the above error the next refresh tends to be the connection timeout error.

Your data gateway is offline or could not be reached. 

We have received this message but it is not very seldom.

These all appear to be some type of network issue. 

We also went through the network connection issue.  Pushed this to the network team… they said they didn’t see anything wrong with the network.  Ran a ping test and didn’t see any major failures.  All the gateways are in the same location.  There are other users using Power BI at our company in different locations on different gateways with no problems.  So, I think it might be an individual computer/network problem on the cluster but haven’t flushed it all out yet.  Turned off all computers on the cluster except for one… running tests on each one individually.  It is running on some but not all machines.  Trying to see if there is some kind of setup issue with one machine. 

This link may help you. It appears that when there are multiple computers configured on the cluster the datasets are pushed to all cores on the gateway… If there is a failure on one gateway then the entire refresh will fail.  This seems to be why we are having the failures based on one machine in the cluster causing issues for entire refresh.  Would be nice if Microsoft would create a tool to monitor the cluster usage and identify which cluster computer is causing failures. This appears to be the case with the gateway cluster I am experiencing.

Working with Power BI Gateway logs - Microsoft Fabric Community

Would like to get resolution from Microsoft but that doesn’t appear to be the case.  Since the ticket has been open with Microsoft for about 1.5 months now.

[BPino]

I have the same issue since 28th January 2024: DM_GWPipeline_Gateway_AdoNetProviderOpenConnectionTimeoutError

We have one cluster with 2 gateways. I upgraded and restarted both of them.

I also tried to make an smaller dataset (with less and simple queries), but the error still appears in most of tests and it seems not to explain the reason of this error.

Has anyone find the cause of this error?

Thanks

[BPino]

I couldn't find out the reason for this error but at the end we could refresh datasets correctly!

I try to explain our issue in case it helps someone else:

We got the mentioned error (DM_GWPipeline_Gateway_AdoNetProviderOpenConnectionTimeoutError) with

scheduled and on demand refreshes (both via Power BI Service).

After several days investigating this issue, we configured a flow through Power Automate and the corresponding refresh worked. And after that... surprise! on demand refreshes work again!

The "x-file" is that when the scheduled refresh is executed again, it doesn't work and the next on demand refresh fails too...

Conclusion: scheduled refreshes make any other refresh don't work. Why? I don't know...

I enclose an image with some sequential refreshes executed through different ways, where you can see what I've tried to explain.

Note: the first following API refresh after a failed refresh, failed too but not subsequent ones.

I hope it helps anyone else 😉

[NickMac]

Hi all,

Sorry for not getting back sooner. Reason is that after a week of running agents and providing logs from servers back to MS. They came across the theory that our 8 VPU gateways do not have enough threads to support the number of requests been issued by the service and hence there is a timeout and the error. Just to note, these machines average about 4% utilization but we have followed the MS recommendation of 2 x 8VPU gateways. Anyway they are suggesting that each machine is limited by the number of threads and to remove/reduce the occurrence of the error we need to scale out .... Not easy to setup in a PRD environment. So the next best thing to do was to scale up. So for a period of a week we run 2 x 16VPU machines and noted the number of incidences occuring. Just for context we have 2 main errors cropping up in our PBI Service. 

1) {"errorCode":"Gateway_Offline","errorDescription":"EnterpriseGateway_LongMessage_Gateway_Offline"}

2){"code":"DM_GWPipeline_Gateway_AdoNetProviderOpenConnectionTimeoutError","pbi.error":

The first error is still being looked at by MS... They can't understand that if all the infrastructure is within Azure how can a High Availability setup lose the gateways... more on that one when I get more out of MS.

Here is my analysis for the period. Showing that doubling the machine size improved but did not eliminate the issue for error 2 above. 

Something doesn't seem right here.. 

FOR ::

{"code":"DM_GWPipeline_Gateway_AdoNetProviderOpenConnectionTimeoutError","pbi.error":

	VPU	Total Events	Unique Events
27/01/2024	16	3	3
26/01/2024	16	9	6
25/01/2024	16	9	4
24/01/2024	16	21	9
23/01/2024	16	8	4
		50	26
22/01/2024	8	19	7
21/01/2024	8	19	9
20/01/2024	8	15	10
19/01/2024	8	13	6
18/01/2024	8	24	9
		90	41

Here are MS suggestions to us. 

We would suggest below action plans:

1. smooth the dataset refresh by adding 2-3 seconds interval, I do know that we discussed earlier you expect the refresh every 5 mins for each dataset , but if like dataset A refreshed at 00:00:00 and dataset B refreshed at 00:00:02 then dataset C refreshed at 00:00:04. And after 5 mins, they should start at 00:05:00 and 00:05:02 and 00:05:04, thus for each dataset, they should still refresh every 5 mins. And this should reduce the sudden spike on gateway side.
2. Scale out the cluster with additional 1-2 nodes to handle the spike.
3. Add the retry parameters in your request as we mentioned in earlier call

I hope this helps someone out there. 

Will report back with updates on this.

Nick

[crhoades538]

We have been experiencing the same issue, DM_GWPipeline_Gateway_AdoNetProviderOpenConnectionTimeoutError. We are running a cluster gateway with 3 computers running on the on premise gateway. Issue started on 1-16 out of the blue. It appears when multiple datasets are using the same dataflows but this is occurring on excel files and SharePoint. Reached out to Microsoft to create a ticket... they were not helpful. They just said there is a connection problem. Any advice on how to correct this issue would be helpful.

This is also an intermittent issue for refreshes... cannot determine the exact cause of why sometimes it works and sometimes it doesn't. This is effecting multiple dataflow/dataset refreshes not just one. So, can only assume there is not an issue contained within one model.

[NickMac]

Just a follow up from our end. Have been working with MS for a few weeks. Installing agents on our HA gateway servers which are pinging MS sites every 5 seconds and also capturing logs and providing MS with a bunch of logs trying to work out the problem.  Will update soon with any resolution.

[andres777]

Hi Nick, 
Did you or Microsoft ever found the fix for the issue or workaround for these TimeOuts?
I'm getting the same error connecting a regular Power BI dashboard to SQL Server...
Thanks
Andres Martinez

[OFBReporting]

we started experiencing the same issues today, for no apparent reason. Struggling to get some responses as to why, or even get things back up and running

[NickMac]

Hi all,

We are also seeing this same issue appearing intermittently wihtout any root cause. We are running Power BI service with High Availability Gateways all within Azure. We are on premium capacity.

We execute 6 dataset refreshes every 5 minutes via an API call. We get this error many times a day. I'm raising a support ticket to Microsoft to get further investigation as to how this can happen ? We are questioning the reliability of the Power BI service to provide the level of service we need at such a high cadence of refreshes.

DM_GWPipeline_Gateway_AdoNetProviderOpenConnectionTimeoutError

All our gateways are upto date our data sources some of which are Cloud Based are always there. 

No root cause / no reason for these failures.

Please let me know if anyone comes across a root cause for these.

[user518520]

 we are having the same issue as of this morning 01-Dec-23.  Already tried restarting gateway but with no luck... Anything that has changed on the power BI service side that we should cater for

[mcaraballo]

Same situation here. We tried restarting the gateway, but still getting this error. 

[BIProcessVIV]

I had the same error and It happens very frequently, but in the following auto refresh, the data refreshed successfully. I had updated the on-premises data gateway and I restarted the data gateway, but it keeps happening. Please Help us.