Forum Discussion
Unable to create gateway connection for SQL Server
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>"
9 Replies
Please see this stack overflow post with a potential solution and some info on the process
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.
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.
Thank you.
Adding server name to the field "SqlTrustedServers" in Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config works for my case.
