1

I am experiencing a "Target principal name is incorrect" error after upgrading SQL Server 2016 to 2017. The SQL Server 2016 instance has been uninstalled. The certificate which is registered in SQL Server Configuration Manager Protocols and is used to enable wire encryption is named with the FQDN of the server. It is the exact same certificate that was working successfully with SQL Server 2016.

I have noticed since the upgrade that I cannot logon to SQL Server 2017 using the FQDN of the server. I can only logon using [HOSTNAME]\[INSTANCE_NAME]. Why has this behaviour changed and how can it be restored? I am wondering whether restoring this behaviour will simultaneously resolve this connection error.

CR241
  • 1,565
  • 3
  • 18
  • 32
Guru Josh
  • 253
  • 1
  • 5
  • 11

1 Answers1

2

Ensure the Kerberos Service Principal Names are configured correctly, either by giving SQL Server the requisite permissions in Active Directory or by manually configuring them. Take a look at my blog for a quick method of configuring the SPNs that doesn't require rebooting or restarting SQL Server.

If that doesn't help when using an encrypted connection, check the "Accepted NTLM SPNs" property in the SQL Server Network Configuration inside SQL Server Configuration Manager.

In the image below, I have configured the accepted NTLM Service Principal Names to accept connections via the SPN for the instance name as well as the instance TCP port.

enter image description here

Configuring the setting like that allowed me to successfully login using the FQDN of the server, as well as the non-FQDN name (i.e. just the hostname). So, for instance I can connect to SERVERNAME\INSTANCE as well as SERVERNAME.domain.name\INSTANCE using encryption.

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323