The target principal name is incorrect and cannot generate sspi context - How to resolve this issue?
We had a power outage last night and when the server came back up our domain users could not access our SQL Server 2000 instance name: MASSQL.
We can access it using SQL Authentication.
- We tried making sure the user that runs the service has SSPI read and Write
- We tried creating a new user
- We tried running the service using NETWORK SERVICE account
- We tried running as Local System Account
- We tried creating a new Domain Admin account and run service as the new Domain Admin account
- I also ran the following command against the problem SQL Server instance:
setspn -L xsql2
Results
Registered ServicePrincipalNames for CN=MASSQL,CN=Computers,DC=ABC,DC=com:
HOST/MYSQL
HOST/MASSQL.ABC.COM
I ran the following command against a non-problem SQL instance:
setspn -L xensql1
Results
Registered ServicePrincipalNames for CN=XENSQL1,CN=Computers,DC=ABC,DC=com:
WSMAN/XENSQL1
WSMAN/XENSQL1.ABC.com
RestrictedKrbHost/XENSQL1
HOST/XENSQL1
RestrictedKrbHost/XENSQL1.ABC.COM
HOST/XENSQL1.ABC.COM
It seems the results for the non-problem instance have more data. I do not know if this has to do with the fact one is 2000 and the other is 2012.
What likely happened? How do I allow domain users to login again?
To resolve - the target principal name is incorrect cannot generate sspi context - Use setspn -X to look for duplicate SPNs for the SQL Server in question. Remove any duplicate SPNs that don't line up the SQL Server Service account in question.
Service Principal Names for SQL Server take the form of:
MSSQLSvc/server.domain:port
MSSQLSvc/server:portAssuming your SQL Server is using the default TCP port, 1433, I would expect you need the following servers:
MSSQLSvc/MASSQL.abc.com:1433
MSSQLSvc/MASSQL:1433
You can create those SPNs using the following command:
SETSPN -A MSSQLSvc/MASSQL.abc.com:1433 DOMAINAccount
SETSPN -A MSSQLSvc/MASSQL:1433 DOMAINAccount
Where DOMAINAccount is the name of the service account used by SQL Server.
If your SQL Server uses a named instance, the SETSPN commands look like:
SETSPN -A MSSQLSvc/MASSQL.abc.com:1433 DOMAINAccount
SETSPN -A MSSQLSvc/MASSQL:1433 DOMAINAccount
SETSPN -A MSSQLSvc/MASSQL.abc.com:INSTANCENAME DOMAINAccount
SETSPN -A MSSQLSvc/MSSQL:INSTANCENAME DOMAINAccountAnswer (1)
To resolve the error "The target principal name is incorrect and cannot generate SSPI context," you can try the following steps:
Check SPN Configuration: Ensure that the Service Principal Name (SPN) is correctly configured for the service you are trying to connect to. Use the setspn utility to verify and set the SPN if necessary.
Verify Server and Client Time: Make sure that the time settings on both the server and client machines are synchronized. Time differences can sometimes cause authentication issues.
Enable Kerberos Authentication: Ensure that Kerberos authentication is enabled and properly configured on both the server and client machines.
Check DNS Configuration: Verify that the DNS settings are correct and that the server can be resolved by its hostname and fully qualified domain name (FQDN) from the client machine.
Check Service Account Permissions: Ensure that the service account running the service has the necessary permissions to access resources and generate the SSPI context.
Check Network Connectivity: Verify that there are no network issues between the client and server machines that could prevent successful authentication.
Check for SPN Duplicate: Ensure that there are no duplicate SPNs registered for the same service account, as this can cause conflicts.
By following these steps, you should be able to resolve the SSPI context error and establish a successful connection between the client and server.