2

A separate domain account was created for the database engine service of a SQL Server Failover Cluster Instance. Server Principal Names (SPNs) were added manually via the SetSPN.exe command line tool.

If the domain account for the database engine service is a member of Domain Admins, SQL can successfully (and automatically) register SPNs when the service starts (eg server reboot, cluster failover event, manual restart of db engine service). When the domain account for the db engine service is removed from the Domain Admins Active Directory group, SQL fails to register SPNs.

The db engine service account needs certain permissions to register or modify a SPN. Microsoft has listed the permissions necessary for Automatic SPN Registration in their documentation:

  • Permission: Validated Write servicePrincipalName
  • Property: Read servicePrincipalName
  • Property: Write servicePrincipalName

Active Directory - Computer Properties Active Directory - Permission Entry for ServerX Active Directory - Effective Permissions

I've followed along with the documentation. I've also verified Effective Access for the domain account. But I just can't get automatic SPN registration to work without elevated permissions (in Domain Admins).

Something has been missed, but I don't know what. Did I miss a step? Is anything missing from the documentation?

UPDATE
I may have found an answer. Everything I posted above relates to the Active Directory account for the computer that hosts the SQL Server instance (or in my case, the virtual cluster name account for the SQL Server Failover Cluster Instance).

The logon account for the database engine service also needs these permissions in Active Directory:

  • Read servicePrincipalName
  • Write servicePrincipalName

However, it does not appear those permissions can be added in the Active Directory Users and Computers GUI tool. ADSIEdit is what I used. Special thanks to Randolph West. His answer to another similar question has all the details.

1
  • 1
    Put the answer as an actual answer below, rather than as part of the question. You are free to answer your own question Commented Apr 26, 2023 at 23:03

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.