The target principal name is incorrect and cannot generate sspi context - How to resolve this issue?

27.3K    Asked by AlanTaylor in SQL Server , Asked on Sep 30, 2022

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?

Answered by Amit raj

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:port
Assuming 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 DOMAINAccount


Your Answer

Interviews

Parent Categories