Everyone understands that running the SQL service with a domain or service account is a good idea. I’m sure you do as well! When you change the SQL Service account, however, you may begin to receive the following error notice when attempting to connect to the SQL Server:
According to Microsoft’s explanation in this KB article
The SPN is immediately registered when the SQL Server service is run under the LocalSystem account, and Kerberos authentication works correctly with the SQL Server-running. If you run the SQL Server service as a domain account or a local account, the attempt to create the SPN will most likely fail since the domain account and local account do not have the authority to generate their own SPNs. When the SPN creation fails, it signifies that no SPN has been set up for the SQL Server-running. When you test with a domain administrator account as the SQL Server service account, the SPN is generated successfully. because you have the domain administrator-level credentials required to create an SPN
What is Service Principal Name (SPN)?
Each service that runs on servers has a unique identifier called an SPN. Clients attempting to connect to the service can quickly identify it thanks to SPN. Each service’s SPN is registered in Active Directory. In Active Directory, SPNs can be created under a Computer account or as a user account. The following format is used to create a service’s SPN.
<service name>/<FQDN of the server>:<port number>
How is SPN created?
When a service starts, it attempts to build the SPN (if one does not already exist) using the service start up account’s credentials. SPN is generated under a Computer Account in AD if the service is configured to execute under machine accounts (Local System, Network service). The SPN is created under the user account in Active Directory if the service is configured to start using a domain user account.
If a domain account is used to launch the service, that account should have Domain Administrator privileges in Active Directory. Otherwise, when the service starts, the SPN will not be created. It’s not a good idea to give service accounts to Domain Administrator privilege. By using the setspn.exe utility SPN can be manually added.
There are 3 ways to fix the problem:
- Use the Network Service or Local System account again (NOT RECOMMENDED)
- Assign the domain account to the Domain Admins group (NOT RECOMMENDED – because of the increased access).
- Fix the issue by granting the domain account only the rights it needs in Active Directory. Permissions are needed ( Granting these rights is not recommended )
- ServicePrincipalName: Read
- ServicePrincipalName: Write
To correct the issue, we’ll select the third option. First, be sure the issue isn’t caused by a lack of permission. Log in to the server that hosts your SQL Instance. Look through the error logs for the last time the SQL was used or restarted, You should get something like this as an error message:
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 fantastic. At the very least, we’ve established that the issue is tied to the SPN, and we’re ready to implement the solution.
Log in to the server that hosts your Active Directory service and follow the procedures below:
- Run the Adsiedit.msc programme.
- Expand Domain [YourDomainName], DC= RootDomainName, CN=Users, right-click CN= [YourAccountName], and then Properties in the ADSI Edit snap-in.
- Click the Security tab in the CN= AccountName Properties dialogue box.
- Select Advanced from the Security tab.
- Select one (any) of the “SELF” rows in the Advanced Security Settings dialogue box.
- Open the Permission Entry dialogue box by clicking Edit.
- Select the properties below in the Properties section, making sure Pricipal is “SELF,” Type is “Allow,” and “Applied to” is “This Object Only.”
- Read servicePrincipalName
- Write servicePrincipalName
To apply all changes and quit the ADSI Edit snap-in, click OK.
Finally, the SQL Service(s) that use the account in question must be restarted.
You can check the SQL Server logs to see if the SPN was correctly registered after the restart. You should now see something like 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 option for resolving the SPN problem is to:
We can check if the SPN has been correctly registered in a couple different methods. To check the services that are registered, run the following command if your SQL Server instance is operating under a domain account (which is recommended). If this account does not have any services registered, an error notice will appear below the command.
C:\Users\test>setspn -l DOMAIN\SQLServiceAccount Error message :Find Domain For Account: Call to DsGetDcName With Account failed with return value 0x00000525 Could not find account SQLServiceAccount
Alternatively, you can check the SQL Server error log to see if the SPN was correctly registered or not. We may find the string below by filtering within the SSMS GUI or using xp read errorlog. If the SPN is found, we know it did not register properly.
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
Finally, you can contact your system administrator and request that they use the ADSIEdit MMC console to manually check if the service is registered.
Now that we’ve figured out what’s wrong, we can try a few alternative approaches to successfully register the SPN and use Kerberos authentication.
Option 1 – Register SPN automatically which we discussed in above
The service must be running under the “Local System” or “Network Service” accounts (not recommended), a domain administrator account, or an account with permissions to register an SPN to enable the SPN to be registered immediately on SQL Server launch. The “Read servicePrincipalName” and “Write servicePrincipalName” access control settings in the Active Directory service are necessary for this. If SQL Server is clustered or if you have several domain controllers, granting these capabilities is not advised , as latency in Active Directory replication can cause connectivity issues with SQL Server.
Option 2 – Register SPN manually
We can use the Setspn.exe software provided by Microsoft to manually register an SPN. You must be a domain admin or have the required credentials to run this utility and register an SPN (defined above). It’s also worth noting that the -s option assures that the SPN you’re trying to build hasn’t already been specified. I’ll give you a handful of instances. The first is for a named instance, whereas the second is for a default instance.
setspn -s MSSQLSvc/myhost.redmond.microsoft.com:instancename DOMAIN\SQLServiceAccount setspn -s MSSQLSvc/myhost.redmond.microsoft.com DOMAIN\SQLServiceAccount
After you’ve chosen and implemented one of these options, and if required, rebooted SQL Server, you may create a new connection and validate that you’re now utilizing Kerberos authentication by running the following TSQL.
select session_id,net_transport,client_net_address,auth_scheme from sys.dm_exec_connections