SQL server 2005 Connection Error: Cannot generate SSPI context

Question

Provide Used: Microsoft OLE DB Provider for SQL Server. Can anyone help me with this.. I was trying to connect with LLBLgen

Answer 1

I used to get this error sometimes when connecting to my local SQL Server with Windows Authentication. I never fixed it unfortunately - it went away when I reinstalled windows.

I think a reboot used to fix it - have you tried that? Not exactly the best solution, I know :P

Answer 2

The error you get is almost always caused by a problem with using Windows Authentication. Please try switching to a SQL server login (username/password), or make sure your current Windows login has access to the SQL server and database you're trying to connect to.

-Edoode

Answer 3

Try to synchronize your date and time with the your domain's. The SSPI issue may be related to Active Directory authentication problems, some of them related to date and time changes. This is very simple to check and fix. Try it out!

Answer 4

There is a Microsoft KB article that addresses many of the reasons for this area (KB811889) at the following URL: http://support.microsoft.com/kb/811889.

A lot of Googling shows that one of the diagnostic steps helped most people who encountered the issue.

I recently had this exact issue where I'd get this error only when authenticating with certain accounts, but not others. Ultimately what was causing my problem was not mentioned in any KB or article I found on the net, but through trial and error I discovered that when the account used through SSPI authentication to SQL Server (2k8) happened to be in a large number of groups (in my case over 250) you would get the "Cannot Generate SSPI context" error. I suspect it has something to do with overflowing the security token that Kerberos uses and have seen similar strange authentication problems for user accounts in a large number of groups.

Answer 5

I get the problem when I have the time set differently on my client machine than either the server or the AD machine ( I was trying to test into the future).

Answer 6

Short Answer: Have you recently change the user the service is running as? Was there a system crash?

Long Answer: I know this is old, but I want to post my experience that I just had. We had spent hours Googling and found nothing that worked. Eventually we ran across a set of actions that could cause this:

If you change the user that the Sql Server runs as (e.g. from Local System to a domain usr) and do certain updates and the server doesn't safely reboot -- you get this.

So, we set things back to Local System and bam it worked. Swapped it to the domain user, no worky worky. Ok. Swapped it to Local System, rebooted, swapped it to domain user, rebooted, bam -- worky worky. All was good in our world. Later that morning it crapped out again... still working on that now but the priority is changing and I'm not sure we're going to continue work on this problem so I wanted to post something in case this happens to someone else.

What caused ours was we did an update and, apparently, we learned that it's bad practice to let Sql Server run as Local System so we changed it to a domain user. We never rebooted, but restart the service. A month later, we do updates. We don't reboot. A month goes by and a power strip fries causing the server to have an unexpected shutdown. Yet another month later we find out problem because we rarely connect to this particular database (Interestingly, Sql Server 2008 worked fine... it was only 2005). Or... at least this is the best we've come across.

Our admin guy doesn't like Vista and likes to blame everything on Vista (refuses to let us test Windows 7)... so he Googled "sspi vista" or something like (I know it had sspi and vista, but it might have had another one... in case you need to Google it was well) that and ran across an article that pretty explained our scenario after we had a meeting we all remember these pieces and placed this picture together.

Answer 7

rebooted my pc and worked fine..

Answer 8

This MSDN blog page has some useful on this...

http://blogs.msdn.com/sql%5Fprotocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

Answer 9

I fixed this by mapping a drive to the server running MSSQL. This seemed to generate some kind of trust that allows MSSQL to connect without this error even after a reboot.

Answer 10

In my case, the time synchronization issue in the Windows 2003 domain environment was actually the issue.

This was quite easy to overlook as the two had been on two different time zones, whilst showing the same times on their clocks; which in effect was about 1 hour apart.

So other than the time on their watches, check the time zones as well.