Summary

I'm having trouble getting a certificate issued by Let's Encrypt R3 to work on SQL Server 2019. When using the certificate for SSL but not trusting the server certificate explicitly (In SSMS, I check "Encrypt connection" but not "Trust server certificate"), all authentication fails with the following error message:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The target principal name is incorrect.) (Microsoft SQL Server, Error: -2146893022)

Server Configuration

Things I verified immediately:

• The certificate, and all certificates in the chain, are installed on the computer certificate store.
• The user under which the SQL server service runs has read permissions on the private key.
• The certificate is correctly configured in the Configuration Manager.
• The common name of the certificate matches the FQDN of the server.
• The server is in the domain and reachable.

Other things that may be worth noting:

• The server runs on a named instance, not the default instance.
• Even though this post focuses on SQL Server 2019, I've reproduced the problem on SQL Server 2022.

Background

We are currently in the process of switching over from an internal CA to Let's Encrypt. The certificate for the SQL server that was in use up until now and recently expired, issued by our internal CA, worked for encrypted connections without any issues. However, after installing the new certificate issued by Let's Encrypt, encrypted connections broke. I found the workaround of adding TrustServerCertificate=true to the affected connection strings, but I don't see this as a permanent solution as that seems really unsafe.

What I've tried so far

Both local and remote authentication fail with the same error.

Authentication fails with both local and domain accounts.

Forcing encryption on the server side or not makes no difference, as expected.

I got the suggestion that MSSQL may not support ECDSA by default, which is the default key type as of Certbot 2.10 (which is the ACME client I use). It is my understanding that ECDSA depends on TLS 1.2, which, indeed, wasn't enabled. I edited the registry according to this article: https://learn.microsoft.com/en-us/windows-server/security/tls/tls-registry-settings?tabs=diffie-hellman#tls-dtls-and-ssl-protocol-version-settings. I enabled TLS 1.2 both in Server and Client, and enabled it by default. All other protocols that are currently used by the clients were disabled by default and enabled, so as to be as nondisruptive as possible. Unfortunately, the problem remained.

In that same vein, I tried passing the parameter --key-type rsa to Certbot to create an RSA key instead. This also made no difference. I noticed that the signature still uses ECDSA, though, while the old certificate has an RSA signature. This is the only difference I can see between the two certificates. Could this be an issue?

I also thought the error sounded like the certificate common name doesn't match the server FQDN, but this is not the case.

Unencrypted connections, or trusting the server certificate, both work, as mentioned above.

I can edit this post with any configuration/other files that might be necessary.

There might still be an Issue with the Certificate Chain, so recheck that.

After that, make sure that you added the DB User Read permissions in the Certificate Management and NOT via the default file permission management.

To do so:

1. Search for "certificates" and open "Manage computer certificates".
2. Open the Personal Logical Store and then the Certificates Object.
3. Now right-click the SSL Certificate you want to set the permissions for.
4. In "All Tasks" select "Manage Private Keys..."
5. A Separate window should open where you can add the SQL Server User.
6. When you added the user, set the Permission to read and Apply the Changes.

Now Restart the SQL Server Service and pray that it did work.

I hope this comment was helpful.