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?
How can I further diagnose which privileges/permissions are missing here?