Problem : You have completed an SQL Server setup on a new instance. The setup is successful, you can connect locally and and everything is fine. No headaches. Now to test your SQL Server, you try to connect from a client PC and you get that “Cannot Generate SSPI Context” error. Now what?
Most likely you have the same situation as below;
- You are using Integrated Security.
- Kerberos authentication is used to perform the security delegation. You can verify this with your domain admin.
- You are not using Local System account in your SQL Server service which is a good practice.
Security Support Provider Interface (SSPI) is a set of Windows APIs that allows for delegation and mutual authentication over TCP/IP sockets. Therefore, SSPI allows for 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.
Another term that we have to define here is SPN. SPN stands for Service Principal Name. Kerberos authentication uses an identifier named “Service Principal Name” (SPN). Consider an SPN as a domain or forest unique identifier of some instance in a server resource. You can have an SPN for a web service, for a SQL service, or for an SMTP service. You can have a multiple SPNs if you have multiple instance sitting on your database server.
If you are using a domain user account for your SQL Server service, you need to manually register the SPN for this instance to create the SPN in your Active Directory. The SPN for SQL Server has 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 hosting SQL Server.
- Port: This is the port number that the service is listening on. Unless you have made changes to the default port during your setup, this value is 1433.
To register the SPN for the domain user account in your Active Directory, you may run the following command in your cmd window;
SETSPN -A MSSQLSvc/<SQLServerName>:1433 domain\useraccount
The trick here is that you need to issue this command twice. One for the NETBIOS name, which is the above command, and one for the FQDN (Fully Qualified Domain Name) to allow for the setup to succeed.
So you will have to run also the below as your next step.
SETSPN -A MSSQLSvc/<SQLServerName.domain.com>:1433 domain\useraccount
For multiple instances, you will then need to run it twice for each instance that you have. There are times, when you will need the help of your friendly domain admin to run the setspn command because your current permission is not enough. Also to make sure this is successful, you will need to check that the SQL Browser service is running during the setup, because it will be looking for it (especially for multiple instances) and validate it.
To validate that the setspn command was successful, in your cmd window;
setspn – L domain\useraccount
This should list all the instances you have in your server.
One less headache… 🙂