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?