I am experiencing error when connecting MY DB which is in VM Role(I have SQL VM Role) from Azure Website. Both VM Role and Azure Website are in West zone. I am facing following issue:
SqlException (0x80131904): 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.)]
I am able to connect to my DB using SSMS. 1433 port is open on my VM role. What is wrong with my connection?
You likely don't have a CA signed certificate installed in your SQL VM's trusted root store.
If you have
Encrypt=True in the connection string, either set that to off (not recommended), or add the following in the connection string:
SQL Server will create a self-signed certificate if you don't install one for it to use, but it won't be trusted by the caller since it's not CA-signed, unless you tell the connection string to trust any server cert by default.
Long term, I'd recommend leveraging Let's Encrypt to get a CA signed certificate from a known trusted CA for free, and install it on the VM. Don't forget to set it up to automatically refresh. You can read more on this topic in SQL Server books online under the topic of "Encryption Hierarchy", and "Using Encryption Without Validation".
Got hit by the same issue while accessing SQLServer from IIS. Adding TrustServerCertificate=True didnot help.
Could see a comment in MS docs: Make sure the SQLServer service account has access to the TLS Certificate you are using. (NT Service\MSSQLSERVER)
Open personal store and right click on the certificate -> manage private keys -> Add the SQL service account and give full control.
Restart the SQL service. It worked.
I ran into this error trying to run the profiler, even though my connection had Trust server certificate checked and I added
TrustServerCertificate=True in the Advanced Section. I changed to an instance of SSMS running as administrator and the profiler started with no problem. (I previously had found that when my connections even to local took a long time to connect, running as administrator helped).
User contributions licensed under CC BY-SA 3.0