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:
- Click Start, click Run, type Adsiedit.msc, and then click OK.
- In the ADSI Edit snap-in, expand Domain [DomainName], expand DC= RootDomainName, expand CN=Users, right-click CN=AccountName, and then click Properties.
- 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.
- In the CN= AccountName Properties dialog box, click the Security tab.
- On the Security tab, click Advanced.
- 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.
- Under Permission entries, click SELF, and then click Edit.
- In the Permission Entry dialog box, click the Properties tab.
- 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
- Click OK three times, and then exit the ADSI Edit snap-in.
If the above steps does not fix the issue
- Verify if the service account under which SQL service was running before has any SPN’s by using setspn -L serviceaccountname.
- 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.