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

164

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?

azure-web-roles
azure-vm-role
asked on Stack Overflow Jul 12, 2013 by ZafarYousafi • edited Nov 25, 2018 by Andrea

7 Answers

331

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".

answered on Stack Overflow Jul 15, 2013 by Thiago Silva • edited Jul 15, 2019 by Dale K
52

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

answered on Stack Overflow May 24, 2018 by ct.tan
29

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

answered on Stack Overflow May 1, 2017 by vmanne • edited Jul 15, 2019 by Dale K
4

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.

answered on Stack Overflow Aug 10, 2016 by Bhavjot
0

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.

answered on Stack Overflow May 10, 2018 by Manas • edited May 10, 2018 by Zoe
0

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).

answered on Stack Overflow Jun 28, 2018 by Bill • edited Jul 15, 2019 by Dale K
0

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

answered on Stack Overflow Dec 9, 2018 by Kanna Reddy • edited Jul 15, 2019 by Dale K

User contributions licensed under CC BY-SA 3.0