Assistance Required with SQL Server Standard Mode On-Premises Gateway Connection Error

[Pavan_123456789]

I am encountering an error while attempting to connect to SQL Server Standard Edition through the on-premises data gateway. The issue seems to be related to the configuration or connection in Standard mode. Please note that I have provided the correct credentials, so the problem does not appear to be related to authentication.

Could you kindly assist in reviewing the setup or provide guidance on resolving this error? Any help you can offer would be greatly appreciated. Please let me know if you need further details or logs to assist with troubleshooting

Unable to update connection credentials. Unable to connect to the data source. Either the data source is inaccessible, a connection timeout occurred, or the data source credentials are invalid. Please verify the data source configuration and contact a data source administrator to troubleshoot this issue.



Details: Production Gateway: Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) Please have this information handy if you choose to create a support ticket.

[Anonymous · Accepted solution]

Thank you @Daryl-Lynch-Bzy 

Hi, @Pavan_123456789 

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.
You can solve this problem by doing the following:
In the gateway configuration (Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config), you can use a new setting called "SqlTrustedServers" in the same way:

<setting name="SqlTrustedServers" serializeAs="String">
    <value> *spintoptoys,sql*,*.contoso.com</value>
</setting>

This will match the local SQL servers "sqlservertopspintoptoys", "sql-yyz-5", "sqlazure.contoso.com" and "SQL5150.contoso.com ".
Note that wildcards expand only in the server name, not in the domain extension. The example above doesn't match the "SQL33.subsite.contoso.com" example.
Also note that SQL Server instances that require a port number cannot specify the port in the usual way, i.e., "*spintopsql,1443", because a comma indicates the new server name. Instead, add a wildcard at the end, for example: "spintopsql*" or ".contoso.com"

You can refer to the following official documentation:

Power Query SQL Server connector - Power Query | Microsoft Learn

To prevent unexpected errors, you need to back up your gateway profile in advance and work with your IT department to practice this method.

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.

[Pavan_123456789]

Hi @Anonymous  

Thank you so much for providing the solution. It looks promising, and I believe it will work for my requirements. I will test it and get back to you with any feedback.

[Anonymous]

hi, @Pavan_123456789 

Thank you very much for your reply. Hope to be able to solve your current problem with it.

Best Regards

Jianpeng Li

[Pavan_123456789]

Hi @Anonymous 

Thank you so much for your assistance. The solution works perfectly!

[Pavan_123456789]

Hi @Daryl-Lynch-Bzy  Thanks very much I will check with my Network team 

[Daryl-Lynch-Bzy]

Hi @Pavan_123456789 - I am sorry, but you really need to discuss this with your DBA and Networking team to resolve the issue, or create a Microsoft Support ticket to trouble-shoot this type of issue.