Application down after SQL Service logon account change

I was working on one of my clients server maintenance schedule. It was a normal maintenance monthly activity to reboot database server every first weekend.  I rebooted the machine and as per the defined steps I checked the SQL services whether its running after the reboot. I verified, all SQL Services & browser service were running fine.

Up to now everything was OK, my maintenance activity completed successfully as per the schedule. But I noticed that SQL Services were running with a local account. As a good practice and per company policy, SQL Services should run with their respective domain account.

To avoid asking for another downtime on shared DB server (which requires lot of coordination) or wait for next schedule maintenance activity, I decided to change the service logon account with domain account in the same maintenance window. I changed the SQL Service account with respective domain account and restarted the services to make the changes in effect. I informed all the application owners on completion of the change.

But it was not excepting that one of the application owner started complaining about their application down. I quickly verified the SQL Services & the browser service again. The services were running absolutely fine. Also, looked at glance the SQL errorlog to make sure no errors reported from database end. Though, I received complaint only one application team, I replied back to the team that everything is OK from database end. However, I also suggested to take a restart of application services. Even after the application services restart, team was still getting the same error message and the application was still down.

Now, I started reading SQL error logs thoroughly and I found the one error message at the starting of errorlog which clearly says an issue with SPN but as an informational message.

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.

At this point, I started relating this error message with the additional change I made to change the logon service account. On the other hand, I tried to connect the SQL instance remotely in CMS [Centralized Management System] via windows authentication. I am getting the error message: “The target principal name is incorrect.  Cannot generate SSPI context.” 

So till now, the problem is clear and now here I have two solutions either change the logon service account back to local account or register the SPNs in AD to fix this issue. In other way, the application team is using kerberos authentication and to make that work you must register SPN. The application owners who are not complaining must be using SQL account to connect the instance.

I start working on the solution, I verify the SPN’s with below command:

setspn -l iLearnSQL\Srv-12345 <domainname\service-account>

Apparently, I don’t get any output which means no SPN is registered for this domain account in AD. Later, I give below commands to AD team to execute:

setspn -A MSSQLSvc/SQL.in.iLearnSQL.com:1433 iLearnSQL\Srv-12345
setspn -A MSSQLSvc/SQL.in.iLearnSQL.com iLearnSQL\Srv-12345
setspn -A MSSQLSvc/SQL:1433 iLearnSQL\Srv-12345
setspn -A MSSQLSvc/SQL iLearnSQL\Srv-12345

As soon as the SPN’s have been registered. Firstly, I try to connect the instance remotely and eventually the connection is successful and I can able to connect the instance. Now, I get the confidence and I inform the application team to try again. Here is the good news that they stop receiving the error message and able to connect the application.

So, the conclusion is that when you are changing the SQL Service logon account to domain account make sure that the SPN’s are registered for the respective domain account. Also, you may download tool Microsoft Kerberos Configuration Manager for SQL Server. This is a diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server.