Environmental details:

SQL Server Computer: YPSQL1

SQL Server Service Account: YPSQLSvc

Remote Client : YPSQL2

Domain Controller: YPDC

Issue: Trying to connect to SQL Server instance installed on YPSQL1 server from remote client machine YPSQL2

Issue:

Unable to connect to SQL Instance YPSQL1 from remote machine YPSQL2, getting below error while connecting:

“The target principal name is incorrect.  Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)”

Cause:

There are two reasons why we see the above error message

SQL SPN registration mismatch issue
EncryptionTypes mismatch issue

Reason 1: SQL SPN mismatch issue

Validate SPN registration status

We need to confirm the account configured to run SQL Server Service. In my server the name of the service account is “YPSQLSvc”

Let’s check the SPN registration status for the service account named “YPSQLSvc”

From SQL Server YPSQL1-> Open CMD -> Execute Setspn -L SQLSvc

There are no SPN registered for this service account. Errorlog shows the required SPNs and the status of registration.

2024-10-04 02:58:55.66 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/YPSQL1.yp.lab ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

2024-10-04 02:58:55.66 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/YPSQL1.yp.lab:1433 ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

Let’s register the SPN for the SQL Server Service account: -> We need to have proper permission to make changes in Active Directory user attributes. Domain Admin permission holder can modify the ServicePrincipalName (SPNs) but not the regular users.
From Domain Admin account -> Launch CMD -> Register the SPNs

Setspn -S MSSQLSvc/YPSQL1.yp.lab YPSQLSVc

Setspn -S MSSQLSvc/YPSQL1.yp.lab:1433 YPSQLSVc

After the successful SPN registration, restart the SQL Server Service on YPSQL1.
Then try to connect to YPSQL1 SQL instance from client machine YPSQL2.

If we are still facing same issue even after correcting the SPN’s issue, please follow the next steps.

Reason 2: EncryptionTypes mismatch issue

From the client machine YPSQL2, check the SYSTEM event log and find if event ID 4 logged or not.

Log Name:      System

Source:        Microsoft-Windows-Security-Kerberos

Date:          10/4/2024 3:02:55 AM

Event ID:      4

Task Category: None

Level:         Error

Keywords:      Classic

User:          N/A

Computer:      YPSQL2.yp.lab

Description:

The Kerberos client received a KRB_AP_ERR_MODIFIED error from the server SQLSvc. The target name used was MSSQLSvc/ypsql1.yp.lab:1433. This indicates that the target server failed to decrypt the ticket provided by the client. This can occur when the target server principal name (SPN) is registered on an account other than the account the target service is using. Ensure that the target SPN is only registered on the account used by the server. This error can also happen if the target service account password is different than what is configured on the Kerberos Key Distribution Center for that target service. Ensure that the service on the server and the KDC are both configured to use the same password. If the server name is not fully qualified, and the target domain (YP.LAB) is different from the client domain (YP.LAB), check if there are identically named server accounts in these two domains, or use the fully-qualified name to identify the server.

As per the above error message observed on client machine, it’s clear that there is a failure in decrypting Kerb Tickets.
Please consult your Active Directory Support team and take help to validate the settings to resolve the issue.

For SQL Server Instance hosted machine: YPSQL1

Lauch Active Directory Users and Computer MMC -> Client View -> Enable “Advanced Features”

Locate the SQL Server Computer object YPSQL1
Right click-> Properties

Click Attribute Editor -> select Attributes: msDS-SupportedEncryptionTypes -> click Edit

As per the above value 24 is for AES128 and AES256 EncryptionTypes. This means YPSQL1 allows and communicate with only AES128/AES256 type encryption methods.

For SQL Server Service Account named “YPSQLSVc”

Now validate the above same steps for SQL Server Service account named “YPSQLSvc”
Right click-> Properties

Click Attribute Editor -> select Attributes: msDS-SupportedEncryptionTypes -> click Edit

As per the above value <not set> means everything is allowed (all the encryption types).
So clearly there is a mismatch with “msDS-SupportedEncryptionTypes” attribute for SQL Server Instance hosted computer and SQL Server Service account. SQL Server Service Account may be trying to communicate via RC4 encryption mechanism with SQL Server Computer, however SQL Server Computer is set to accept only AES 128/ SES 256 encryption mechanism.

SQL Server Computer: msDS-SupportedEncryptionTypes -> 24 (AES128/AES256)

SQL Server Service Account: msDS-SupportedEncryptionTypes -> Not Set

Solution to resolve the issue is to correct the attribute msDS-SupportedEncryptionTypes mismatch issue for both SQL Server Server computer account (YPSQL1)) and SQL Server Service account (SQLSvc)
Made changes to SQL Server Service account “SQLSvc” to use AES 128/ AES 256 Encryption types.

Now both SQL Server Computer object and SQL Server Service account object matches the Supported Encryption Types.

SQL Server Computer (YPSQL1): msDS-SupportedEncryptionTypes -> 24 (AES128/AES256)

SQL Server Service Account (SQLSvc): msDS-SupportedEncryptionTypes -> 24 (AES128/AES256)

It may take some time for the above changes to replicate based on your Active Directory replication process. You could try to do GPUPDATE /FORCE or wait for the replication to complete.
Then try to connect SQL Server instance YPSQL1 from the remote client YPSQL2. It should connect successfully now.

Reference articles: Decrypting the Selection of Supported Kerberos Encryption Types – Microsoft Community Hub

​Environmental details:
SQL Server Computer: YPSQL1
SQL Server Service Account: YPSQLSvc
Remote Client : YPSQL2
Domain Controller: YPDC
Issue: Trying to connect to SQL Server instance installed on YPSQL1 server from remote client machine YPSQL2
 
Issue:
Unable to connect to SQL Instance YPSQL1 from remote machine YPSQL2, getting below error while connecting:
“The target principal name is incorrect.  Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)”

 
 
Cause:
There are two reasons why we see the above error message

SQL SPN registration mismatch issue
EncryptionTypes mismatch issue

 
Reason 1: SQL SPN mismatch issue

Validate SPN registration status

We need to confirm the account configured to run SQL Server Service. In my server the name of the service account is “YPSQLSvc”

Let’s check the SPN registration status for the service account named “YPSQLSvc”

From SQL Server YPSQL1-> Open CMD -> Execute Setspn -L SQLSvc

 
There are no SPN registered for this service account. Errorlog shows the required SPNs and the status of registration.
2024-10-04 02:58:55.66 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/YPSQL1.yp.lab ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
2024-10-04 02:58:55.66 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/YPSQL1.yp.lab:1433 ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

Let’s register the SPN for the SQL Server Service account: -> We need to have proper permission to make changes in Active Directory user attributes. Domain Admin permission holder can modify the ServicePrincipalName (SPNs) but not the regular users.
From Domain Admin account -> Launch CMD -> Register the SPNs

Setspn -S MSSQLSvc/YPSQL1.yp.lab YPSQLSVc
Setspn -S MSSQLSvc/YPSQL1.yp.lab:1433 YPSQLSVc

After the successful SPN registration, restart the SQL Server Service on YPSQL1.
Then try to connect to YPSQL1 SQL instance from client machine YPSQL2.

If we are still facing same issue even after correcting the SPN’s issue, please follow the next steps.

 
Reason 2: EncryptionTypes mismatch issue

From the client machine YPSQL2, check the SYSTEM event log and find if event ID 4 logged or not.

Log Name:      System
Source:        Microsoft-Windows-Security-Kerberos
Date:          10/4/2024 3:02:55 AM
Event ID:      4
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      YPSQL2.yp.lab
Description:
The Kerberos client received a KRB_AP_ERR_MODIFIED error from the server SQLSvc. The target name used was MSSQLSvc/ypsql1.yp.lab:1433. This indicates that the target server failed to decrypt the ticket provided by the client. This can occur when the target server principal name (SPN) is registered on an account other than the account the target service is using. Ensure that the target SPN is only registered on the account used by the server. This error can also happen if the target service account password is different than what is configured on the Kerberos Key Distribution Center for that target service. Ensure that the service on the server and the KDC are both configured to use the same password. If the server name is not fully qualified, and the target domain (YP.LAB) is different from the client domain (YP.LAB), check if there are identically named server accounts in these two domains, or use the fully-qualified name to identify the server.

As per the above error message observed on client machine, it’s clear that there is a failure in decrypting Kerb Tickets.
Please consult your Active Directory Support team and take help to validate the settings to resolve the issue.

For SQL Server Instance hosted machine: YPSQL1

Lauch Active Directory Users and Computer MMC -> Client View -> Enable “Advanced Features”

Locate the SQL Server Computer object YPSQL1
Right click-> Properties

Click Attribute Editor -> select Attributes: msDS-SupportedEncryptionTypes -> click Edit

As per the above value 24 is for AES128 and AES256 EncryptionTypes. This means YPSQL1 allows and communicate with only AES128/AES256 type encryption methods.

For SQL Server Service Account named “YPSQLSVc”

Now validate the above same steps for SQL Server Service account named “YPSQLSvc”
Right click-> Properties

Click Attribute Editor -> select Attributes: msDS-SupportedEncryptionTypes -> click Edit

As per the above value <not set> means everything is allowed (all the encryption types).
So clearly there is a mismatch with “msDS-SupportedEncryptionTypes” attribute for SQL Server Instance hosted computer and SQL Server Service account. SQL Server Service Account may be trying to communicate via RC4 encryption mechanism with SQL Server Computer, however SQL Server Computer is set to accept only AES 128/ SES 256 encryption mechanism.

SQL Server Computer: msDS-SupportedEncryptionTypes -> 24 (AES128/AES256)
SQL Server Service Account: msDS-SupportedEncryptionTypes -> Not Set

Solution to resolve the issue is to correct the attribute msDS-SupportedEncryptionTypes mismatch issue for both SQL Server Server computer account (YPSQL1)) and SQL Server Service account (SQLSvc)
Made changes to SQL Server Service account “SQLSvc” to use AES 128/ AES 256 Encryption types.

Now both SQL Server Computer object and SQL Server Service account object matches the Supported Encryption Types.

SQL Server Computer (YPSQL1): msDS-SupportedEncryptionTypes -> 24 (AES128/AES256)
SQL Server Service Account (SQLSvc): msDS-SupportedEncryptionTypes -> 24 (AES128/AES256)

It may take some time for the above changes to replicate based on your Active Directory replication process. You could try to do GPUPDATE /FORCE or wait for the replication to complete.
Then try to connect SQL Server instance YPSQL1 from the remote client YPSQL2. It should connect successfully now.

 
 
Reference articles: Decrypting the Selection of Supported Kerberos Encryption Types – Microsoft Community Hub
 
 
 
 
   Read More

​