I have a SQL Server set up to force encrypted connections from all client. It has a standard SSL certificate, using Let's Encrypt as the CA. Browsing to the server via a web browser verifies that the SSL certificate is valid and current.

However, if one checks Encrypt Connection setting in SSMS, one gets an error about invalid certificate.

TITLE: Connect to Server

Cannot connect to <redacted>


A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-2146893019&LinkId=20476


The certificate chain was issued by an authority that is not trusted



One can then remove the Encrypt Connection, connect anyway, which the connection is then successful. The dm_exec_connection will still show that connection as encrypted nonetheless.

As noted in the comments, Let's Encrypt was recently added to trusted list by Windows, so it might be the older OS not trusting Let's Encrypted certificate. However, that in itself does not explain why clients apparently are able to connect and get encrypted connections by default. If the client explicitly specify Encrypt Connection, then it becomes an issue. But why would it be? In both cases, the Trust Server Certificate is not enabled, so I would have expected the behavior to be the same.

Who's pulling who? What's really going on?

Bonus: If anyone can show a way to determine which certificate is in use for SSL connection that would be appreciated; the server has only one active SSL certificate but I have no way of verifying that it's the one that SQL Server is actually using to identify itself. For all I know, it might be waving around a expired certificate, which may explain why the error.

  • Where and how was the certificate generated? On the SQL Server itself? Dec 14, 2018 at 15:08
  • No, using Let's Encrypt.
    – this
    Dec 14, 2018 at 15:11
  • Let's Encrypt was only confirmed as trusted in late July 2018 - have you confirmed that the client machine in question trusts the issuing authority (Lets Encrypt)? Dec 14, 2018 at 15:14
  • That's the trouble - I can confirm this via a web browser but whether that's a fair test? Hence the bonus question.
    – this
    Dec 14, 2018 at 15:19
  • It sounds to me like the client computer doesn't trust Lets Encrypt. This guide may help - particularly the section around 'Trusted Root Certification Authorities/Certificates' - if this looks like your problem comment back and I'll add an answer. Dec 14, 2018 at 15:27

1 Answer 1


This did not come up in my original search but I think this related question has the answer, in particular:

One difference is that if you use self-signed certificate and the client is the one request encryption (with “Encrypt connection” option checked), then it will attempt to perform server validation on the certificate to verify the identity of the server machine so that it will be failed for a self-signed certificate since it hasn’t been signed by a trust root authority. For more information, please refer to http://blogs.msdn.com/b/dataaccess/archive/2005/08/05/448401.aspx.

Note that this is at variance with what is implied in the official documentation, suggesting that the certificate always will be checked. Evidently that is not the case. Thus, a client connecting to a SQL Server instance that has Force Encryption will be encrypted but without checking the identity of the server's certificate, unless the client explicitly sets the encryption form client-side.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.