Microsoft KB Archive/811889

From BetaArchive Wiki

Article ID: 811889

Article Last Modified on 3/16/2007


APPLIES TO

  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft Windows Server 2003, Standard Edition (32-bit x86)
  • Microsoft Windows 2000 Standard Edition


SUMMARY

This step-by-step article describes how to troubleshoot the most typical sources of the "Cannot generate SSPI context" error message. You may receive this error message under the following conditions:

  • You are connecting to SQL Server.
  • You are using Integrated Security.
  • Kerberos is used to perform the security delegation.

Understanding Kerberos terminology and Service Principle Name

The SQL Server driver on a client computer uses integrated security to use the Windows security token of the user account to successfully connect to a computer that is running SQL Server. The Windows security token is delegated from the client to the computer that is running SQL Server. The SQL Server driver performs this delegation when the user's security token is delegated from one computer to another by using one of the following configurations:

  • NTLM over Named Pipes (not using Security Support Provider Interface [SSPI])
  • NTLM over TCP/IP sockets with SSPI
  • Kerberos over TCP/IP sockets with SSPI

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.

Why Security Support Provider Interface chooses NTLM or Kerberos

Kerberos uses an identifier named "Service Principle Name" (SPN). Consider an SPN as a domain or forest unique identifier of some instance in a network server resource. You can have an SPN for a Web service, for an SQL service, or for an SMTP service. You can also have multiple Web service instances on the same physical computer that has a unique SPN.

An SPN for SQL Server is composed of the following elements:

  • ServiceClass: This identifies the general class of service. This is always MSSQLSvc for SQL Server.
  • Host: This is the fully qualified domain name DNS of the computer that is running SQL Server.
  • Port: This is the port number that the service is listening on.

For example, a typical SPN for a computer that is running SQL Server is: 

MSSQLSvc/SQLSERVER1.northamerica.corp.mycompany.com:1433

The format of an SPN for a default instance and the format of an SPN for a named instance are not different. The port number is what ties the SPN to a particular instance.

When the SQL Server driver on a client uses integrated security to connect to SQL Server, the driver code on the client tries to resolve the fully qualified DNS of the computer that is running SQL Server by using the WinSock networking APIs. To perform this operation, the driver code calls the gethostbyname and gethostbyaddr WinSock APIs. Even if an IP address or host name is passed as the name of the computer that is running SQL Server, the SQL Server driver tries to resolve the fully qualified DNS of the computer if the computer is using integrated security.

When the SQL Server driver on the client resolves the fully qualified DNS of the computer that is running SQL Server, the corresponding DNS is used to form the SPN for this computer. Therefore, any issues pertaining to how the IP address or host name is resolved to the fully qualified DNS by WinSock may cause the SQL Server driver to create an invalid SPN for the computer that is running SQL Server.

For example, the invalid SPNs that the client-side SQL Server driver can form as resolved fully qualified DNS are:

  • MSSQLSvc/SQLSERVER1:1433
  • MSSQLSvc/123.123.123.123:1433
  • MSSQLSvc/SQLSERVER1.antartica.corp.mycompany.com:1433
  • MSSQLSvc/SQLSERVER1.dns.northamerica.corp.mycompany.com:1433

When the SQL Server driver forms an SPN that is not valid, authentication still works because the SSPI interface tries to look up the SPN in the Active Directory directory service, and it does not find the SPN. If the SSPI interface does not find the SPN, Kerberos authentication is not performed. At that point, the SSPI layer switches to an NTLM authentication mode and the logon uses NTLM authentication and typically succeeds. If the SQL Server driver forms an SPN that is valid but is not assigned to the appropriate container, it tries to use the SPN but cannot, causing a "Cannot generate SSPI context" error message. If the SQL Server startup account is a local system account, the appropriate container is the computer name. For any other account, the appropriate container is the SQL Server startup account. Because authentication will try to use the first SPN that it finds, make sure that there are no SPNs assigned to inappropriate containers. In other words, each SPN must be assigned to one and only one container.

The key factor that makes Kerberos authentication successful is the valid DNS functionality on the network. You can verify this functionality on the client and the server by using the Ping command-line utility. On the client computer, run the following command to obtain the IP address of the server that is running SQL Server (where the name of the computer that is running SQL Server is SQLServer1):

ping sqlserver1


To see if the Ping command-line utility resolves the fully qualified DNS of SQLServer1, run the following command:

ping -a IPAddress


For example: 

C:\>ping SQLSERVER1

Pinging SQLSERVER1 [123.123.123.123] with 32 bytes of data:
    
Reply from 123.123.123.123: bytes=32 time<10ms TTL=128
Reply from 123.123.123.123: bytes=32 time<10ms TTL=128
Reply from 123.123.123.123: bytes=32 time<10ms TTL=128
Reply from 123.123.123.123: bytes=32 time<10ms TTL=128
    
Ping statistics for 123.123.123.123:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum =  0ms, Average =  0ms
C:\>ping -a 123.123.123.123
    
Pinging SQLSERVER1.northamerica.corp.mycompany.com [123.123.123.123] with 32 bytes of data:
    
Reply from 123.123.123.123: bytes=32 time<10ms TTL=128
Reply from 123.123.123.123: bytes=32 time<10ms TTL=128
Reply from 123.123.123.123: bytes=32 time<10ms TTL=128
Reply from 123.123.123.123: bytes=32 time<10ms TTL=128
Ping statistics for 123.123.123.123:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum =  0ms, Average =  0ms

C:\>

When the command ping -a IPAddress resolves to the correct fully qualified DNS of the computer that is running SQL Server, the client side resolution is also successful.

SQL Server Service Principle Name creation

This is one of the critical parts of Kerberos and SQL Server interaction. With SQL Server, you can run the SQL Server service under one of the following: a LocalSystem account, a local user account, or a domain user account. When the SQL Server service instance starts, it tries to register its own SPN in Active Directory by using the DsWriteAccountSpn API call. If the call is not successful, the following warning is logged in Event Viewer: Source: MSSQLServer EventID: 19011 Description: SuperSocket info: (SpnRegister) : Error 8344.

For more information about the DsWriteAccountSpn function, visit the following Microsoft Web site:

http://msdn2.microsoft.com/en-us/library/ms676056.aspx


Simplified explanation

If you run the SQL Server service under the LocalSystem account, the SPN is automatically registered and Kerberos interacts successfully with the computer that is running SQL Server. However, if you run the SQL Server service under a domain account or under a local account, the attempt to create the SPN will fail in most cases because the domain account and the local account do not have the right to set their own SPNs. When the SPN creation is not successful, this means that no SPN is set up for the computer that is running SQL Server. If you test using a domain administrator account as the SQL Server service account, the SPN is successfully created because the domain administrator-level credentials that you must have to create an SPN are present.

Because you might not use a domain administrator account to run the SQL Server service (to prevent security risk), the computer that is running SQL Server cannot create its own SPN. Therefore, you must manually create an SPN for your computer that is running SQL Server if you want to use Kerberos when you connect to a computer that is running SQL Server. This is true if you are running SQL Server under a domain user account or under a local user account. The SPN you create must be assigned to the service account of the SQL Server service on that particular computer. The SPN cannot be assigned to the computer container unless the computer that is running SQL Server starts with local system. There must be one and only one SPN, and it must be assigned to the appropriate container. Typically, this is the current SQL Server service account. However, this is the computer account with local system.

Verify the domain

Verify that the domain that you log on to can communicate with the domain to which the computer that is running SQL Server belongs. There must also be proper name resolution in the domain.

  1. If your logon domain is the same domain that the computer that is running SQL Server belongs to, use Windows authentication to log on to SQL Server. If authentication fails, there is a Windows account or domain account problem that you must address. Contact your security administrator or network administrator to verify the Windows account or domain account for appropriate permissions.
  2. If your logon domain is different from the domain of the computer that is running SQL Server, check the trust relationship between the domains.
  3. Check whether the domain that the server belongs to and the domain account that you use to connect are in the same forest. This is required for SSPI to work.
  4. Use the Account is Trusted for Delegation option in Active Directory Users and Computers when you start SQL Server.
  5. Use the Manipulate Service Principal Names for Accounts (SetSPN.exe) utility in the Windows 2000 Resource Kit. Windows 2000 domain administrator accounts or Windows 2003 domain administrator accounts can use the utility to control the SPN that is assigned to a service and an account. In the case of SQL Server, there must be one and only one SPN. The SPN must be assigned to the appropriate container, the current SQL Server service account in most cases and the computer account when SQL Server starts with the local system account. If you start SQL Server while logged on with the LocalSystem account, the SPN is automatically set up. However, if you use a domain account to start SQL Server, or whenever you change the account that is used to start SQL Server, you must run SetSPN.exe to remove expired SPNs, and then you must add a valid SPN. For additional information, see the "Security Account Delegation" topic in SQL Server 2000 Books Online. To do so, visit the following Microsoft Web site:

    http://msdn2.microsoft.com/en-us/library/aa905162(SQL.80).aspx

    For more information about Windows 2000 Resource Kits, visit the following Microsoft Web site:

    http://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/default.mspx?mfr=true

  6. Verify that name resolution is occurring correctly. Name resolution methods may include DNS, WINS, HOSTS files, and LMHOSTS files. For more information about name resolution problems and troubleshooting, click the following article number to view the article in the Microsoft Knowledge Base:

    169790 How to troubleshoot basic TCP/IP problems

  7. For more information about how to troubleshoot accessibility and firewall issues with Active Directory, click the following article numbers to view the articles in the Microsoft Knowledge Base:

    291382 Frequently asked questions about Windows 2000 DNS and Windows Server 2003 DNS

    224196 Restricting Active Directory replication traffic and client RPC traffic to a specific port

How to configure the SQL Server service to create SPNs dynamically for the SQL Server instances

To configure the SQL Server service to create SPNs dynamically, you must modify the account's access control settings in the Active Directory directory service. You must grant the "Read servicePrincipalName" permission and the "Write servicePrincipalName" permission for the SQL Server service account.

Warning If you use the Active Directory Service Interfaces (ADSI) Edit snap-in, the LDP utility, or any other LDAP version 3 clients and you incorrectly modify the attributes of Active Directory objects, you can cause serious problems. These problems may require that you reinstall Microsoft Windows Server 2003, Microsoft Windows 2000 Server, Microsoft Exchange Server 2003, Microsoft Exchange 2000 Server, or both Windows and Exchange. We cannot guarantee that problems caused by incorrectly modifying the attributes of Active Directory objects can be resolved. Modify these attributes at your own risk.

Note To grant the appropriate permissions and user rights to the SQL Server startup account, you must be logged on as a domain administrator, or you must ask your domain administrator to do this task.

To configure the SQL Server service to create SPNs dynamically, follow these steps:

  1. Click Start, click Run, type Adsiedit.msc, and then click OK.
  2. In the ADSI Edit snap-in, expand Domain [DomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= AccountName, and then click Properties.


Notes

    • DomainName is a placeholder for the name of the domain.
    • RootDomainName is a placeholder for the name of the root domain.
    • AccountName is a placeholder for the account that you specify to start the SQL Server service.
    • If you specify the Local System account to start the SQL Server service, AccountName is a placeholder for the account that you use to log on to Microsoft Windows.
    • If you specify a domain user account to start the SQL Server service, AccountName is a placeholder for the domain user account.
  2. In the CN= AccountName Properties dialog box, click the Security tab.
  3. On the Security tab, click Advanced.
  4. In the Advanced Security Settings dialog box, make sure that SELF is listed under Permission entries.


If SELF is not listed, click Add, and then add SELF.

  1. Under Permission entries, click SELF, and then click Edit.
  2. In the Permission Entry dialog box, click the Properties tab.
  3. On the Properties tab, click This object only in the Apply onto list, and then make sure that the check boxes for the following permissions are selected under Permissions:
    • Read servicePrincipalName
    • Write servicePrincipalName
  4. Click OK three times, and then exit the ADSI Edit snap-in.

For help with this process, contact Active Directory product support, and mention this Microsoft Knowledge Base article.

Verify the server environment

Check some basic settings on the computer where SQL Server is installed:

  1. Kerberos is not supported on Windows 2000-based computers that are running Windows Clustering unless you have applied Service Pack 3 (or later) to Windows 2000. Therefore any attempt to use SSPI authentication on a clustered instance of SQL Server might not succeed. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

    235529 Kerberos support on Windows 2000-based server clusters

  2. Verify that the server is running Windows 2000 Service Pack 1 (SP1). For more information about Kerberos support on Windows 2000-based servers, click the following article number to view the article in the Microsoft Knowledge Base:

    267588 "Cannot generate SSPI context" error message is displayed when you connect to SQL Server 2000

  3. On a cluster, if the account that you use to start SQL Server, SQL Server Agent, or full-text search services changes, such as a new password, follow the steps that are provided in the following Microsoft Knowledge Base article:

    239885 How to change service accounts for a clustered computer that is running SQL Server

  4. Verify if the account that you use to start SQL Server has the appropriate permissions. If you are using an account that is not a member of the Local Administrators group, see the "Setting up Windows Services Accounts" topic in SQL Server Books Online for a detailed list of permissions that this account must have:

    http://msdn2.microsoft.com/en-us/library/aa176564(SQL.80).aspx

Verify the client environment

Verify the following on the client:

  1. Make sure that the NTLM Security Support Provider is correctly installed and enabled on the client. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

    269541 Error message when you connect to SQL Server if the Windows NT LM Security Support Provider registry key is missing: "cannot generate SSPI context"

  2. Determine if you are using cached credentials. If you are logged on to the client with cached credentials, log off the computer and then log back on when you can connect to a domain controller to prevent the cached credentials from being used. For more information about how to determine if you are using cached credentials, click the following article number to view the article in the Microsoft Knowledge Base:

    242536 User is not alerted when logging on with domain cached credentials

  3. Verify that the dates on the client and the server are valid. If the dates are too far apart, your certificates may be considered invalid.
  4. SSPI uses a file named Security.dll. If any other application installs a file with this name, the other file may be used instead of the actual SSPI file. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

    253577 Error: 80004005 - MS ODBC SQL Server driver cannot initialize SSPI package

  5. If the operating system on the client is Microsoft Windows 98, you must install the Client for Microsoft Networks component on the client. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

    267550 BUG: "Assertion failed" when you connect to a SQL Server through TCP/IP

Verify the client network utility

The Client Network Utility (CNU) is delivered together with Microsoft Data Access Components (MDAC) and it is used to configure connectivity to computers that are running SQL Server. You can use the MDAC Cliconfg.exe CNU utility to configure connectivity:

  1. On the General tab, the way protocols are defined varies according to the MDAC version. With earlier versions of MDAC, you can select a "default" protocol. On the latest versions of MDAC, you can enable one or more protocols with one at the top of the list when you connect to SQL Server. Because SSPI applies only to TCP/IP, you can use a different protocol, such as Named Pipes, to avoid the error.
  2. Check the Alias tab in the CNU to verify if an alias has been defined for the server that you are trying to connect. If a server alias has been defined, check the settings for how this computer is configured to connect to SQL Server. You can verify this by deleting the alias server to see if the behavior changes.
  3. If the alias server is not defined on CNU, add the alias for the server that you are connecting to. When you perform this task, you are also explicitly defining the protocol and optionally defining the IP address and the port.

Information to collect to open a Microsoft Product Support (PSS) case

If you cannot obtain the cause of the problem by using the troubleshooting steps in this article, collect the following information and open a Microsoft Product Support (PSS) case:

For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support


  1. Generate a sqldiag report from SQL Server. For more information, see the "sqldiag Utility" topic in SQL Server Books Online.
  2. Capture a screenshot of the error on the client.
  3. On the node that cannot connect to SQL Server, type the following command from the command prompt:

    net start > started.txt

    This command generates a file named Started.txt in the directory where you run the command.
  4. Save the values for the registry key under the following registry key on your client computer:

    HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MSSQLSERVER\CLIENT\CONNECTTO

  5. In a clustered environment, get the value of following registry key for each node of the cluster:

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\LSA\LMCompatibilityLevel

  6. In a clustered environment, see if the following registry key exists on each cluster server node:

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\NTLMSsp

  7. Capture the results if you connect to SQL Server by using a Universal Naming Convention (UNC) name (or the SQL Network Name on a cluster) from the client.
  8. Capture the results if you ping the computer name (or the SQL Network Name on a cluster) from the client.
  9. Save the name of the user accounts you use to start each one of the SQL Server services (MSSQLServer, SQLServerAgent, MSSearch).
  10. The support professional must know whether SQL Server is configured for Mixed Authentication or Windows Only Authentication.
  11. See if you can connect to the computer that is running SQL Server from the same client by using SQL Server Authentication.
  12. See if you can connect by using Named Pipes protocol.

How to manually set up a Service Principle Name for SQL Server

For more information about how to manually set up a Service Principle Name for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:

319723 How to use Kerberos authentication in SQL Server




The Security Support Provider Interface (SSPI) is the interface to Microsoft Windows NT security that is used for Kerberos authentication, and supports the authentication scheme of the NTLM Security Support Provider. Authentication occurs at the operating system level when you log on to a Windows domain. Kerberos authentication is only available on Windows 2000-based computers that have Kerberos enabled and that are using Active Directory.

SSPI is only used for TCP/IP connections that are made by using Windows Authentication. (Windows Authentication is also known as Trusted Connections or Integrated Security.) SSPI is not used by Named Pipes or multi-protocol connections. Therefore, you can avoid the problem by configuring your clients to connect from a protocol other than TCP/IP.

When a SQL Server client tries to use integrated security over TCP/IP sockets to a remote computer that is running SQL Server, the SQL Server client network library uses the SSPI API to perform security delegation. The SQL Server network client (Dbnetlib.dll) makes a call to the AcquireCredentialsHandle function and passes in "negotiate" for the pszPackage parameter. This notifies the underlying security provider to perform negotiate delegation. In this context, negotiate means to try either Kerberos or NTLM authentication on Windows-based computers. In other words, Windows use Kerberos delegation if the destination computer that is running SQL Server has an associated, correctly configured SPN. Otherwise, Windows use NTLM delegation.

Note Verify that you are not using an account named "SYSTEM" to start any of the SQL Server services (MSSQLServer, SQLServerAgent, MSSearch). The keyword SYSTEM may cause conflicts with the Key Distribution Center (KDC).

REFERENCES

For more information about how Kerberos and SSPI security works, click the following article numbers to view the articles in the Microsoft Knowledge Base:

266080 Answers to frequently asked Kerberos questions


231789 Local logon process for Windows 2000


304161 SSPI mutual authentication is indicated on the client side but not on the server side


232179 Kerberos administration in Windows 2000


230476 Description of common Kerberos-related errors in Windows 2000


262177 How to enable Kerberos event logging


277658 Setspn fails if domain name differs from NetBIOS name where SQL Server SPN is registered


244474 How to force Kerberos to use TCP instead of UDP in Windows Server 2003, in Windows XP, and in Windows 2000


326985 How to troubleshoot Kerberos-related issues in IIS


To see a white paper about Microsoft SQL Server 2000 security, visit the following Microsoft Web site:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec00.mspx


Keywords: kbhowtomaster kbhowto KB811889



Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.


Retrieved from ‘https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/811889&oldid=332649’
the Creative Commons 3.0 ShareAlike (except the Microsoft KB Archive).
Powered by MediaWiki