15

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.

Improve this question
0

12 Answers 12

Reset to default
23

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

Improve this answer
0
4

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

Improve this answer
1
  • Any idea why this wrong SPN is added?
    – MichaelD
    Aug 12, 2021 at 10:43
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'.

Improve this answer
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.

Improve this answer
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.

Improve this answer
1
  • 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, 2019 at 15:29
1

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.

Improve this answer
1
  • This is what I'm currently facing. the steps to fix would be helpful.
    – BJury
    Feb 14, 2022 at 12:52
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.

Improve this answer
0
0

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

runas /user:OtherDomain\User SSMS.exe

Improve this answer
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.

Improve this answer
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.

Improve this answer
-1

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.

Improve this answer
2
  • 17
    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, 2018 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, 2020 at 10:41
-3

It mostly happens when you are working on an office laptop and you are changing password at home. It worked for me this way.

Connect to the VPN on you laptop and make sure everything else is closed. Lock your screen and wait for 10-20 mins so that your new password would be synchronized. Unlock with the new password and you sql server should connect without an issue.

Improve this answer

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