When you change the service account used by the MS SQL server services, they often are not abel to register the corresponding SPN in active directory.
You should see these messages in the SQL server log when it's working correctly
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/SQL.testdomain.in:24629 ] for the SQL Server service.
In the case you get errors like this, the SPN registration (and therefore later on the lookup via AD) is not working
The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. 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.
For a service account to be able to register the SPN you need to set these rights on the AD user account:
Now you just have to restart the MS SQL service, and it should be able to register the SPN in AD.
If you still receive the error message, then it's because the corresponding SPN's are still/already registered on another object (mostly the computer account of the MS SQL server) and the service account has no rights to modify them (Since we did only allow it to modify it's own rights)
So the simplest way to do this is to use the setspn command to remove the stale entries.
You can look what SPN entries are registered for a specific AD object with this command:
setspn -l domain\sql-server
You will then probably see something like this:
- MSSQLSvc/ sql-server.domain.local
- MSSQLSvc/ sql-server.domain.local:1433
So you can also remove them from the object with:
setspn -d MSSQLSvc/sql-server.domain.local domain\sql-server
setspn -d MSSQLSvc/sql-server.domain.local:1433 domain\sql-server
Now you restart the MS SQL service and it should be able to register.
If it still throws errors, then the SPN is probably assigned to another account. In that case just try to add the spn manually, and it will tell you where the duplicate SPN can be found.
setspn -a MSSQLSvc/sql-server.domain.local domain\service-account
This can also happen when you switch from one service account to another.
