Cannot Generate SSPI Context


#1

This was originally written back in 2006, in a word document and later buried/archived in an email. Reproducing here for because web :slight_smile:

NOTE: StackOverflow is of course always a good place to look! e.g.

Cannot Generate SSPI Context

Occasionally programs will display this error on opening. The following is an attempt to pinpoint possible reasons and solutions. On the following pages I have suppliers some excerpts from web pages detailing this issue, see the footnotes for links to the original sites.

  • It is possible this error can be resolved by making sure all service packs are applied to workstations, servers and SQL server.

  • The root of this problem however relates to window authentication, domain configuration and SQL server configuration (especially when running within a domain).

  • Turning off TCP/IP (and making sure named pipes is used instead) in the SQL Server network utility can help. Named Pipes will not use Kerebos authentication which is responsible for much of the SSPI error.

  • Sometimes the account under which the SQL Server is configured to run can be the cause of the problem.

  • Security Support Provider Interface (SSPI) is a set of Windows APIs that permits delegation and mutual authentication over any generic data transport layer, such as TCP/IP sockets. Therefore, SSPI permits a computer that is running a Windows operating system to securely delegate a user security token from one computer to another over any transport layer that can transmit raw bytes of data.
    The “Cannot generate SSPI context” error is generated when SSPI uses Kerberos to delegate over TCP/IP and Kerberos cannot complete the necessary operations to successfully delegate the user security token to the destination computer that is running SQL Server.
    – Reference: How to troubleshoot the “Cannot generate SSPI context” error message


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>”, “<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 . For example, if your connection string has form of “<instancename>” and is not prefixed with “tcp”, without modifying the connection string, you can configure an alias with alias name as <instancenane>, protocol as TCP/IP, server as “127.0.0.1<instancename>” 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. If the connection string is prefixed with “tcp”, then you do need to modify your connection string to specify “127.0.0.1” as .

– Reference: “Cannot generate SSPI context” error message, when connect to local SQL Server outside domain