Allow SQL Service Accounts to create/modify SPNs (Service Principal Name)
A Service Principal Name (SPN) must be registered for the SQL Server service account to allow clients to identify and authenticate the SQL service using Kerberos authentication.
The SetSPN utility can be used to create/delete SPNs but in my test lab I change SQL server configurations on a regular base and so a more flexible way of creating SPNs was required. The following changes to a service accounts permissions allow the account to create/modify SPNs without making it a domain admin.
- Launch Adsiedit and Connect to the “Default Naming Context”
- Expand “Default Naming Context”, expand DC=YOUR_DOMAIN_NAME, expand CN=Users, right-click CN=YOUR_SQL_SERVICE_ACCOUNT_NAME, and then click Properties (if you keep your service accounts in a dedicated OU adjust the service account location above)
- In the CN=YOUR_SQL_SERVICE_ACCOUNT_NAME Properties dialog box, click the Security tab.
- On the Security tab, click Advanced.
- In the Advanced Security Settings dialog box, make sure that SELF is listed under Permission entries (if SELF is not listed, click Add, and then add SELF).
- Under Permission entries, click SELF, and then click Edit.
- In the Permission Entry dialog box, click the Properties tab
- On the Properties tab, click This object only in the Apply onto list, and then make sure that the check boxes for the following permissions are selected under Permissions:
- Exit the ADSI
- Restart the SQL server service and confirm in the Event log (Application) that the SPNs were successfully created.