The certificate chain was issued by an authority that is not trusted when connecting DB in VM Role from Azure website

Question

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

User · Answer

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.

User · Answer

If You are trying to access it through Data Connections in Visual Studio 2015  and getting the above Error  Then Go to Advanced and set  TrustServerCertificate True for error to go away

User · Answer

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 · Answer

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   TrustServerCertificate True   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

User · Answer

I got this Issue while importing Excel data into SQLDatabase through SSMS  The solution is to set TrustServerCertificate   True in the security section

User · Answer

The same can be achieved from ssms client itself  Just open the ssms  insert the server name and then from options under heading connection properties make sure Trust server certificate is checked

User · Answer

If you re seeing this error message when attempting to connect using SSMS  add TrustServerCertificate True to the Additional Connection Parameters

User · Answer

If you re using SQL Management Studio  please goto connection properties and click on  Trust server certificated

[azure-web-roles] "The certificate chain was issued by an authority that is not trusted" when connecting DB in VM Role from Azure website

Examples related to azure-web-roles

Examples related to azure-vm-role