This is the most common issue in generic SQL Server environment and most likely due to Kerberos authentication issue. Just create required SPN’s on all involved SQL Servers and it should be working fine.
In our case, we have upgraded (in-place) SQL server from 2019 to 2022. After our successful upgrade of SQL Server to 2022, we could not able to connect to SQL server instance from outside the server.
MY SQL Server Setup:
3 node cluster with Always on high availability configured
All SQL instances are running under same service account [domain\sqlserversvc]
Windows version: 2016
ERROR:
The target principle name is incorrect. Cannot generate SSPI context.
Event Viewer:
This is most generic error and could not able to locate any errors related to Kerberos. We have enabled the logging to capture the error related SQL Server Kerberos authentication issue.
To log the error related to Kerberos from client machine:
We have reproduced the issue to log the error
- Launch Regedit as administrator
- Go to
Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters
- Create new DWORD 32 as LogLevel
Check the error related in the event viewer SQL Server (DB)
Now we have logged error is due to Kerberos but we have validated all SPN’s set and we have deleted a few and recreated as well with no luck.
Cause:
- It seems from SQL Server 2022, Service account had encryption types set to the default value “not-set” which means it’ll use RC4 to decrypt the Kerberos ticket
- RC4 is disabled by default in SQL machine
Resolution:
- Go to active directory and access the service account ([domain\SQLServerSVC])
- Go to Properties of the service account
- Open attribute editor
- By default the value will be 0X0 = ()
- Set the value of msDS-SupportedEncryptionTypes to 28
