0

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>

------------------------------
ADDITIONAL INFORMATION:

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

------------------------------
BUTTONS:

OK
------------------------------

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? – George.Palacios Dec 14 '18 at 15:08
  • No, using Let's Encrypt. – this Dec 14 '18 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)? – George.Palacios Dec 14 '18 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 '18 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. – George.Palacios Dec 14 '18 at 15:27
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, privacy policy and cookie policy

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