[sql] Cannot create SSPI context

I am working on a .NET application where I am trying to build the database scripts. While building the project, I am getting an error "Cannot create SSPI context.". This error is shown in the output window (inside VS2008 screen) and the building process failed. Please help on this. SQL Server is configured to work on Windows authentication & running as network service (these two things are must for my project).

Please help on this. This error is not seems to be consistent. It was fixed in the past by restarting the machine, changing the system time to match the domain time and some suggestions in the net. Please help on this.

I had this error- it happened because my password expired and I had to change it. I didn't notice it, because in some programs I could still log in and everything would work normally (including windows), but I couldn't log to any sql servers.

Here is my case. I had a remote machine that hosted SQL Server. From my local machine, I was trying to access the SQL instance via some C# code and I was getting this error. My password for the user account on my machine/domain had expired. I fixed it with the following:

1. Opened the remote machine, which prompted me for a password change
2. I changed my password within this prompt and logged into the remote machine
3. I "locked" my local machine (using windows + L key so I didn't have to completely sign off) so that I could get back to the sign-on page
4. I signed back onto my local machine with the new password

Everything then worked fine.

Perhaps you have used Integrated Security = SSPI in connection string. SSPI is used for Trusted connections using Windows Authentication.hence, to work properly in windows authentication, either your system and database server should be in same domain and using same DNS server address, or should be in trusted domain.

if your system and database server is in same domain, Check DNS server address of IPV4 properties in your system's network connection and provide same DNS server being used by database server.

We had this issue on instances in which we changed the service user from Domain1\ServiceUser to Domain2\ServiceUser. The SPNs remained registered under Domain1\ServiceUser, and never registered under Domain2\ServiceUser. We registered the SPNs under Domain2\ServiceUser, but the issue persisted. We then removed the SPNs under Domain1\ServiceUser, and the issue was resolved.

This error usually comes when the Windows user account is expired and he is already logged in with old password. Just ask the user to restart his machine and check if the password is expired or he has changed the password. Hope this helps!!!!!

I can able to get this resolved by resetting the domain (server machine, which is the domain server, but not related to SQL Server except domain managing) followed by the client machines.

Thank you all for your immediate support!

First thing you should do is go into the logs (Management\SQL Server Logs) and see if SQL Server successfully registered the Service Principal Name (SPN). If you see some sort of error (The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service) then you know where to start.

We saw this happen when we changed the account SQL Server was running under. Resetting it to Local System Account solved the problem. Microsoft also has a guide on manually configuring the SPN.

I resolved my Cannot Generate SSPI Context error by using the SQL Server Configuration Manager. Since I have SQL Server native client 10.0 on my machine, the connection to the server is trying to use named pipes (or shared memory?). Other machines could run my app with no problem. When I looked at the configuration manager, named pipes and shared memory were both enabled (good). However, under alias, the name of the computer was there with TCP forced. Since I didn't know what effect changing this would have, I changed the connection string in my program to use <servername>.<domainname> instead. Fixed.

If you are hosting on IIS, make sure the password for the AppPool account has not changed.

If it has, then follow these steps:

• Go to IIS
• Click on Application Pools
• Select the AppPool of your application
• Right Click on your AppPool
• Advanced settings
• Identity
• Update Password
• Restart AppPool

I also issued this problem, and the server admins solved it by following the same solution as indu_teja proposed in http://www.sqlservercentral.com/Forums/Topic546566-146-1.aspx

The solution proposed by indu_teja says :

If you get this "SSPI Context Error". The issues we face are:

1. We will not be able to connect to SQL Server remotely.
2. However we will be able to connect to server with local account.

CAUSE: The issue might be becasue of no proper sync happenign fro the SPNs in Active directory.

RESOLUTION:

1. You need to reset SPN. Use the synytax "SET SPN". You can check the syntax in net once.
2. Change your sql server service account from domain account to Local account, recycle sql, and then reset again with your domain account and recycle sql server.

In my case it was a missing SPN, had to run these two commands:

setspn -a MSSQLSvc:SERVERNAME SERVERNAME setspn -a MSSQLSvc:SERVERNAME:1433 SERVERNAME

In other words in my case I had the FQDN in there already correctly but not just the NETBIOS name, after adding these it worked fine. Well initially it didn't but after waiting 2 minutes it did.

I had the same issue after changing the user which was running the MSSQLSERVER-Service

To solve incorrect SPNs with SQL Server I used this tool

http://www.microsoft.com/en-us/download/details.aspx?id=39046 - Microsoft® Kerberos Configuration Manager for SQL Server

In my case it worked pretty well.

The "Cannot Generate SSPI Context" error is very generic and can happen for a multitude of reasons. Is just a cover error for any underlying Kerberos/NTLM error. Gbn's KB article link is a very good starting point and usualy solves the issues. If you still have problems I recommend following the troubleshooting steps in Troubleshooting Kerberos Errors.

It sounds like your PC hasn't contacted an authenticating domain controller for a little while. (I used to have this happen on my laptop a few times.)

It can also happen if your password expires.

In vb.net, if you are using a linked server than check your connection string. Integrated Security=true; doesn't work in all SQL providers, it throws an exception when used with the OleDb provider. So basically Integrated Security=SSPI; is preferred since works with both SQLClient & OleDB provide. If you still hit with error, remove the syntax completely.

In case you are running a code not written in your computer, that runs in a computer used by your work peer, but not in yours, check the web.config. Maybe there is your colleague's name as userPrincipalName at some place that should be in blank. That happens automatically when we create a service reference to the project in VS.

Had a really weird instance of this; All the web products that had connection strings containing the windows computer name of the SQL server worked fine, but the products that had a FQDN with the internal domain attached gave an SSPI error. i.e. COMPUTERNAME vs COMPUTERNAME.DOMAIN (ping always worked as expected)

This ONLY gave problems when a new SQL server was being used and hosts files pointed both the computer name and the computername as a FQDN for the connection strings.

Solution in this case was to set all the connection strings to the computer name only, removing the domain references.

SQL : 2008R2 SQL2012

IIS : 2008R2

I just had the same problem and all I did was delete the user log in credentials in sql server using another user id and adding them back.