Everyone knows that it is good practice to use a domain or service account to run the SQL service. I’m sure you do too! However, once you do the right thing and change the SQL Service account, you may start getting the following error message when attempting to connect to the sql server:
“The target principal name is incorrect. Cannot generate SSPI context.”
The explanation, as given by Microsoft in this KB article
If you run the SQL Server service under the LocalSystem account, the SPN is automatically registered and Kerberos authentication 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 by 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.
There are 3 ways to fix the problem:
- Revert to using the Network Service or Local System account (NOT RECOMMENDED)
- Assign the domain account to the Domain Admins group (NOT IDEAL – due to the elevated permissions)
- Fix the problem by giving the domain account just the appropriate permissions in Active Directory. Permissions required are
- ServicePrincipalName: Read
- ServicePrincipalName: Write
We will use the 3rd option to fix the error. First, it is good practice to verify that the problem is actually due to permission issues. Log in to the server where you SQL Instance is running. Go to the error logs and look for the last time that the SQL service was restarted. You should find an error message similar to this:
Date 10/17/2013 9:29:50 AM
Log SQL Server (Archive #1 - 10/17/2013 10:53:00 AM)
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/servername.domainname.net:1433 ] for the SQL Server service. Windows return code: 0x2098, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
This is great. At least now we have verified that the problem is related to the SPN and we are ready to apply the fix.
Log in to the server running your Active Directory service and execute the following steps:
- Run Adsiedit.msc
- In the ADSI Edit snap-in, expand Domain [YourDomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= [YourAccountName, and then click Properties.
- In the CN= AccountName Properties dialog box, click the Security tab.
- On the Security tab, click Advanced.
- In the Advanced Security Settings dialog box, select one (any) of "SELF"'s row
- Click Edit, Open Permission Entry dialog box.
- Make sure Pricipal is "SELF", Type is "Allow" and "Applied to" is "This Object Only", in Properties section, select the properties below:
- Read servicePrincipalName
- Write servicePrincipalName
Click OK to apply all changes and exit the ADSI Edit snap-in
Finally, you need to restart the SQL Service(s) that use the account in question.
You can verify that the SPN has been registered successfully upon the restart by going to the SQL Server logs. You should now see an entry similar to this:
Date 10/17/2013 10:53:58 AM
Log SQL Server (Current - 10/17/2013 10:54:00 AM)
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/servername.domainname.net:1433 ] for the SQL Server service.
Connections to SQL Server should now succeed!