Wednesday, March 10, 2010

Grant permission for SQL Server Service account to register SPN automatically

While this is a best option if you are using Kerberos authentication in your environment, it is not recommended to do this in clustered SQL Server instance.

As per http://blogs.msdn.com/dataaccesstechnologies/archive/2010/01/06/how-to-grant-readserviceprincipalname-and-writeserviceprincipalname-rights-to-sql-server-service-start-up-account-without-using-adsdiedit-tool.aspx Microsoft only recommends for Service account of Standalone SQL Instance to have this privilege of registering SPN's automatically in AD (ReadServicePrincipalName and WriteServicePrincipalName) and not on clustered instance of SQL Server.

Why it is not recommended in clustered SQL Server instance service account to have privilege to register SPN automatically?
Well, we know that when SQL Server starts, it registers SPN and while it is shutdown, it deregisters SPN. So in a cluster, during failover there might be a problem with this chain. so SPN's might be lost.