Dynamically Set SPN's for SQL Service Accounts

For SQL Services Accounts they must have a SPN (service principal name) set. If the service account is also a Domain Admin this will be done automatically. If your SQL service account is not a Domain Admin it will not be able to set the SPN automatically. Usually a way to get around this is to use a program called setspn.exe and set the SPN on behalf of the user account as an Administrator. setspn.exe is part of the windows resource kit.

I am going to show you another way how to do this - to allow a non-Domain Admin SQL service account to dynamically register its own SPN without having to use setspn.exe.

1. Click Start, click Run, type Adsiedit.msc, and then click OK.

2. In the ADSI Edit snap-in, expand Domain [DomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= AccountName, and then click Properties.

3. In the CN= AccountName Properties dialog box, click the Security tab.

4. On the Security tab, click Advanced.

5. 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.

6. Under Permission entries, click SELF, and then click Edit.

7. In the Permission Entry dialog box, click the Properties tab

8. 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:
- Read servicePrincipalName
- Write servicePrincipalName

9. Click OK three times, and then exit the ADSI Edit snap-in.

Below is a screenshot of the configuration required:



This will allow the SQL Serive Account to automatically set its own SPN so you do not have to worry about using setspn.exe anymore.