Sql-server – `Encrypt Connection` causes an invalid certificate error even though server-side encryption is forced

Securitysql serverssl

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.

Best Answer

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.

Related Question