Cfrgtkky

I have a live SQL Server 2012 environment that had no SPN registered - this has previously not been an issue as Kerberos authentication wasn't required. But now it is required.

I have configured everything correctly, but after these changes I want to ensure that SQL Server can register the SPN's successfully- preferably without a service restart as this is a live environment.

Is there a way of doing this?

P.S. I know I can register the SPN's manually, but I want to make certain that they can be registered automatically.

Quick answer

Question:

I have configured everything correctly, but after these changes I want to ensure that SQL Server can register the SPN's successfully- preferably without a service restart as this is a live environment.

Is there a way of doing this?

Answer:

No.

Explanation

From the official documentation Register a Service Principal Name for Kerberos Connections

A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it is registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration has not been performed or fails, the Windows security layer cannot determine the account associated with the SPN, and Kerberos authentication will not be used.

and

If the server cannot automatically register the SPN, the SPN must be registered manually. See Manual SPN Registration.

and further down in the section Automatic SPN Registration you can find

When an instance of the SQL Server Database Engine starts, SQL Server tries to register the SPN for the SQL Server service. When the instance is stopped, SQL Server tries to unregister the SPN. For a TCP/IP connection the SPN is registered in the format MSSQLSvc/:.Both named instances and the default instance are registered as MSSQLSvc, relying on the value to differentiate the instances.

Summary

The SQL Server Service essentially regsisters the SPN on service start automatically. If you cannot start the SQL Server Service then you will have to perform this task manually.

Considerations

Ensure the service account that start the SQL Server Service is allowed to read and write SPNs.

To do this, you must grant the following access control settings for the SQL Server service account in the Active Directory directory service:

• Read servicePrincipalName


• Write servicePrincipalName

^{Reference: How to use Kerberos authentication in SQL Server (Microsoft Support)}