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!

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

[v-jianpeng-msft]

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.

[lbendlin]

Since you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi

[lbendlin]

On-premises data gateway October 2024 release | Microsoft Power BI Blog | Microsoft Power BI

[sherin1402]

I have had the same issue, but adding all the servers in the Config file as Trusted SQL servers, have resolved it! It doesnt look like a bug but something that we actually have to do going forward?? Can anyone confirm? Thanks

Link:On-premises data gateway October 2024 release | Microsoft Power BI Blog | Microsoft Power BI

[motoray]

Wish that had resolved it for me. We'd skipped several versions of the upgrade since June in the hopes one of the newer versions would fix it.

[v-jianpeng-msft]

Hi, @motoray 

Thank you for your reply. You have opened a ticket. You can communicate your ideas with the engineer corresponding to the ticket at any time. If you get a solution later, you can share it here so that other members of the community can quickly find answers when they encounter similar problems.
Thank you again for your contribution to the development of the community.

Best Regards

Jianpeng Li