Cannot generate SSPI Context error from SQL Server Management Studio

This is an error that I’ve seen on various occasions when connecting to SQL Server using integrated security.

I recently came across this problem when attempting to connect to SQL Server 2005 from Management Studio. Connections to the server had been working fine until applying several Windows updates on the server so one of those must have been the cause of the problem.

The following knowledgebase article provides a full explanation to the cause of this error, in particular the ‘Simplified Explanation’ is very helpful.

http://support.microsoft.com/kb/811889

In my case, here are the steps that I took to resolve the issue

1. Modify the SQL Server service to run under the Domain Administrator account.
2. Start SQL Server.
3. Stop SQL Server.
4. Restore the SQL Server service to run under the account that it was set up with initially (in my case the Local System Account.)

Since the service initially ran under the LocalSystem account, the explanation given in the KB article doesn’t completely make sense as this account should have the necessary permissions to register the SPN. Presumably, there was something else that required elevated domain permissions and briefly starting SQL Server under the domain administrator account provided a resolution.

A similar but unrelated problem that I’ve seen is the error “The trust relationship between this workstation and primary domain failed” when logging a Windows 7 machine onto a domain. The easiest fix for this is to remove and rejoin the machine to the domain.

http://social.answers.microsoft.com/Forums/en-US/w7security/thread/0e96dbf0-1252-431a-b638-440d93ef7317 

Technorati Tags: ,

Advertisements

About dotnettim

Tim Leung is a Microsoft .Net / SQL Server developer based in England.
This entry was posted in SQL Server 2008. Bookmark the permalink.

One Response to Cannot generate SSPI Context error from SQL Server Management Studio

  1. Mike Surtees says:

    Another cause is that your account may have been disabled whilst you were logged in

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s