“Cannot Generate SSPI Context” error message, more comments for SQL Server


The Cannot generate SSPI context issue is described by http://support.microsoft.com/?id=811889 in general. In this article we 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 “They are not able to connect to their local SQL Server, but once they connect to my network, they can’t connection to my local SQL Server”.

Such issue is reported against MSDE and SQLExpress versions. But actually, it can happen with any version/edition of SQL Server, including SQL Server 2000 and SQL Server 2005 that support NT integrated authentication.

The error message for the failed connection 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”;

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 quite often if the hosting machine is a laptop computer.

To avoid condition (1) by connecting to your corporate domain through VPN or disconnecting from network completely.

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 “.\”, “(local)\”, “\” are among them. We 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.

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.

If You really want the TCP/IP connection, the option is to use TCP/IP loop-back address, i.e. “127.0.0.1”, as your . You can also add an entry into the /etc/host file as well.

For example, if your connection string has form of “\” and is not prefixed with “tcp”, without modifying the connection string, you can configure an alias with alias name as \, protocol as TCP/IP, server as “127.0.0.1\” or “127.0.0.1,”.

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.

Be aware that only TCP/IP provider can provides the benefits of Kerberos authentication as discussed in http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx

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.

Make sure service account has enough permission in AD to register the SPN. You can check the below parameters for SPN as well.

SET SPN-L Service Account. You can get more information for the SPN from this post.
https://sqldbpool.com/2009/11/26/service-principle-name/

Check that SPN is registered, if registered there shouldn’t be duplicate entry, should be match with the service account, Service account should have enough permission, PORT number is correct.

For the connection failure…
You can also check Resolving Connection failure article.

Advertisements

13 thoughts on ““Cannot Generate SSPI Context” error message, more comments for SQL Server

  1. wan optimizers compared

    John sums up telepresence from a network perspective, “Telepresence is an interactive real- time application, which means it is delay sensitive, loss sensitive and jitter sensitive. This sounds familiar: it is just like VoIP, with the one difference being that it has huge bandwidth requirements.” It’s that last part that makes things more difficult. No form of QoS can allocate bandwidth that doesn’t exist and it doesn’t have provisions to force the application to downscale the experience based on realtime metrics. …

    Reply
  2. Babu

    our client has reported this issue with our IE based system. our system connects to the local sql server express. i’ve been trying to reproduce this error but could not. but i could reproduce the error if i disable the share memory protocol, leaving Named Pipes and TCP/IP enabled. has someone got an explanation on this, please?

    Reply
  3. Raghav

    Jugal..

    Please help me to resolve my issue

    Im getting the below error while connecting from HYDHCUSQL04 server to HYDHCUSQL05 using SSMS ( Both are in same domain)

    ===================================

    Cannot connect to HYDHCUSQL05.

    ===================================

    Cannot generate SSPI context. (.Net SqlClient Data Provider)

    ——————————
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

    ——————————
    Server Name: HYDHCUSQL05
    Error Number: 0
    Severity: 11
    State: 0
    Procedure: GenClientContext

    ——————————
    Program Location:

    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
    at System.Data.SqlClient.TdsParser.SSPIError(String error, String procedure)
    at System.Data.SqlClient.TdsParser.SNISSPIData(Byte[] receivedBuff, UInt32 receivedLength, Byte[] sendBuff, UInt32& sendLength)
    at System.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec)
    at System.Data.SqlClient.SqlInternalConnectionTds.Login(Int64 timerExpire, String newPassword)
    at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
    at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
    at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
    at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
    at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
    at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
    at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
    at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
    at System.Data.SqlClient.SqlConnection.Open()
    at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
    at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

    Reply
    1. Jugal Shah Post author

      Check whether your password is changed or there is some network issue. Try to restart the management studio or your machine. It will resolve the issue most of the time.

      Reply
    2. Clarence Liu

      I had the same issue, turns out I merely had to change my password, it appears the password expiry wasn’t in sync for some reason.

      Reply
  4. best tennis shoes for kids

    Great goods from you, man. I’ve understand your stuff previous to and you’re just extremely magnificent. I actually like what you have acquired here, certainly like what you’re stating and the way in which you say it. You make it entertaining and you still take care of to keep it sensible. I can not wait to read much more from you. This is really a tremendous web site.

    Reply
  5. col

    If you need to run the SQL Server service under a domain account but are getting this error you can:
    (1) Temp add your SQL Server service account to Domain Admins
    (2) Restart the SQL Server services on your server
    This will allow the SPN to be created.
    (3) Remove the SQL Server service from Domain Admins.
    You should now be able to connect even when SQL Server is restarted as the SPN is only created once.

    Reply

Thanks for the comment, will get back to you soon... Jugal Shah

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