I was working on one of my clients server recently. This was during a normal maintenance monthly activity to reboot the database server every first weekend. I rebooted the machine, and as per the defined steps, I checked the SQL services after the reboot to ensure they were up. I verified all SQL Services and the SQL Browser service were running fine.

Up to this point, everything was OK. My maintenance activity completed successfully as per the schedule. But while checking, 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 more downtime on the shared database server, which requires a lot of coordination, or waiting for the next schedule maintenance activity, I decided to change the service logon account to a domain account in the same maintenance window. I changed the SQL Service account to the respective domain account and restarted the services to ensure the changes took effect. I informed all the application owners on completion of the change.

I was not expecting that one of the application owners to start complaining about their application being down. I quickly verified the SQL Services and the browser service again. The services were running absolutely fine. Also, I glanced at the SQL Server error log to make sure no errors were reported from the database end. I replied back to the team that everything is OK from database end, and I suggested that they restart the application services. Even after the application services restart, they reported getting the same error message and the application was still down.

At this time, I started reading SQL Server error logs more thoroughly and I found one error message at the beginning of the error log, which clearly notes an issue with the 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 the logon service account. I tried to connect the instance remotely in SSMS via Windows authentication. I received the error message: "The target principal name is incorrect.  Cannot generate SSPI context." 

Now the problem is clear and I have two solutions. Either change the logon service account back to the local account or register the SPNs in AD to fix this issue. The application team is using Kerberos authentication and to make that work you must register the SPN. The application owners who are not complaining must be using a SQL account to connect to the instance.

I start working on the solution. First, I verify the SPN's with the below command:

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

I don't get any output, which means no SPN is registered for this domain account in AD. Later, I give the below commands to the 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 am confidendent this is working, and I inform the application team to try again. The good news is that they stop receiving the error message and are able to connect the application.

The conclusion is that when you are changing the SQL Service logon account to a 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.