8

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.

  • 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. – Nelz Feb 20 '15 at 12:13
14

'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
    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
3

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.

  • 6
    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
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'.

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.

  • 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 at 15:29
  • An entry mapping the machine name to the machine IP? – Bill Greer Jun 20 at 16:45
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

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

runas /user:OtherDomain\User SSMS.exe

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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