Unable to create gateway connection for SQL Server

[NothingZ]

Please help!

I'm unable to create gateway connections for my SQL Server data source. However, on PBI desktop, everything works fine as usual. Once published to the service, it shows error creating the new SQL server connection.

The gateway and SQL server connection works perfectly before.
What has changed: The machine where both the gateway and SQL server are on has been move to another physical location, so that the IP address is different. I have changed the IP4 address accordingly on TCP/IP configuration, and SQL Server address on PBI desktop so that it could get the data from that source.

What I have done so far:

• Checked all the SQL server settings: all(including login credentials) are OK as it works fine before.
• Restarted gateway/SQL Server
• Uninstall and install the newest version/previous version of gateway
• The SQL server data source is un-encrypt connection
• Tried create a new data connection and got same error
• Looked into Gateway error logs and found messages like "InnerMessage=<ccon>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.)</ccon>"

[NothingZ]

I get this error in system log:

The certificate received from the remote server was issued by an untrusted certificate authority. Because of this, none of the data contained in the certificate can be validated. The TLS connection request has failed. The attached data contains the server certificate.
The SSPI client process is Microsoft.PowerBI.EnterpriseGateway (PID: 6376).

Is there any methods to add encryption/trust server cert into SQL server connection string for gateway? I can't find any more parameters to add when creating gateway connections.

[Deku · Accepted solution]

Old solution from Microsoft https://powerbi.microsoft.com/en-us/blog/ssl-security-error-with-data-source/

And yet another one https://learn.microsoft.com/en-us/power-query/connectors/sql-server#sql-server-certificate-isnt-trus...

[NothingZ]

Thank you.
Adding server name to the field "SqlTrustedServers" in Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config works for my case.

[NothingZ]

Thank you for your infomation. I have tried following steps and adding entries on SqlTrustedServers field in gateway config file, but still it would not allow me to create such SQL Server on-premise gateway connection😓. 

Another thing I tried: OLE DB connection type works fine in gateway new connection creation as it provides "TrustServerCertificate=True" in connection string. However, OLE DB data souce from PBI desktop once published to the service, it becomes SQL Server connection type again and be un-able to add such parameter in the connection.

[Deku]

Think it might be time for a support ticket 

[Deku]

Please see this stack overflow post with a potential solution and some info on the process

[NothingZ]

Hi, thank you for your quick reply. I'm not sure how to add those "TrustServerCertificate=True" into connection string when I'm creating SQL Server on-premise gateway connections. Could you please guide me further?

Plus, the connection works okay before my network environment got changed.

[Deku]

Steps suggested by Microsoft here

[NothingZ]

Thanks a lot.

My SQL connection in PBI desktop has disabled the encryption setting before. Along with my SQL server databases have un-checked the "Encryption Enabled" options.

Still the issue persists, I'm not sure what to do next.