Changing service account for MS SQL Server 2012 and the following issues

Assume that you need to change service account for Microsoft SQL Server 2012 service. If earlier it was System, then now you need to work under some domain account.

Procedure itself of changing account is quite simple. Just start Sql Server Configuration Manager and in SQL Server Services subtree change properties of service.

Issues you will encounter after changing service account of Microsoft SQL Server 2012.

Issue №1.

Very possible, you will get an error when trying to remotely login to SQL-server using any domain account. At the same time, you will still be able to login locally (I mean - to log in using Management Studio from the same SQL-server) - without problems.

The reason of this is in wrong SPN-record in Active Directory domain, which was created for computer account (and now it's needed to be created for the new service account). In general, SQL-server tries to update its SPN-record in domain. But in this case it cannot do this, and in the SQL-server logs (not in the Windows Event Log) you will find the following errors:

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/alwayson-tst-1.domain.local: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.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/alwayson-tst-1.domain.local ] 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.

 OK, that's not a big problem. Go to the Domain Controller, open ADSI Edit, find your new account for the SQL-server, open Properties and open Security tab. Then - Advanced button. Pick any ACL record, where Principal - SELF, and Applies to - This object only.

Set the following properties and apply them:

• Read servicePrincipalName
• Write servicePrincipalName

And restart SQL services.

Issue №2.

Unfortunately, after setting correct security properties for the service account in AD, there is a possibility (but - not necessary) that remote log in will still not work. And the errors in SQL logs will change to the following:

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/alwayson-tst-2.domain.local:1433 ] for the SQL Server service. Windows return code: 0x21c7, 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.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/alwayson-tst-2.domain.local ] for the SQL Server service. Windows return code: 0x21c7, 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.

Error 0x21c7 means, that the value provided for the new SPN-record (remember - SQL-server try every start to update it) is not unique within the Active Directory forest. I.e. value that service tries to write to the AD, already exist somewhere. Let's check the computer account.

So return to the Domain Controller:

C:\Users\user>setspn -l alwayson-tst-2
Registered ServicePrincipalNames for CN=ALWAYSON-TST-2,CN=Computers,DC=domain,DC=local:
        MSSQLSvc/alwayson-tst-2.domain.local
        MSSQLSvc/alwayson-tst-2.domain.local:1433
        MSServerClusterMgmtAPI/ALWAYSON-TST-2
        MSServerClusterMgmtAPI/alwayson-tst-2.domain.local
        TERMSRV/ALWAYSON-TST-2
        TERMSRV/alwayson-tst-2.domain.local
        WSMAN/alwayson-tst-2
        WSMAN/alwayson-tst-2.domain.local
        RestrictedKrbHost/ALWAYSON-TST-2
        HOST/ALWAYSON-TST-2
        RestrictedKrbHost/alwayson-tst-2.domain.local
        HOST/alwayson-tst-2.domain.local

Really, there are SPN-records "bound" to the computer account. You will need to delete them:

C:\Users\user>setspn -d MSSQLSvc/alwayson-tst-2.domain.local  alwayson-tst-2

Unregistering ServicePrincipalNames for CN=ALWAYSON-TST-2,CN=Computers,DC=domain,DC=local
        MSSQLSvc/alwayson-tst-2.domain.local
Updated object

C:\Users\user>setspn -d MSSQLSvc/alwayson-tst-2.domain.local:1433  alwayson-tst-2
Unregistering ServicePrincipalNames for CN=ALWAYSON-TST-2,CN=Computers,DC=domain,DC=local
        MSSQLSvc/alwayson-tst-2.domain.local:1433
Updated object

C:\Users\user>setspn -l alwayson-tst-2
Registered ServicePrincipalNames for CN=ALWAYSON-TST-2,CN=Computers,DC=domain,DC=local:
        MSServerClusterMgmtAPI/ALWAYSON-TST-2
        MSServerClusterMgmtAPI/alwayson-tst-2.domain.local
        TERMSRV/ALWAYSON-TST-2
        TERMSRV/alwayson-tst-2.domain.local
        WSMAN/alwayson-tst-2
        WSMAN/alwayson-tst-2.domain.local
        RestrictedKrbHost/ALWAYSON-TST-2
        HOST/ALWAYSON-TST-2
        RestrictedKrbHost/alwayson-tst-2.domain.local
        HOST/alwayson-tst-2.domain.local

As you can see, unneeded records are deleted. Now you can try to reboot SQL-server and check the logs again. You should see:

The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/alwayson-tst-1.domain.local:1433 ] for the SQL Server service.

The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/alwayson-tst-1.domain.local ] for the SQL Server service.

Now remote logging in should work too.