Received error payload from gateway service with ID 132386

[t_young]

"Received error payload from gateway service with ID 132386: SqlException encountered while accessing the target data source."

Running into an issue withing my Power BI workspaces.

I've had my gateway and data sources set up for a few years now and never had any issues, then seemingly overnight my gateway is no longer able to connect to any of my SQL Server data sources. I can still connect to local files, but nothing from a SQL Server.

Not sure if there was an update or something in the past week or 2 that would have thrown this off, but even re-installing the gateway didnt seem to resolve the issue. Not sure where to go from here.

[t_young · Accepted solution]

My IT team helped resolve the issue, but i believe the issue was caused due to an update on Microsoft's side that forces you to set up trusted connections for SQL servers. So the solution was to go in and update our SQL Server data sources within our Gateway.

[v-menakakota]

Hi @t_young ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[t_young · Accepted solution]

My IT team helped resolve the issue, but i believe the issue was caused due to an update on Microsoft's side that forces you to set up trusted connections for SQL servers. So the solution was to go in and update our SQL Server data sources within our Gateway.

[jobinpjoseph]

Hi, 

I wanted to understand more about the trusted connection setup.

When I check the available authentication methods in the Gateway settings, I only see options for Basic, OAuth, Service Principal, or Windows Authentication. However, I don’t see a specific option labeled "Trusted Connection".

Could you clarify what exactly is meant by a trusted connection in this context? Does it refer to using Windows Authentication with Kerberos delegation, or is it related to enabling encrypted connections and trusted certificates for SQL Server?

Would appreciate any guidance on how to correctly configure this within the Gateway settings.

Thanks,

[B_Letteer · Accepted solution]

Hello,
Here is a detailed breakdown of the fix. Hope this helps others with the same issue.

SQL Server connections created in the On-Prem Data Gateway have an offline status.

This as a known issue and confirmed that it is a by-design change. Users with gateways connected to SQL untrusted servers might experience issues while updating the gateway credentials, as the datasource status shows offline.

To resolve this issue, you need to update your SQL connection to use SQL trusted servers. Please follow the steps below:

1. On the local machine where the on-premises data gateway is installed, navigate to C:\Program Files\On-premises data gateway.
2. Make a backup of the configuration file named -Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config.
3. Open the original Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config configuration file and locate the SqlTrustedServers entry.
4. Update the SqlTrustedServers value with the names of the SQL servers to trust and connect to. The value contains a comma-delimited list of server names and supports * as a wildcard. For example:

contososql*,mysvr

In this example, contososql*,mysvr matches contososql6, contososqlazure, and mysvr, but doesn't match mysvr.microsoft.com.

For more detailed instructions, you can refer to the public documentation: Power Query SQL Server connector - Power Query | Microsoft Learn

[gabreu]

@B_Letteer 
Thank you for providing this solution.  Worked like a charm!🌟

[v-menakakota]

Hi  @t_young ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-menakakota]

Hi @t_young ,

Thank you @collinq  for providing helpful solution,the response is accurate and may help you in ressolving the issue. I wanted to check if you had the opportunity to review the information provided by collinq . Please feel free to contact us if you have any further questions.

Regards,
Menaka

[collinq]

Hey @t_young ,

That is most likely a network or SQL issue.  And, since you have stated that this worked in the past that really makes me think that somethign changed on your network.  Maybe the IP list of "whitelisted" IP's changed.  Or the IP's of servers changed, or the security settings on SQL or firewalls was changed. It could even be that the account being used to get there has changed.

I would talk to your SQL folks to confirm no database changes or SQL Server changes.  And, I would talk to the network folks.  In fact, you probably should do a network trace with them to see where exactly the fail comes in - using something like Fiddler.

Since it did work and now doesn't the answer is almost in the "what changed" part and that message is about being unable to get to the data.