SQL-Server - traffic encryption can't connect using only hostname

Copper Contributor
 

I have setup a certificate in SQL server configuration manager and can make encrypted connections fine using the FQDN of the server but not if I just use the hostname. I have other servers where I can use either the FQDN or the hostname.

Does anyone have any ideas on what I am doing wrong?

I've compared the certs of the 'working' and 'not working' servers and can't find a material difference (coz I don't know what to look for...). They are both proper trusted certs and both only reference FQDNs in the subject and SAN fields. Both are for BAG setups and encryption to all the listeners works fine (hostname or FQDN for older server but only FQDN for new server).

SQL 2019 standard edition. A VM in Azure

The error returned at the client is "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)

2 Replies

This error:

 

> SSL Provider, error: 0 - The target principal name is incorrect.

 

indicates that a Kerberos Service Principal Name does not exist for the name used by the client when connecting to the server.  Look in the SQL Server Error Log shortly after startup for messages mentioning Kerberos Service Principal Names (or SPN as they are somtimes known).

 

Take a look at an article I wrote about configuring SPNs at https://www.sqlserverscience.com/configuration/enable-kerberos-authentication-without-rebooting-sql-...