Data Refresh Errors after Enterprise Gateway upgrade

[motoray]

After upgrading to the October 2024 gateway, I'm getting refresh errors randomly for scheduled Power BI refreshes. The error is: "DM_GWPipeline_Gateway_DataSourceAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_DataSourceAccessError." The error isn't consistent across reports and there's always a table referenced, but the table isn't consistent either. There's are all SQL Server on prem connections that are failing. I can always go click the refresh button in the workspace and the model refreshes without a problem. Had the same problem when updating from June to July 2024 gateway and reverted and the errors went away.

Things I've tried:

1. Recreated the entire gateway cluster and ALL connections from scratch.

2. Recreated individual connections and reset credentials.

3. Tried different credentials.

4. Tried setting up different connections.

5. Currently on my 2nd support ticket with Microsoft and am still stuck on really low-level troubleshooting (frustating).

6. Creating new reports with new data connections.

7. Rebuilding exisitng reports with new connections.

8. Simplifying the Power Query queries in the reports (this one has shown a little promise, but is problematic).

One thing I've noted through all this is that there are a couple things we do that create "Personal Mode" connections. If one of us "Takes Over" a semantic model to update it, "Personal Mode" connections show up--despite the fact that there are shared connections exsiting. Also, publishing a report to a workspace results in "Personal Mode" connections showing up...even after we've updated the settings to use the proper shared connection. These Personal Mode connections, can't be edited in any meaningful way--can't add credentials, can't update users, etc. and they're always "Offline." There seems to be a correlation between having the Personal Mode connections there (or not--our practice now is to delete them at the end of the day) and the number of failure we get.

Has anyone seen anything like this?

[AMD0791]

You mention "Personal Mode" connections.  Are you referring to

A. The connections on the on-premise gateaway "Connections" tab that have "(Personal mode)" for the Gateway Cluster Name?

or

B."On-premise gateway (Personal Mode)" items on the "On -premise Data Gateways" tab?

For A, I can only see the connections for myself.  Have you found a way to identify additional connections possibly created by others?  I also haven't found any explanation for what they are or why they matter.

Thanks!

[motoray]

Am referring to (Personal mode) in the Gateway cluster name column of the Connections tab.

I haven't found a way to see others' (Personal mode) connections either. Luckily there's only two of us who do this work and we just try and go in there at least 1X weekly to clean it up.

[AMD0791]

Interesting -- I wonder if this happens for everyone that can publish to the service, or only those that have the admin or creator privileges on the gateway.  

[motoray]

There are only a handful of us with permission to set up the scheduled refreshes and we all have admin/creator on the gateway, so I haven't been able to test this.

[JohnJ2]

Same error here too of: 

Data source error:

{"error":{"code":"DM_GWPipeline_Gateway_DataSourceAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_DataSourceAccessError","parameters":{},"details":[],"exceptionCulprit":1}}} Table: Specialists where "Table" is always listed as something different.  Have done everything you listed as well: configured CPUUtilizationPercentageThreshold to 70 from 0 Enabled query caching on jobs using import mode Enabled Large Semantic Model Storage Format Enabled Query Scale out Error is completely random, no consistent time or reason.  Semantic model is nowhere near exceeding the 25GB size limit.  MS just keeps requestintg logs, logs, logs but because the refresh failure throws randomly, we are having a hard time capturing it in logs and when we do submit, no reason is provided from Microsoft.  Frustrating!

[kymramosrpo]

have been experiencing the exact same issue here for weeks. Error logs are of no help at all. Have you found a solution, or even a way to diagnose the problem?

[motoray]

We were given a solution that's worked well enough for us with the promise of Microsoft doing some kind of more permanent fix in a future gateway.

There's a configuration file--Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config. On our servers, it's here "C:\Program Files\On-premises data gateway." 

Open the config file in a text editor and scroll down to almost the bottom. Find the "NextResultSkipped" setting and change the value from False to True. After that, restart your gateway and see if the failures stop. They did for us. I never got a satisfactory answer about why they stopped.

[kymramosrpo]

Thanks for sharing! I've set it up just now. Hope to see some improvements soon. Will share the results here!

[anon_jens]

Was it more stable, @kymramosrpo ? We did this today as well.

[kymramosrpo]

Hi @anon_jens - yes! Dataflows are now stable. Thank you!

[motoray]

I hear you on with the frustration. I did finally get to the next level of tech support...and they wanted more logs. I've found two things that brought some relief--but we still see daily refresh failures.

1. For some reason we get Personal mode connections occasionally in our gateway connections list. We do not use Personal gateways. The Personal mode connections don't connect to anything and can't be changed. If we are careful to check and remove these connections, we get fewer failures.

2. I found that one of the gateway cluster servers was very low on hard drive space. I had our server folks increase HDD space on both servers in the cluster and bring them up to a higher spec in line with what Microsoft recommends for gateway cluster servers. There was a noticeable difference in # of refresh failures.

Maybe take a look at those and see whether you can get any relief that way.

[lbendlin]

You also want to consider putting the heavy load items (spooler and logging) onto a different spindle.

[JohnJ2]

Yup redirected those to the unlimited E: drive long ago

[lbendlin]

After upgrading to the October 2024 gateway

which one?

Did you notice the "Trusted Server" discussion in the release notes?

[motoray]

I've just upgrade to 242.8. I hadn't seen the "Trusted Sever" discussion. Can you point me to that?

[motoray]

Never mind, I did find how to set it up over here: 
https://learn.microsoft.com/en-us/power-query/connectors/sql-server#sql-server-certificate-isnt-trus...

We don't use encrypted connections internally yet, so this may very well have something to do with it.

[Dreamer0100101]

Hi ,please let me know if you found any solutions for this ,I am facing similar issue.

[motoray]

I was just on a call with Microsoft again this morning. Have to upgrade to the latest Gateway again and they grabbed log files, but no real progress.

[Anonymous]

Your solution is so great lbendlin 

Hi, @motoray 

The current error is that it is now the default behavior for all PBI desktop and gateway versions that are equal to or higher than the February 2024 release.

Solved: Assistance Required with SQL Server Standard Mode ... - Microsoft Fabric Community

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[motoray]

Unfortunately in my case this did not correct the problem. I continue to have refresh failures after adding trusted SQL servers to the config. Same error as below--with the name of the table randomly being a table from the model.

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