SQL: (SQL Server) The certificate chain was issued by an authority that is not trusted

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.

32 thoughts on “SQL: (SQL Server) The certificate chain was issued by an authority that is not trusted”

  1. 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?

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

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

  2. 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???

    1. Hi Jason,

      What is shown in your Trusted Root Certification Authorities? Is it the cert you generated or the parent CA's cert?

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

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

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

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

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *