Issues with On-premise Gateway - Received error payload from gateway service with ID 328575

[Phoenix529]

I was using On premise gateway with my Personal work account to connect to SQL server. There were multiple connections using the on premise gateway.

But as per IT guidelines, I had to move it to a service account. When I did migrate the existing gateway to a new service account, the refreshes from that gateway are still running fine. But when you check for Connections and gateways, it shows offline with below error.
"Received error payload from gateway service with ID 328575: SqlException encountered while accessing the target data source."

This is not creating any issues as such as the refreshes using this on-premise connection are fine, they do not fail.

But the connections should not be showing offline either ways.

These are the things I have tried till now, but there is no solution/reason as to why it shows offline.

• Service account has same user roles and permissions as my account
• Created a new On Premise server using the service account as it is.
• Checked if Service account is able to access the SQL Server with trusted certificates.

I may have tried some more suggestions but to no avail, the connections to the gateway still show offline even though the On premise gateway shows online.

Any suggestions why this has happened?

Thanks in Advance.

[v-saisrao-msft]

Hi @Phoenix529,

Could you please confirm if the issue was resolved after raising a support case? If so, we’d appreciate it if you could share the solution to help others in the community. As we haven’t heard back, we’re closing this thread. For any further issues, please raise a new thread in the Microsoft Fabric Community Forum — we’ll be happy to assist.

Thank you for being part of the Microsoft Fabric Community.

[Phoenix529 · Accepted solution]

Hi @v-saisrao-msft ,

Apologies for the delayed response.

The issue has now been resolved; however, we did not implement any of the suggested solutions from the support ticket or related discussions. None of the recommendations provided led directly to the resolution.

Based on our observations, the issue appears to have resolved itself over time. We suspect that updates to the On-Premises Data Gateway in newer versions may have addressed the underlying problem.

We appreciate everyone's time and effort in assisting with this matter

Best regards.

[Phoenix529]

Currently using this version and as you can see the connections using that Cluster are showing as Online without any errors as before.

[Quandru]

You may find that the issue here is to do with you having a later version of the data gateway app once you've gone back to install it again with a service account. Recent versions changed how the gateway service handles SQL connections in particular, requiring them to be defined as "trusted" prior to allowing them to be used.

On the server with the gateway installed, browse to C:\Program Files\On-premises data gateway and find the appropriate gateway config file, which will be Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config in your case. Make a backup of the file before you change anything.

Edit the file in a notepad window run as administrator, or use Notepad++ or equivalent, and find the entry in the XML with the tag SqlTrustedServers, which by default will look like this:

      <setting name="SqlTrustedServers" serializeAs="String">
        <value />
      </setting>

and amend it to include the values of your server hostnames, like this:

      <setting name="SqlTrustedServers" serializeAs="String">
        <value>SQLserver.domain.com,SQLcluster*.domain.local</value>
      </setting>

You can see that you can specify a comma separated list with the hostnames of your servers as used in the gateway config in MS Fabric or PowerBI connections, and can also use wildcards to reflect some or all of the hostname.

You could even trust all SQL connections through the agent like this:

      <setting name="SqlTrustedServers" serializeAs="String">
        <value>*</value>
      </setting>

but I'd not recommend it if you can avoid it, you introduce a range of risks and attack vectors.

Restart the gateway service on the machine it runs on, which is called On-premises data gateway service and then refresh the status in Fabric and if this was your cause, you should see them immediately come online.

[Pulendar]

We have been facing the similar issues with payload errors. We also added the SQL servers names into Gateway config file as per the recent recommendation by MSFT. **Issues seems to specific while configuring the connection with Windows Authentication, however with SQL/Basic login the connections are able to show online. Not sure this might be involve some OS level support to ensure to no issues with Window Authentication from the gateway server. We are using 2016 Windows Server OS. still wating for the concrete resolution form Microsoft. 

[v-saisrao-msft]

Hi  @Phoenix529,
Thank you for reaching out to the Microsoft Fabric Forum Community.

From your description, it appears that although you're On-Premises Data Gateway is online, the specific connections using the gateway are displaying as offline, even though data refreshes are successful. 

• Go to Manage Gateways in Power BI Service, select SQL_SERVER, and ensure all data sources are mapped to the new service account with updated credentials. Incorrect mappings may cause connection issues even if the gateway appears online. 
• If the issue continues, reinstall the On-Premises Data Gateway using the service account to ensure correct registration and permissions. After reinstalling, reconfigure the data sources and test the connection. 

Please refer to the solved links for more details: 

Solved: Received error payload from gateway - Microsoft Fabric Community 

Solved: Received error payload from gateway service with I... - Microsoft Fabric Community 

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

[Phoenix529]

Hi @v-saisrao-msft ,

I tried updating the credentials in the connections and it gives me this error.

I have already re-installed the gateway once but that does not seem to solve the issue.

Tried it with all the available authentication modes as well (Except Service Principal)

I have also accessed SQL server using these service account credentials and it seemed to have worked fine.

Unsure what needs to be done.

[v-saisrao-msft]

Hi @Phoenix529,

Since you are encountering this issue repeatedly, please raise a support ticket so that one of the engineers can assist you.

Below is the link to raise the support ticket.

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Thank you.

[Phoenix529]

Hi @v-saisrao-msft ,

The ticket has already been raised but by someone else on our behalf. 
Fabric Support was asking us to reboot/restart our SQL Server (Which is not feasible). We have inhouse applications running on the 2012 version of SQL, which would be a huge pain to update thus, not feasible either. 

They are still looking into the ticket with no resolution yet, which is why I thought of trying the forum to see if someone else is facing similar issues as well.

PS: There are no refresh failures, just that when we refresh the connectors to that gateway, they show as offline with the above error. 

It ideally should not be showing any error as it works. It seems to me that there could be some issue with the new versions of the On-Premise gateway  or with the older version of SQL 2012 but I am just speculating here.

Thank you.

[v-saisrao-msft]

Hi @Phoenix529,

Thanks for the update. Since a support ticket is already raised, I recommend waiting for Microsoft Fabric Support to resolve the issue, as they can provide a targeted solution, especially considering the potential compatibility concerns with SQL Server 2012.

Once the issue is resolved, it would be great if you could share the outcome here, as it will help other community members.

Thank you.

[Phoenix529]

Hi @v-saisrao-msft ,

I will update here as and when I have any solution for this issue.

Thank you for all your help.

Best regards.

[v-saisrao-msft]

Hi @Phoenix529,

Sure, Thank you.