“Cannot generate SSPI context” error message

If you deal with MS SQL Server should have seen this error message. I faced this issue few times under different scenarios. When I fixed this recently I thought I should write it down  so it could help someone.

One classic scenario  you get this error is if you change the SQL server service account and suddenly you cannot connect to the server from a different machine using SSMS. Meaning using TCP over the network. Mind you, service account needs to be a domain account. The issue here is that there is no valid SPN and server is not able to authenticate the user.

The authentication from a different computer is delegated by using one of the following

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

“Cannot generate SSPI context” error is generated when SSPI uses Kerberos authentication to delegate over TCP/IP and Kerberos authentication cannot complete the necessary operations to successfully delegate the user security token to the destination computer that is running SQL Server” The newly added service account is not able to create SPN’s which is required for kerberos authentication.

You can assign proper permissions to the service account on the active directory directory service so it can generate SPN’s dynamically by following below steps

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.
  3. In the CN= AccountName Properties dialog box, click the Security tab.
  4. On the Security tab, click Advanced.
  5. 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.

  6. Under Permission entries, click SELF, and then click Edit.
  7. In the Permission Entry dialog box, click the Properties tab.
  8. 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
  9. Click OK three times, and then exit the ADSI Edit snap-in.

 If the above steps does not fix the issue

  1. Verify if the service account under which SQL service was running before has any SPN’s by using setspn -L serviceaccountname.
  2. Delete the SPN using setspn -d MSSQLSvc/server name:port

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 as follows: MSSQLSvc/SQLSERVER1.northamerica.corp.mycompany.com:1433

3. Add new SPN for the new service account using setspn -s MSSQLSvc/SQLSERVER1.northamerica.corp.mycompany.com:1433 serviceaccountname . This is SPN is with fully qualified domain . You also need to add one for netbios name MSSQLSvc/SQLSERVERNAME  ServiceAccountName.

Now you should be able to connect to SQL server from a different machine. Meaning using TCP over the network.

 

Infrastructure error while connecting to a SQL server

I faced this error few times on different scenarios. For ex: one user cannot connect to SQL server using SSMS or Application cannot connect to SQL server. This usually happened to me when there was a change in the system like user computer has been replaced or there were some network updates.

Resolution:

  1. Some times just by rebooting the user machine I was able to resolve the issue .
  2. For today , I had to move a DB from Dev server to QA and when application configuration file has been modified to use the new server info I could not connect to it. When I checked the SQL logs surely I saw ‘. Login failed for user ‘xxxx’. Reason: Login-based server access validation failed with an infrastructure error. Check for previous error.  When I launch the SSMS on the SQL server itself I could connect to it using the same user, which means there is no problem with the authentication. But when doing the same across the network it has issues meaning it needed specific permissions on TCP endpoint . I used the below query to do this and it resolved the issue
    GRANT CONNECT SQL TO "xxxxx"
    GRANT CONNECT ON ENDPOINT::"TSQL Default TCP" TO "xxxxx";

Below site has detailed explanation of all the infrastructure errors with severity and state

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx