SQL Server running under a domain account cannot register its SPN

I am trying to configure a fresh install of SQL Server to run under a domain account. However, I get intermittent errors when trying to connect to the server using another domain account, and I still see The SQL Server Network Interface library could not register the Service Principal Name when trawling the ERRORLOG file.

I have added my service account (not a Managed Service Account, just a regular user account), to an AD group (e.g. SQL Servers), and I have added an ACE to my domain Computers container's ACL, for this group, selecting:

• Apply to: Descendant Computer objects
• Validated write to service principal name: Allow
• Read servicePrincipalName: Allow
• Write servicePrincipalName: Allow

I have replicated this to all domain controllers, and confirmed the inheritence of the new ACE to the specific computer object, using both the Effective Permissions tab and with dsacls CN=SERVER01,CN=Computers,DC=fabrikam,DC=local, the latter of which now includes:

Allow FABRIKAM\SQL Servers
                                  SPECIAL ACCESS for Validated write to service principal name   <Inherited from parent>
                                  WRITE SELF
Allow FABRIKAM\SQL Servers
                                  SPECIAL ACCESS for Validated write to service principal name   <Inherited from parent>
                                  WRITE SELF
                                  WRITE PROPERTY
                                  READ PROPERTY

However, when I restart the SQL Server service, I still see the could not register the Service Principal Name message. I have also restarted the server, with the same result.

I have used Sysinternals Process Explorer to inspect the running sqlservr.exe; the Security tab there clearly shows the correct service user and its membership of the SQL Servers group.

I know I can manually add the SPN with setspn -A, but that isn't really the point.

What else must I do to ensure that the service account (and any future account I place in the SQL Servers group) can automatically register its own SPN without manual intervention?

AND/OR

How can I further diagnose which privileges/permissions are missing here?

Solution 1:

I found it.

I manually registered the SPN to the service account, then inspected the AD with ADSIEdit, only to find that the manually-registered SPNs were not stored in the servicePrincipalName field of the Computer account, but the servicePrincipalName field of the specific User account.

So, instead of granting my SQL Servers group rights to register their own SPNs, I had (inadvertantly) granted them the rights to alter the SPNs registered by services running as the Local System / Network Service accounts on that computer.

I have now removed the new ACE from the Computers container and, instead, created a new SQL Servers Organisational Unit. I have added an ACE for SELF to this OU, and constrained it to apply to descendant users:

• SQL Servers OU ACL
  • SELF
    • Apply to: Descendant User objects
    • Read servicePrincipalName: Allow
    • Write servicePrincipalName: Allow

Now, when I start my SQL Server instance, I see the expected The SQL Server Network Interface library successfully registered the Service Principal Name, and Kerberos is now being used for my remote connections.

(Now to update our internal process documentation, so it requires new SQL Server service accounts be created under the new OU, rather than added to the group)

Edit: Note that a domain administrator can also manually register SPNs to a domain account, using setspn.exe.

setspn -S MSSQLSvc/myhost.redmond.microsoft.com:1433 DOMAIN\User
setspn -S MSSQLSvc/myhost.redmond.microsoft.com DOMAIN\User

Register a Service Principal Name for Kerberos Connections (TechNet).

Edit 2: If the Read servicePrincipalName and Write servicePrincipalName properties are not visible in the ACE list, go to the Attribute Editor tab of the object's Properties dialog, click the Filter button and ensure the following:

• Show only attributes that have values is unchecked
• Show only writable attributes is unchecked
• Show attributes: Mandatory is checked
• Show attributes: Optional is checked
• Show read-only attributes: Constructed is checked
• Show read-only attributes: Backlink is checked
• Show read-only attributes: System-only is checked

(Other combinations may work, but this is what does it for me)

Solution 2:

You didn't say what version of SQL Server you were running, nor the permissions of the service account (aside from it not being a Managed Service Account and having write SPN), but from the information you're providing I believe the account doesn't have the authority to register itself as an SPN. Even though, yes, you've explicitly granted that.

Apparently, SQL 2012 requires a virtual account or Managed Service Account on Server 2008:

When the Database Engine service starts, it attempts to register the Service Principal Name (SPN). If the account starting SQL Server doesn’t have permission to register a SPN in Active Directory Domain Services, this call will fail and a warning message will be logged in the Application event log as well as the SQL Server error log. To register the SPN, the Database Engine must be running under a built-in account, such as Local System (not recommended), or NETWORK SERVICE, or an account that has permission to register an SPN, such as a domain administrator account. When SQL Server is running on the Windows 7 or Windows Server 2008 R2 operating system, you can run SQL Server using a virtual account or a managed service account (MSA). Both virtual accounts and MSA’s can register an SPN. If SQL Server is not running under one of these accounts, the SPN is not registered at startup and the domain administrator must register the SPN manually.

I hope that helps.