“Cannot generate SSPI context” error message, when connect to local SQL Server outside domain
The “Cannot generate SSPI context” issue is described by https://support.microsoft.com/?id=811889 in general. In this post I will discuss one daunting case of “Cannot generate SSPI context” error message when failing to connect to SQL server. In most related cases, customers report this issue as "I can connect to my local SQL Server, but once I connect to my network, I can't connection to my local SQL Server". Such issue is reported against MSDE and SQLExpress. But actually, it can happen with any SKU of SQL Server, including SQL Server 2000 and SQL Server 2005, that support NT integrated authentication. The error message for the failed connection that we discussed here is
[SNAC] “[SQL Native Client]SQL Network Interfaces: The Local Security Authority cannot be contacted.[ SQL Native Client]Cannot generate SSPI context”
[MDAC] “Cannot generate SSPI context”;
[.Net1.0/2.0]” Failed System.Data.SqlClient.SqlException: Cannot generate SSPI context”
It can happen when all of followings are true:
(1) The hosting machine of SQL Server is connected to a network, including home network or dialup connection, but it is disconnected from its domain.
(2) The OS of the hosting machine is Windows XP or 2000. Not windows 2003.
(3) The connection is to a local SQL Server.
(4) Connection configuration causes network library to choose TCP/IP provider.
A scenario that meets all of (1) (2) and (3) looks like an extreme corner case. But the reality is that it is quit often if the hosting machine is a laptop computer. One solution, of course, is to avoid condition (1) by connecting to your corporate domain through VPN or disconnecting from network completely. The reason why they work is subtle and I’ll discuss it later. From user’s perspective, however, in many cases, either connecting over VPN or disconnecting from network might prevent you from accessing some valuable resources, so I want to discuss solutions that do not depend on (1) first.
In most cases, users do not explicitly require TCP/IP as the connection provider. For example connection strings in form of “.<instance>”, “(local)<instance>”, “<servername><instancename>” are among them. Users might wonder why network library chooses TCP/IP provider instead of Shared Memory provider, if the connection string is not prefixed with “tcp” and the server is local. A simple answer is that it can happen if the TCP/IP provider is in front of other providers in the client protocol order list, or/and the local server is not listening on Share Memory and Name Pipe. As described above, only TCP/IP provider has the issue; hence, configuring network library not to choose TCP/IP is a solution. To do that, first, on the server side, make sure your server is listening on Shared Memory or/and Named Pipe connection requests; then, on the client side, change the protocol order list such that Shared Memory and/or Named Pipe are in front of TCP/IP, or prefixing your connection strings with “lpc” or “np” to force Shared Memory or Named Pipe, or using alias that prefix Named Pipe in connection strings, whichever you feel most comfortable with. Note that certain SKUs of SQL Server have named pipe connection turned off by default.
In very rare case, however, if you really in need of TCP/IP connection, the option is to use TCP/IP loop-back address, i.e. “127.0.0.1”, as your <servername>. For example, if your connection string has form of “<servername><instancename>” and is not prefixed with “tcp”, without modifying the connection string, you can configure an alias with alias name as <servername><instancenane>, protocol as TCP/IP, server as “127.0.0.1<instancename>” or “127.0.0.1,<port>”. Remember that the “Cannot Generate SSPI context” problem described in this post only happens when connecting to a local server; thus, the “127.0.0.1” is applicable. If the connection string is prefixed with “tcp”, then you do need to modify your connection string to specify “127.0.0.1” as <servername>.
If these workarounds described above do not fit your needs, we would like to hear more from you.
The reason that we didn’t fix this subtle issue is because the limitation is rooted in a behavior of an integrated authentication module (SPNEGO) in XP and windows 2000, i.e. whether to fallback to NTLM if KDC is not available when the target SPN points to local machine. KDC, normally, is part of your domain controller. For this specific case, SPNEGO chooses not to fallback, hence connection fail. This issue is not a security issue though. Reader might ponder why avoiding using TCP/IP provider can solve the problem while explaining it is because certain behavior of SPNEGO in Windows. Not going too deep, the simple answer is that only TCP/IP provider, with an exception of loop-back connection, uses SPNEGO while other providers use NTLM. Be aware that only TCP/IP provider can provides the benefits of Kerberos authentication as discussed in https://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx
Back to the questions we left before, the reason that disconnected from network (no network media) works is because, in such case, local <servername> is resolved to “127.0.0.1” by windows network layer and NTLM is used directly. When connected over VPN, the SPNEGO issue goes away because the KDC is accessible in this case.
From the error message reported by SNAC ODBC/OLEDB, you can differentiated the issue described by this post from another case of “Cannot generate SSPI context”, in which the root cause is because, in Active Directory, the Service Principle Name (SPN) of SQL Server is registered for a domain account different from the SQL Server is actually running under. The error message for the other case is “[SQL Native Client]SQL Network Interfaces: The target principal name is incorrect.[SQL Native Client]Cannot generate SSPI context. The “Cannot generate SSPI context” issue is described by https://support.microsoft.com/?id=811889 in general and by https://blogs.msdn.com/sql_protocols/archive/2005/10/15/481297.aspx specifically for the other case.
Do you know that you can post question w.r.t SQL Server data access, connectivty issues at https://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1 ?
Nan Tu, Software Design Engineer, SQL Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights
Comments
Anonymous
November 04, 2005
Just wanted to say thank you for this info...
It just helped me solve my SSPI problems.
You rock.Anonymous
January 03, 2006
Interesting issue, and definitely not something I've run into before. But, now that I have I'm glad I've found this article. Thanks for posting this.Anonymous
January 10, 2006
Blessings, You saved me another 3 hours after the 5 I already spent on the issue...Anonymous
January 26, 2006
Thank you very much for this article. I've just spent an hour trying all sorts of fixes, only to find changing my server setting to 127.0.0.1 from 'localhost' solves the issue.
I agree the situation of a machine joined to a domain but separated from it and running SQL locally is quite common.Anonymous
February 03, 2006
The comment has been removedAnonymous
February 03, 2006
The error message:
Before Connect String in Initialize= Provider=SQLOLEDB.1;Trusted_Connection=Yes;Data Source=33BF451THOMAS;Initial Catalog=NAPAScope
Msg occurred at 2:03:20 PM
State = 0
Msg occurred at 2:03:20 PM
-2147467259 Microsoft OLE DB Provider for SQL Server - ScopeDataEngine = Cannot generate SSPI context
Msg occurred at 2:03:20 PM
Informatin: All of this on a laptop.
We installed sql express with remote connections enabled and tcp/ip enabled.
When we login to the network via vpn, we are able to login ok.
When we reboot, and login to the local system, we get the error you see above in the email …. “Cannot Gen SSPI context”
We then set the Configuration to Local ONLY using the SAC and the ole DB connection connects perfectly to the database.
The connection string has a data source of (Local) when it fails as well as the one above.
One solution we can live with is an automated way to set the SAC to Local Only using commands with some batch file.
Another solution is to find a fix for the OLE DB Connection either the string, or the actually driver that connects to SQL Express.
All of that said, we tried connecting using a program with .NET SqlClient. Using the same connection string, we were able to connect with SqlClient .NET provider in EVERY case. However since our main software uses OLE DB, we are stuck with the possible suggested solutions.
Anonymous
February 03, 2006
One of the following should fix your problem:
(1) Use the new SQL Native Client provider instead of SQLOLEDB by specifying "Provider=SQLNCLI". This assumes that SQL Native Client is installed on the machine, which is the case if SQL Express is installed.
OR
(2) Specify the use of the Named Pipes protocol in the connection string by adding ";Network Library=dbnmpntw" to the connection string. That will avoid the use of Kerberos authentication protocol.Anonymous
February 17, 2006
The comment has been removedAnonymous
February 20, 2006
Manoj,
Is it a local or remote connection? Is your client machine in domain or out of domain? If it is local connection, does connection using tcp:127.0.0.1 work for you when the server TCP protocol is turned on?Anonymous
March 18, 2006
Thanks for info. I use local server for devel purpose in my laptop and was unable to connect while using home network. After disconnecting home network, it worked...Then re-enabled local connection.
Thank You.Anonymous
April 27, 2006
Yes, it was enough to disable TCP/IP, in my case and it works.Anonymous
May 08, 2006
I also get this error on client machines, (W2003) using a standalone SQL Server 2000 running W2003AS. “System.Data.SqlClient.SqlException: Cannot generate SSPI context”
The weird thing is that this only happens occasionally, about 20 times per day.
Windows 2003 Advanced Server SP1
SQL Server 2000 SP4
Not much load on the system
Application layer: 8 web servers Net 1.1. and two Biztalk 2002 servers
We use TCP/IP as a connection provider
The clock on the servers are in sync
Any ideas?Anonymous
May 08, 2006
Henrik,
In your case, we need to know what is the connection string, "what is the machine account that your server is running under", "do you change account often", "Are client and server unning under different domain" and etc. Please post your question with more specific info such as connection string on
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1,
There is a general guideline on the forum w.r.t to how to post a question.Anonymous
May 08, 2006
Thanks for your reply.
[1] Client side:
1. What is the connection string in you app or DSN? (please specify)
"server=mydbserver; database=mydatabase; uid=; pwd=; trusted_connection=yes; Max Pool Size=10; Connection Timeout=60; Packet Size=4096; ;"
2. If client fails to connect, what is the client error messages? (please specify)
Category: 300
Computer Name: KATTEFOT
Event Code: 0
Record Number: 54
Source Name: AltInn.eGA
Event Type: Error
User:
Time Written: 20060508160522.000000+120
<ACALog><LogCategory>UnknownException</LogCategory><Header>Unknown Exception</Header><EventID>0</EventID><Body><ExceptionType>SqlException</ExceptionType>
<UserId>08057833557</UserId>
System.Data.SqlClient.SqlException: Cannot generate SSPI context.
3. Is the client remote or local to the SQL server machine?
It is remote.
4. Can you ping your server?
Yes.
5. Can you telnet to your SQL Server?
Yes
6. What is your client database provider?
Client app is .Net SqlClient Data Provider. It uses MDAC 2.82.1830.0 on both client and server machine.
7. Is your client computer in the same domain as the Server computer?
Same domain
8. What protocol the client enabled? [Shared Memory | TCPIP | Named Pipes].
Can you configure this on the client? We're using the "SQL Server .NET Data Provider" and I belive this protocol uses the
the default protocol of the server, which is 1) TCP/IP and 2) Named pipes.
9. Do you have aliases configured that match the server name portion of your connection string?
The clients are always using the IP adress of the db-server
[2] Server side:
1. What is the MS SQL version?
SQL Server 2005
2. What is the SKU of MS SQL?
Enterprise
3. What is the SQL Server Protocol enabled? [
TCPIP and Named Pipes
4. Does the server start successfully?
Yes
6. What is the account that the SQL Server is running under?
Domain Account
7. Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider?
YES
8. Do you make firewall exception for SQL Browser UDP port 1434?
YES
[3] Platform:
1. What is the OS version?
Windows 2003 Enterprise edition
2. Do you have third party antivirus, anti-spareware software installed?
No.
Anonymous
June 02, 2006
I could login to SQL Express this morning, from a disconnected laptop. Later, with a web connection, but no domain connection, I got the SSPI error
logging on as 127.0.0.1sqlexpress solved the problem
How do I upsize from Access to SQL Express?
Thanks.Anonymous
July 31, 2006
Yes, Connecting as localhostsqlexpress solved the problem. Thanks a ton.Anonymous
August 16, 2006
Hi,
I am having the same problem, and the issue here is that the user is using the Win XP and the SQL serve is Hosted on Win 2000. The user is in a seperate doamin and the server is as well. The rights have been given to the user within the SQL server for access. When the user tries to access one server he is able to without any problems, but when he tries to connect to other server in same donain he gets this SSPI error.
Hopy you can help me on that.
Regards,
Himanshu NirmalAnonymous
September 26, 2006
Hi,
I am facing this problem over VPN, I am not able to connect my SQL Server hosted at Different Domain. While connecting with SQL Server Client, it is giving me same error "Cannot generate SSPI context". Please update me. What is the actual process has to be follow to get resolve this issue.Anonymous
September 27, 2006
The comment has been removedAnonymous
October 02, 2006
I checked the system log and was told the SPS server could not connect to a timing/clock computer... I ran the net time command to sync the SPS server time with our domain server and that eliminated the problem. net time <ip or computer name> /SET /YAnonymous
January 02, 2007
The comment has been removedAnonymous
January 28, 2007
This post provides some tips to troubleshoot Sql Server connection problems based on various displayedAnonymous
January 29, 2007
“Cannot generate SSPI context” error message, when connect to local SQL Server outside domainAnonymous
April 10, 2007
This happens to me, and anything you say does nothin, neither syncronizing time nor doing the other things.Anonymous
April 10, 2007
Dove, Can you describe what is your configuraiton, including machine, account, connection string, sql server and etc. and when it connect, when it dosn't. There could be ton of reasons. Without your input, we don't have clue to help you out. Nan TuAnonymous
April 22, 2007
Excellent! Using 127.0.0.1 as my server instead of my computer name solved my issue. Thanks!Anonymous
June 26, 2007
I'm having this error in a different situation: If I'm trying to run: osql -S 127.0.0.1 ... then I get the error but if I run: osql -S <name> ... then I don't The machine is a domain controler Win2K with SQL 2K on it - all latest patches applied. Any clue?Anonymous
June 26, 2007
Thank you!!! This worked (and now I understand why) on an ODBC connection for a Crystal Report.Anonymous
August 19, 2007
Changing the database name to 127.0.0.1 (using the default SQL instance) fixed it! Thanks for the excellent post; it saved my life while I was disconnected from the mother ship.Anonymous
September 04, 2007
The comment has been removedAnonymous
September 26, 2007
You champion. Thanks for this article.Anonymous
October 04, 2007
From ssms of one system iam able to register other system having ssms. But when i do the same from SQL enterprise manager of one system to ssms of another,i get "Cannot generate SSPI context" message. Both the system are in same domain. ThanksAnonymous
November 27, 2007
Very nice & Informtive Article. helped a lot to understand things. thans,Anonymous
January 01, 2008
PingBack from http://movies.247blogging.info/?p=3422Anonymous
January 09, 2008
The comment has been removedAnonymous
April 09, 2008
The comment has been removedAnonymous
April 16, 2008
The comment has been removedAnonymous
May 14, 2008
Pocket PC connect SQL Server is Error "Cannot generate SSPI context" Becuase ?. How do ?. To connect sql server on PC with Connect Active syncronize. Thank youAnonymous
May 20, 2008
PingBack from http://kimora.freemusiconlineindia.info/cannotgeneratesspicontext.htmlAnonymous
July 10, 2008
PingBack from http://damian.mediacentermovie.info/cannotconnecttosqlserversecondinstance.htmlAnonymous
July 14, 2008
Does this error occur on Vista as well? What about 2008 Server?Anonymous
August 25, 2008
I'm sure there are many complex causes for this SSPI context error. However, the simplest case isn't covered here or in the MS KB. If you change the service account password but do not change it in the SQL service credentials and leave the SQL instance running, you will get this error trying to connect with Windows authentication. Put the correct new password in the service credentials and it's fixed.Anonymous
September 01, 2008
PingBack from http://cesardiaz.es/wordpress/?p=9Anonymous
October 27, 2008
A bit of back ground - for the last so many years I am used to running local Ax installation in my laptopAnonymous
November 12, 2008
Im using BizTalk server and SQL server, i faced the same Error .. the DATE was not the same on BizTalk server and SQL server !!! one of the guys did change the date. after changing the date and make it the same on the 2 servers everything ran fine!!!Anonymous
January 14, 2009
iam using the Win XP and the SQL serve is Hosted on Win 2000...i keep getting disconnected from SQL D.B every hour and SSPI context error shows up...i log off the pc then log in again and then i can log in to the D.B again...how can i stop it plz?Anonymous
January 18, 2009
PingBack from http://www.hilpers.it/2538994-contesto-sspiAnonymous
January 18, 2009
PingBack from http://www.keyongtech.com/2862529-vpn-and-cannot-generate-sspiAnonymous
May 16, 2009
PingBack from http://tagz.in/posts/4gl/comments/Anonymous
May 21, 2009
Hi, I installed SQL Express in Windows server and tried to connect with it, getting this error...but it connects with the SQL authentication...when I tried to connect it with Windows authentication. I'm getting this error.....plz help meAnonymous
May 31, 2009
PingBack from http://woodtvstand.info/story.php?id=8365Anonymous
June 15, 2009
I'd like to script this so I can ensure Named Pipes is enabled on the SQL 2008 server and the SQL 2008 client has named pipes enabled and the order is BEFORE tcp/ip. How can I script this? Not finding it in sp_configure.. Please reply or email sql@davidcobb.netAnonymous
September 18, 2009
I disabled TCP/IP, enabled Shared Memory, and Named Pipes options from SQL Server Configuration Manager on my Microsoft SQL Server 2005, restarted the Server. I was able to get out of this error. Thanks a lot to this posting.Anonymous
November 10, 2009
Please help me out!! I have been trying to connect but getting this error msg "Cannot generate SSPI context". I need a access a BAK file on server using SQL.Anonymous
November 11, 2009
i have ms access based application linked to ms sql server. when i try to connect this application through a client machine (winXP), i am getting unablae to generate SSPI context error. but in the same machine, when another user log in he is able to connect to server without problems? is there a possibility that this problem is linked to user account?Anonymous
January 04, 2010
I just experienced this error again, on a computer that is a member of a domain, but where the computer is disconnected from the network. The error occurs not only when using the TCP/IP protocol, but also when using Shared Memory (I tried disabling all other client protocols and also all other server protocols). The error I get, when trying to use the osql.exe utility, to connect to the server, looks like this: [SQL Server Native Client 10.0]SQL Server Network Interfaces: The Local Security Authority cannot be contacted [SQL Server Native Client 10.0]Cannot generate SSPI context The computer has a loopback interface which allows it to still use TCP/IP, but doesn't allow it to connect to anything. The only workaround that has worked on this computer has been to enable the TCP/IP protocol again and connect to 127.0.0.1/InstanceName instead of using .InstanceName or ComputerNameInstanceName.Anonymous
February 17, 2010
Superb , it helped me solved my problem in BiztalkAnonymous
April 07, 2010
I there, I have just experienced this error and the problem was in the server where SQL Server instance was running because the time in Operating System was not correct and assincronous from the Active Directory server time. The error was solved fixing the time in the operating system where SQL Server was running. CheersAnonymous
July 26, 2010
Hi , im facing problem cannot generate sspi context after some time the application is open. once i logged off and login again, im able to connect for some duration, again it will raise the same error message. Please guide me on thisAnonymous
September 07, 2010
Bless you! I'm the classic laptop software professional, with a domain in the office, and no domain at home. Changing my code from my laptop's name to 127.0.0.1 was the trick.Anonymous
December 07, 2010
I received this error because the password of the "functional user account" running my webservice was expired.Anonymous
January 05, 2011
Thanks for your post Nan Tu - not only was it helpful but very interesting and informativeAnonymous
February 16, 2012
The comment has been removedAnonymous
April 12, 2012
What do you do when none of the followings are true?Anonymous
July 31, 2012
that was awesome. I ve been searching for 2 hours till I got this article , which fixed my issue in a minute. Hats off to you.Anonymous
October 09, 2013
Restart the SQL Server Browser servivceAnonymous
April 22, 2015
Good informational source covering the most of the references related to SSPI. <a href="staygreenacademy.com/">SharePoint 2013 Administrator Training</a>Anonymous
September 25, 2015
This issue might also happen if the service account is changed and password is not updated. You are able to connect to the SQL Server on that machine. Not able to connect the SQL Server from the remote or different machineAnonymous
November 19, 2015
I had this issue and nothing posted in many forums, blogs Microsoft Support and TechNet articles solved my problem. Nothing in this post worked for me. I ran into this issue on a Windows Server 2012, running both SQL Server 2012 Express and SharePoint 2013 Foundation Services. How I fixed my issue is in Services.msc I stopped the SharePoint Search Host Controller service which was running, but disabled on installation of SharePoint. The issue is gone. How the service got started is beyond me.