Fix Service Principal Name (SPN) for SQL Server in Windows 2012 AD Environment

 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.

Symptoms:

SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/VMMSQL01.dbaglobe.com ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/VMMSQL01.dbaglobe.com:1433 ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

How to Fix:  (Example Service Account is DBAGLOBE\sqlsvcs)

If SELF is not listed, click Add, and then add SELF.
Click Edit

Ensure the following permissions under Permissions are selected:

• Read servicePrincipalName
• Write servicePrincipalName

Click Apply to apply the setting.
Modify the account to make sure the scope is “This object only”.

Check  the effective Access to ensure “Write servicePrincipalName” is granted.


Restart SQL Server Services

Grant the privilege via command line:

C:\Users\Administrator>dsacls CN=sqlsvcs2,CN=Users,DC=dbaglobe,DC=com /G SELF:RPWP;servicePrincipalName

Additional verification to make sure service account has “Validated write to service principal name” privilege

C:\Users\Administrator>hostname
VMMAD01
C:\Users\Administrator>dsacls CN=sqlsvcs,CN=Users,DC=dbaglobe,DC=com
Owner: DBAGLOBE\Domain Admins
Group: DBAGLOBE\Domain Admins
Access list:
Allow DBAGLOBE\Domain Admins          FULL CONTROL
Allow BUILTIN\Account Operators       FULL CONTROL
Allow NT AUTHORITY\Authenticated Usersq
                                      SPECIAL ACCESS
                                      READ PERMISSONS
Allow NT AUTHORITY\SELF               SPECIAL ACCESS
                                      READ PERMISSONS
                                      LIST CONTENTS
                                      READ PROPERTY
Allow NT AUTHORITY\SYSTEM             FULL CONTROL
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS   <Inherited from parent>
                                      READ PERMISSONS
                                      LIST CONTENTS
                                      READ PROPERTY
                                      LIST OBJECT
Allow DBAGLOBE\Enterprise Admins      FULL CONTROL   <Inherited from parent>
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS   <Inherited from parent>
                                      LIST CONTENTS
Allow BUILTIN\Administrators          SPECIAL ACCESS   <Inherited from parent>
                                      DELETE
                                      READ PERMISSONS
                                      WRITE PERMISSIONS
                                      CHANGE OWNERSHIP
                                      CREATE CHILD
                                      LIST CONTENTS
                                      WRITE SELF
                                      WRITE PROPERTY
                                      READ PROPERTY
                                      LIST OBJECT
                                      CONTROL ACCESS
Allow DBAGLOBE\RAS and IAS Servers    SPECIAL ACCESS for Account Restrictions
                                      READ PROPERTY
Allow DBAGLOBE\RAS and IAS Servers    SPECIAL ACCESS for Logon Information
                                      READ PROPERTY
Allow DBAGLOBE\RAS and IAS Servers    SPECIAL ACCESS for Group Membership
                                      READ PROPERTY
Allow DBAGLOBE\RAS and IAS Servers    SPECIAL ACCESS for Remote Access Information
                                      READ PROPERTY
Allow DBAGLOBE\Cert Publishers        SPECIAL ACCESS for userCertificate
                                      WRITE PROPERTY
                                      READ PROPERTY
Allow BUILTIN\Windows Authorization Access Group
                                      SPECIAL ACCESS for tokenGroupsGlobalAndUniversal
                                      READ PROPERTY
Allow BUILTIN\Terminal Server License Servers
                                      SPECIAL ACCESS for terminalServer
                                      WRITE PROPERTY
                                      READ PROPERTY
Allow BUILTIN\Terminal Server License Servers
                                      SPECIAL ACCESS for Terminal Server License Server
                                      WRITE PROPERTY
                                      READ PROPERTY
Allow NT AUTHORITY\Authenticated Users
                                      SPECIAL ACCESS for General Information
                                      READ PROPERTY
Allow NT AUTHORITY\Authenticated Users
                                      SPECIAL ACCESS for Public Information
                                      READ PROPERTY
Allow NT AUTHORITY\Authenticated Users
                                      SPECIAL ACCESS for Personal Information
                                      READ PROPERTY
Allow NT AUTHORITY\Authenticated Users
                                      SPECIAL ACCESS for Web Information
                                      READ PROPERTY
Allow NT AUTHORITY\SELF               SPECIAL ACCESS for Personal Information
                                      WRITE PROPERTY
                                      READ PROPERTY
Allow NT AUTHORITY\SELF               SPECIAL ACCESS for Phone and Mail Options
                                      WRITE PROPERTY
                                      READ PROPERTY
Allow NT AUTHORITY\SELF               SPECIAL ACCESS for Web Information
                                      WRITE PROPERTY
                                      READ PROPERTY
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS for Account Restrictions   <Inherited from parent>
                                      READ PROPERTY
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS for Logon Information   <Inherited from parent>
                                      READ PROPERTY
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS for Group Membership   <Inherited from parent>
                                      READ PROPERTY
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS for General Information   <Inherited from parent>
                                      READ PROPERTY
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS for Remote Access Information   <Inherited from parent>
                                      READ PROPERTY
Allow NT AUTHORITY\ENTERPRISE DOMAIN CONTROLLERS
                                      SPECIAL ACCESS for tokenGroups   <Inherited from parent>
                                      READ PROPERTY
Allow NT AUTHORITY\SELF               SPECIAL ACCESS for msDS-AllowedToActOnBehalfOfOtherIdentity   <Inherited from parent>
                                      WRITE PROPERTY
                                      READ PROPERTY
Allow NT AUTHORITY\SELF               SPECIAL ACCESS for Private Information   <Inherited from parent>
                                      WRITE PROPERTY
                                      READ PROPERTY
                                      CONTROL ACCESS
Allow NT AUTHORITY\SELF               SPECIAL ACCESS for Validated write to service principal name
                                      WRITE PROPERTY
                                      READ PROPERTY
Allow Everyone                        Change Password
Allow NT AUTHORITY\SELF               Change Password
Allow NT AUTHORITY\SELF               Send As
Allow NT AUTHORITY\SELF               Receive As
Permissions inherited to subobjects are:
Inherited to all subobjects
Allow DBAGLOBE\Enterprise Admins      FULL CONTROL   <Inherited from parent>
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS   <Inherited from parent>
                                      LIST CONTENTS
Allow BUILTIN\Administrators          SPECIAL ACCESS   <Inherited from parent>
                                      DELETE
                                      READ PERMISSONS
                                      WRITE PERMISSIONS
                                      CHANGE OWNERSHIP
                                      CREATE CHILD
                                      LIST CONTENTS
                                      WRITE SELF
                                      WRITE PROPERTY
                                      READ PROPERTY
                                      LIST OBJECT
                                      CONTROL ACCESS
Allow NT AUTHORITY\SELF               SPECIAL ACCESS for msDS-AllowedToActOnBehalfOfOtherIdentity   <Inherited from parent>
                                      WRITE PROPERTY
                                      READ PROPERTY
Allow NT AUTHORITY\SELF               SPECIAL ACCESS for Private Information   <Inherited from parent>
                                      WRITE PROPERTY
                                      READ PROPERTY
                                      CONTROL ACCESS
Inherited to computer
Allow NT AUTHORITY\ENTERPRISE DOMAIN CONTROLLERS
                                      SPECIAL ACCESS for tokenGroups   <Inherited from parent>
                                      READ PROPERTY
Inherited to group
Allow NT AUTHORITY\ENTERPRISE DOMAIN CONTROLLERS
                                      SPECIAL ACCESS for tokenGroups   <Inherited from parent>
                                      READ PROPERTY
Inherited to computer
Allow NT AUTHORITY\SELF               SPECIAL ACCESS for msTPM-TpmInformationForComputer   <Inherited from parent>
                                      WRITE PROPERTY
Inherited to group
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS   <Inherited from parent>
                                      READ PERMISSONS
                                      LIST CONTENTS
                                      READ PROPERTY
                                      LIST OBJECT
Inherited to inetOrgPerson
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS   <Inherited from parent>
                                      READ PERMISSONS
                                      LIST CONTENTS
                                      READ PROPERTY
                                      LIST OBJECT
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS for General Information   <Inherited from parent>
                                      READ PROPERTY
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS for Group Membership   <Inherited from parent>
                                      READ PROPERTY
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS for Logon Information   <Inherited from parent>
                                      READ PROPERTY
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS for Account Restrictions   <Inherited from parent>
                                      READ PROPERTY
Allow BUILTIN\Pre-Windows 2000 Compatible Access
                                      SPECIAL ACCESS for Remote Access Information   <Inherited from parent>
                                      READ PROPERTY
The command completed successfully

References: http://mssqlwiki.com/tag/the-sql-server-network-interface-library-could-not-register-the-service-principal-name-spn/