Are you trying to connect to a SQL Server instance and ending up with the error:
The certificate chain was issued by an authority that is not trusted
You aren't alone.
SQL Server 2005 introduced authentication encryption (by default) in the SQL Native Access Client (SNAC). SQL Server will self-generate a certificate that's then used unless you replace it with your own certificate.
If you do use your own SSL (Secure Sockets Layer) certificate for SQL Server, unless it's a publicly trusted certificate, your client system will need to trust that certificate. Generally that means that you'll need to list your own certificate authority (CA) as a trusted publisher on each of your client systems. Then that would work well.
And that's often the problem that causes the above issue.
Trusting the Server
But what if you just want to trust the certificate that was self-signed by the server? Well there's an option for that (Trust server certificate), in the Options section of the connection dialog:
Chances are that if you just check that box, you'll then be fine.
Note that there's also an option to turn off encryption (by unchecking Encrypt connection). While it would also "fix" the issue, that's not the best option to choose here.
And I'm posting this so that one day in the future when I forget what this was about, I'll find this post.
I keep reading that this isn't a good idea on a production server, but if the connection is encrypted anyway… What could be the problem with ticking that box?
Hi Celso, totally depends upon your level of concern. If all you want is to ensure the authentication is encrypted, then it's just fine. If you want to guarantee you're talking to the server you think you're talking to, you need to install a real SSL certificate that's already trusted.
Regards,
Greg
how do you get to that " connect to server" page to check that box?
Hi Celso, when you are about to connect (Connect to Server dialog from Object Explorer > Connect or from the New Database Query icon in the toolbar), there is an Options button near the bottom right hand side. It opens these options up, and this is the second tab.
Working from the morning to resolve and your tip helps in finding the solution.
Hi Lok, glad to hear it helped.
THANK YOU FOR THE SOLUTION NOW IAM ABLE TO LOGIN
You are most welcome. Glad it helped you.
This really helped me, you have no idea
Thank you
Hi Vera, so glad to hear it helped. You are most welcome.
Nice! This helped me..
Glad to hear it helped Ed.
Thank You
You are most welcome
Thank you! This works for me, since I'm already using SQL Server Authentication.
You are most welcome !
OK, this is driving me crazy. I have a self-signed certificate in place on the SQL Server, but I have imported that same certificate into Trusted Root Certification Authorities and still get an error "the certificate chain was issued by an authority that is not trusted." What am I doing wrong???
Hi Jason,
What is shown in your Trusted Root Certification Authorities? Is it the cert you generated or the parent CA's cert?
Hi
I am using SQL 2012 and the Trust server certificate option is not there.
How do i resolve.
if i look at my protocols in SQL configuration manager, there is no certificate.
thank you
Hi Ahmed, it's a client setting. Have you upgraded to a recent version of SSMS ? When you do that, you get more recent SQL Server tools. And do you have a recent SQL native client library (either OLEDB or ODBC) ?
Thank you Greg.
I have requested this info and will share the feedback i receive.
I found the 'trust server certificate' option on another machine that has SQL 2016.
Thank you once again..
You are most welcome
You are a saint. Thank you.
Glad to hear it helped.
I'm not getting this problem with SSMS….but EF Core. Ideas?
You'll need to modify your SQL Server connection string. Add TrustServerCertificate=true
Instead, you can also disable encryption by adding encrypt=false but that is not recommended.
Hope this helps
You're the best!
You are most welcome
Great. Such a simple fix. Never would have found it! Thanks.
Glad to hear it helped. You are most welcome
Hello, I get the same error using excel and trying to connect server.
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.)
where can I find "Trust server certificate"?
Many Many thanks for your help.
Sorry but unsure on the Excel options. One way I guess would be to generate a certificate, configure it as your SSL certificate for SQL Server, and add it to the certificates in your root store, for trusted publishers. That's harder, but I'm not sure of options for setting those connection details directly in Excel.