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?

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

[Anonymous]

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

[motoray]

I have a ticket open now. I figured I'd try from both directions. At the moment, Microsoft is still having me troubleshoot really basic things and I'm hoping someone in the community has already seen (and fixed) this.

[AMD0791]

Did you ever get more information or resolution?  I am seeing the same issue.

I haven't checked on the config for trusted servers, but that seems like it would be an all-or-nothing proposition and it would never work instead of random, sporadic errors.

Thank you!

[motoray]

I was given a bandaid. There's a config file for the gateway...same one where you update the Trusted Servers. One of the flags in the file is "NextResultSkipped." They had me flip that to True. It killed the refresh errors, allowing the reports to refresh. I've not been able to get them to explain exactly what that does, but CoPilot says it basically tells the refresh not to get data it doesn't need to refresh the report--but I have no idea how the engine determines that. The tech support folks really really wanted me to close the ticket before the end of the year with the promise that they'd reach out to me after the 3rd week of the new year--but I have yet to hear from them and have been too busy to reach out. The whole process this time has been less than satisfactory. I actually sent a request to have the ticket escalated and while that may have happened, they've forced me to continue working with someone I have a very hard time communicating with. 

Ping me if you want to know more about the band aid and I can tell you exactly what to do. 

[CaptainBI]

Hi @motoray , are you still using the bandaid till now? We're past 6 months of frustration as well.

[motoray]

@CaptainBI, what @JohnJ2 shared below is the thing Microsoft had us do. The file he mentioned is also the config file where you add in server names if you haven't implemented encrypted connections yet (like us).

[JohnJ2]

After working extensively with Microsoft, was determined SQL connection was hanging causing the timeout error.  To resolve:
Go to:

C:\Program Files\On-premises data gateway\Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config

Change <value> setting 

<setting name="NextResultSkipped" serializeAs="String">

       <value>True</value>

</setting>

Since making this change we haven't had any additional errors for this.  This solution is not documented anywhere.

[motoray]

That's the exact same one they had me implement. After each gateway upgrade, they'd have me flip the switch back to False and we'd watch for failures. When the failures came back (as they have since the Jan. 2025 update), we'd go back and flip the switch back.

@JohnJ2, did they ever explain what that switch actually does and how? I never got a good answer from Microsoft. CoPilot said something about it telling the Gateway to only load stuff it needs to refresh, but that left me with more questions than answers. When I asked the question of Microsoft, the best answer they could provide was something like, "when you change that value to true, your errors go away."

[JohnJ2]

Our understanding (accurate or not) is that the (SQL) connection hangs for whatever reason and switching that setting forces it to drop ... or some such thing. We focused on one report that was erroring and it happened to be using an SQL connection otherwise it was chasing ghosts.

[motoray]

Thanks for that explanation @JohnJ2 -- it's more than I got from anyone else. All of our failures have been with on-prem SQL connections through the gateway. 

Just did another call with a different Microsoft tech and passed off more dataset and request IDs and log files for them to look at.

[anon_jens]

Any update for you guys? @motoray 

[CaptainBI]

Long story short after our own support ticket: There is no other option than to change the setting on the gateway as described. I can confirm refreshes no longer fail.

They said that if changing this setting has positive results, it means the underlying cause is a network delay in critical operations. This setting skips the processing of sensitivity label metadata, which is a time-critical operation causing a failure for the wider refresh. Most queries don't have an associated label. An update in the future will include changes to be more resilient with such network latency issues and remove this setting.

[motoray]

I've been out of the office a while, so a colleague has been keeping this going with Microsoft. We were bascially told that they were going to change the configuration to default to True in the config file going forward, but there were no other details beyond that. This has been a most unsatisfatory support experience. Everything worked fine up until about July of last year and then it's been failures since.