I don’t have access, like many DBAs, to the inner bowels of Active Directory.  While I’m more than happy for it to stay this way I still want to check that certain things have been setup correctly and haven’t been “cleaned-up” by a security nazi  focused domain administrator.

One such situation arose recently with Service Principal Names. SPNs are used predominately with impersonation and delegation. There’s a good explanation on SPNs here.

The account SQL Server runs under requires the “Read/Write servicePrincipalName” permission. If for some reason this is removed, and you restart SQL Server, client using the SPN will no longer function.

Luckily this can be easily checked with Quest’s Powershell cmdlets for Active Directory. Once these are installed you can simply do…

Get-QADPermission -Identity Domain\SqlServiceAcc | Where-Object {$_.RightsDisplay -eq "Read/Writ
e servicePrincipalName" -and $_.AccessControlType -eq "Allow"} | SELECT * | Format-List;

The output should look something like below. You may want to hold off rebooting your SQL Server if the ReadProperty/WriteProperty values are missing.

NativeAce : System.DirectoryServices.ActiveDirectoryAccessRule
TargetObject : Domain\SqlServiceAcc
Account : NT AUTHORITY\SELF
TransitiveAccount : NT AUTHORITY\SELF
AccountName : NT AUTHORITY\SELF
AccessControlType : Allow
Rights : ReadProperty, WriteProperty
RightsDisplay : Read/Write servicePrincipalName
Source : NotInherited
ExtendedRight :
ValidatedWrite :
Property : CN=Service-Principal-Name,CN=Schema,CN=Configuration,DC=domain,DC=co,DC=uk
PropertySet :
ApplyTo : ThisObjectOnly
ApplyToDisplay : This object only
ApplyToType :
ChildType :