9

When someone tries to connect to a SQL Server instance, the error shows up:

It's not possible do generate SSPI context.

Yesterday we had a blackout (don't know how to say this expression in English) and I had to shut down our servers.

Looking for answers, I found this:

SQL Server 2008 connectivity issue : cannot generate SSPI context

But it doesn't help me, because they're working fine until yesterday. I don't want to change anything. But if a have to, I will change it.

Obs: I can't restart the server now.


Edit: Since I my answer, we haven't had any errors.

2
  • I can't restart the server, we have more than 500+ users online. I really can't figure out what to do. There's nothing useful on the internet. – Racer SQL Feb 20 '15 at 12:04
  • 1
    Set the group policy from the server itself to automatically change the time for the users. If you dont want to restart the server to force the changes in the group policy you can use gpupdate /force. More on this beautiful subject here But I would highly recommend this task to be handed to the server manager. – Nelson Feb 20 '15 at 12:13

11 Answers 11

16

'Cannot generate SSPI context' is a generic error. It can be caused by many issues, like an outaded password, clock drift, Active Directory access permissions, failure to register an SPN and so on and so forth.

There is no solution to this problem. The only 'solution' is to investigate the cause, as per KB811889 and/or Troubleshooting Kerberos Errors. Applying one solution or another from random Internet resources, w/o understanding the cause, may or may not solve the issue, may or may not cause frustration, may or may not cause irrevocable damage.

1
  • 2
    Answer->Applying one solution or another from random Internet resources, w/o understanding the cause, may or may not solve the issue, may or may not cause frustration, may or may not cause irrevocable damage. Response->"We changed the SQL SERVICE user to one that is "Domain Admin"." -ooooo-kaay then. – matao Sep 28 '16 at 6:57
2

We changed the SQL SERVICE user to one that is "Domain Admin".

I've made some researches to know why this happens. It says that when you shutdown the service, you need an account with privileges do create a new SPN ( when it turns on again ). If you start a service without it, it will show in CANNOT GENERATE SSPI CONTEXT.

we are changing the privileges of our system account.

Hope it helps someone.

2
  • 16
    Good practice for service accounts is to give them the least privileges possible. Giving your service account domain admin is the opposite of that approach - I'd strongly recommend against doing so, from a security point of view. – Mike Mar 21 '18 at 9:55
  • By doing that, all you've done is narrowing the issue down to the previous account used. You should have used that information to fix the issue on the account used before. – Hybris95 Nov 27 '20 at 10:41
2

We had this problem after we took a database from PROD and restored it in QA. Our application called three databases on three servers and aside from that was not complicated.

It turns out a spurious SPN (Service Principal Name) was getting in the way of the service account under which the connection should have been running. We discovered this using the Program Files > Microsoft Kerberos Config Manager.

The short term fix was to use SQL Server Configuration Manager and change the SQL Server and SQL Server Agent connections from the service account to 'LocalSystem' under 'Use BuiltIn Account'.

0
1

Cannot generate SSPI context can mean exactly that. When a client connects to an SQL server it uses a generation method that includes the service type (MsSQLsvr) Server FQDN and port. It uses DNS to generate the server name so if it resolves the name incorrectly due to CNAMEs or host file etc the generation will fail. Ping the desired server and see what reply you get. If it isn't the FQDN of the SQL server then the SPN will be generated incorrectly causing this error.

1

In some situations you will get this error due to SPN settings. For example if you are doing disaster recovery testing to a new sever you may get an SSPI error while connecting to SQL Server. Even more odd is that you may be able to connect using SQL Server Management Studio but you can't connect via ODBC or OLEDB. There may be a temporary workaround that will probably allow you to get to the database.

Workaround: On each client computer trying to connect to the SQL Server, create an entry in each workstation's host file. The exact mechanism this uses to fix the issue is unconfirmed but it may be that using it overrides the need for the SPN.

While this isn't guaranteed to be fix for all cases it will most likely get you back up and running. This should not be considered a permanent fix. You should resolve the SPN or other issues in an ideal situation. However if you're having issues with old Windows machines running an unsupported operating system or if you're just doing proof of concept disaster recovery testing this should get you where you need to be to keep the lights on or production back up and running.

2
  • I just want to reiterate that this is a workaround. Workarounds are not permanent fixes. If you want to cause less problems in the future you should fix the underlying issue which, in my experience, has to do with SPNs. – Jason Geiger Apr 29 '19 at 15:29
  • An entry mapping the machine name to the machine IP? – Bill Greer Jun 20 '19 at 16:45
1

I experienced this issue and it has been resolved by removing the SPN entry in the attributes of the computer account in AD for the server

Find the machine account in AD Users and Computers(Advanced view)

enter image description here

Then remove the two entries in servicePrincipalName for MSSQLSvc

enter image description here

0

I had a similar issue. I ended up having to remove entries from the SPN information on the computer account in ADSIEdit.

After removing the entries, I restarted the SQL service and it registered the SPN with the domain resource account that I had created. I was then able to access SQL Server remotely.

0
0

For me the solution was to run SQL Studio as the domain user with this command:

runas /user:OtherDomain\User SSMS.exe

0

I experienced this issue just recently. I was running as the NT SERVICE account and when I switched to using a service account, I could no longer connect using SSMS with the machine name or the FQDN. I could connect with the IP address. With some digging I found that the SPN in Active Directory for the SQL instance was registered on the machine. Once I removed this and then added it to the service account, and rebooted the SQL machine, everything worked as it should. Happy to provide more details if needed.

0

When you are running your service account using a local account you will not have this issue, but if you run your serice accounts as an active directory user, then it will need permissions to create its own SPN. If it does not have this permission, you should manually create the SPN.

Format for SetSPN command: mssqlsvc/fqdn:sql port

I think the easier approach is to install Kerebos Configuration Manager and run the diagnostic check. It will produce scripts to create SPNS and you can then hand them over to your AD team.

0

I ran into this error when my Account was Locked-Out and I wasn't aware of it yet.
I was still logged into Windows at the time, so I couldn't figure out why at first.
Lock your station and try logging back in to see if your Windows Account is locked-out.
Once I Unlocked it, I didn't have to reboot, as the SSMS window (that I still had open)
was then able to Windows Authenticate (but you should still do a reboot).

As other answers state, it could be more than one thing that causes this error message.

Not the answer you're looking for? Browse other questions tagged or ask your own question.