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.
What is Service Principal Name (SPN)?
SPN is a unique identifier for each service that is running on servers. With the help of SPN the clients which try to connect to the service can easily identify it. SPN for each service is registered in the Active Directory. SPNs can be registered under a Computer account or as a user account in Active Directory. The SPN for a service is created in the following format.
<service name>/<FQDN of the server>:<port number>
How is SPN created?
When a service starts, the service tries to create the SPN (if it does not exist already) under the credentials of the service start up account. If the service is configured to run under machine accounts (Local System, Network service), SPN is created under a Computer Account in AD. If the service is configured to start using a domain user account, the SPN is created under the user account in Active Directory.
If the service is starting under a domain account, that account should have Domain Administrator privilege in the Active Directory. Else the creation of the SPN will fail when the service starts. It is not a good security practice grant service accounts with Domain Administrator privilege. SPN can be manually added using the setspn.exe utility.
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 ( Granting these rights is not recommended )
- 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!
Another way to fix SPN issue:
There are a few ways that we can check if the SPN has been registered successfully. If your SQL Server instance is running under a domain account (which is recommended) you can run the following command to see the services that are registered. If there are no services registered for this account you will get the error message below the command.
C:\Users\test>setspn -l DOMAIN\SQLServiceAccount FindDomainForAccount: Call to DsGetDcNameWithAccountW failed with return value 0x00000525 Could not find account SQLServiceAccount
Alternatively, you can also use the SQL Server error log to validate if the SPN has been registered successfully or not. By either filtering within the SSMS GUI or using xp_read_errorlog we can search for the string below. If found we know the SPN did not register successfully.
The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
Finally, you can contact your system administrator and have them use the ADSIEdit MMC console to manually check if the service is registered.
Now that we’ve identified the issue we can go through a couple of different options that will allow us to successfully register the SPN and use Kerberos authentication.
Option 1 – Register SPN automatically which we discussed in above
To enable the SPN to be registered automatically on SQL Server startup the service must be running under the “Local System” or “Network Service” accounts (not recommended), under a domain administrator account, or under an account that has permissions to register an SPN. The permissions required for this are the “Read servicePrincipalName” and “Write servicePrincipalName” access control settings in the Active Directory service. One thing that should be noted is granting these rights is not recommended (see http://support.microsoft.com/kb/319723) if SQL Server is clustered or if you have multiple domain controllers as latency in Active Directory replication can cause connectivity issues with your SQL Server instance.
Option 2 – Register SPN manually
To register an SPN manually we can use the Microsoft provided Setspn.exe utility. To be able to run this tool and register an SPN you need to be a domain admin or have the appropriate privileges (defined above). One other thing to note is that the -s option ensures that the SPN you are trying to create is not already defined. Here are a couple examples. The first one is for a default instance and the second is for a named instance.
setspn -s MSSQLSvc/myhost.redmond.microsoft.com DOMAIN\SQLServiceAccount setspn -s MSSQLSvc/myhost.redmond.microsoft.com:instancename DOMAIN\SQLServiceAccount
Once you’ve picked and implemented one of these options and if necessary restarted SQL Server you can establish a new connection and run the following TSQL to check that you are now using Kerberos authentication.
select session_id,net_transport,client_net_address,auth_scheme from sys.dm_exec_connections