SQLServer: "Cannot generate SSPI context" error message

Started getting the error above this morning. The SQL server involved supports our Alteris installation. None of the users could get on either.

The main Microsoft reference is:
Microsoft doc

This covers a lot of ground, but something that seems/seemed to be significant is the (lack of) a SPN. SPN is a “service principle name”. My understanding is that it is a record in Active Directory which shows that resource X is up and running on server Y. It seems that this needs to be ther for the client to talk to the server.

The SPNs can be displayed by running setspn on the server. Setspn is in the Windows resource kit. I think there was a link from the doc above

> setspn -L bigserver

Registered ServicePrincipalNames for CN=BIGSERVER,OU=LONDON,OU=Servers,OU=SBE and SCE (UK),DC=eu,DC=mycompany,DC=com:
MSSQLSvc/bigserver.eu.mycompany.com:1433
HOST/bigserver
HOST/bigserver.eu.mycompany.com

When I ran the above for the server with the connectivity problem there was no entry for MSSQLSvc. I tried to add one as follows:

> setspn -A MSSQLSvc/otherserver.eu.mycompany.com:1433 otherserver

But this failed with:

Failed to assign SPN to account…

Instead out AD administrator used ADSI Edit (an AD GUI) to manually add the entry. This seems to have fixed it at a sqlserver level. Alteris users still can’t get in but I suspect this is because the Alteris application servers need to be bounced. This is happening as I type….

Why did the error suddenly occur ? The server was bounced for the first time in several weeks last night and the following hotfixes were applied:

KB899923
KB893066
KB893086
KB890859
KB893803
KB842773

Dunno which of the above if any made any difference

Advertisements