Let's Encrypt certificate on SQL Server 2019 - "The target principal name is incorrect"

bg flag


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.


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

Manu avatar
us flag
Stupid question but just to be sure.. You also connect to the instance by using the FQDN and not just the hostname, localhost or even the IP, right?
dave_thompson_085 avatar
jp flag
FYI ECDSA (and ECDH[E|A]) in TLS does not depend on 1.2; they were first defined on 1.0 and 1.1, although 1.2 tweaked them for new sigalg handling (see rfc5246 A.7). By my records Vista (2007) added ECC (with only 1.0) and Win7 (2009) added 1.1, while only Win8 (2012) added 1.2. But AIR no public CA issued ECC certs until about 2015 (USgovt may have, with NSA then pushing Suite B). Anyway today using any protocol below 1.2 is generally a bad idea, and increasingly is outright prohibited.
pk flag
"Target Principal Name is incorrect" is a horribly worded error message. It really means that the server name in the connection string doesn't match the CN in the cert. Double check that. use FQDN's. There is also "HostNameInCertificate" which you can add in the connection string in recent drivers. Also, confirm in the ERRORLOG that the cert was loaded successfully (it logs the thumbprint after SQL restarts)
pk flag
There are also some requirements on the certificate iteself:
lr flag

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.


Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.