I often use this blog as my own personal scratch space... if any of my writings here end up helping anyone else, that is a major bonus, but I can't count the number of times I've been working on some technical issue and purposely searched through my own articles looking for a Powershell script or something that I vaguely remembered writing that might help with whatever I'm working on at the moment.
And that's why I was really surprised when I searched this blog today for SPN and servicePrincipalName and got nothing. How have I not written anything about SPNs before!? We're about to fix that...
Service Principal Names. A really simple concept that seems, inexplicably, to blow some people's minds. (Or maybe just make them doze off.) And more often than not, when I look into Active Directory environments that use SQL Server, IIS, etc., the admins and owners have usually forgotten about SPNs. And who cares about SPNs anyway? I mean, the application works fine without them, right? I guess... if you don't mind having to use crummy old NTLM authentication when you could be using swanky Kerberos instead!
I'm going to focus specifically on Microsoft SQL Server and how it uses Service Principal Names today. I wrote "#274" in the title of this post because there are many different ways to go about delegating these permissions, and I'm just going to present one possible way. (A way that I think is better than how I have seen other people do it.)
So just as a quick recap, whenever the SQL service starts up, it attempts to register an SPN or service principal name in Active Directory. An SPN is stored as an attribute on a user or computer account in Active Directory, depending on the security context in which the service is operating. Even though the attribute name servicePrincipalName uses a singular tense, it's actually a multi-valued attribute that can and usually does contain many different SPNs for many different services.
So when you configure the SQL Server service (MSSQLSERVER) to run as Local System, the computer account for the computer that's running SQL needs the ability to write or update its own servicePrincipalName attribute on its computer account object in AD. By default, computer accounts already have the permissions to write to their own servicePrincipalName attribute. The name of the privilege is displayed "Validated write to service principal name:"
This is important that the computer only has the ability to write SPNs for itself, because it would be a major security concern for Active Directory accounts to be able to write SPNs on other accounts.
The validated write permission is restricted even further than just the regular ability to write to the servicePrincipalName attribute, as this causes the Directory Service Engine to reject updates that do not conform to the expected DNS FQDN and hostname format. With just the basic write permissions, an account could theoretically write just any old invalid thing into the servicePrincipalName attribute, which is another security concern. However, validated write to service principal name is only applicable to computer objects, not user objects.
So back to when the SQL Service starts up. When SQL is running as Local System, the computer account should have no problem registering an SPN for itself, like so:
However, most organizations run SQL services with a "service account," instead of Local System, and this is where things usually start to go pear-shaped:
You really should be using Managed Service Accounts for this, but the fact of the matter is that adoption of Managed Service Accounts is still very low and most organizations are still using what I would call "traditional" or "legacy" service accounts. There are security advantages to using a service account to run SQL Server rather than using Local System. The main advantage being that if an attacker were to exploit SQL in some way, they could theoretically use that exploit to gain unlimited access to the entire system if SQL were running under the System account because the System account has unlimited access to the machine. But the downside to using a regular user account as a "service account" is that regular user accounts do not, by default, have the permission to update SPNs, not even on themselves.
So let's fix that.
In other similar articles that you'll find on the web, you might see the author advising you to create a security group, putting all your service accounts into that security group, and then delegating the "Write servicePrincipalName" permission to that group for the entire domain. I would call that practice suboptimal at best. And by suboptimal, I mean terrible. The reason it's terrible is because that gives every member of the "Service Accounts" group the ability to write service principal names for themselves and every other account in the domain, which is certainly a security hazard.
So try this as a better alternative:
First, make a "Legacy Service Accounts" OU or otherwise organize your service accounts into an OU. (You know, right there next to the "Managed Service Accounts" container that you should be using, but I know you won't...)
Next, open up ADSI Edit and connect to your default naming context. (Nothing worse than searching for something in AD Users and Computers for 15 minutes before you realize that you can only find what you're looking for with ADSI Edit.) Right-click on your "Legacy Service Accounts" OU and go to Properties. Then go to the Security tab. Click Advanced. Click Add. Click "Select a principal" and type in SELF and click OK. Leave the Type on "Allow" and change the Applies to: "Descendant User objects." Scroll way down and check the box that says "Write servicePrincipalName".
Click OK a couple times to confirm and apply your changes.
Now what you've done is you've configured each account in that OU to inherit the SELF: write servicePrincipalName permission so that it is allowed to write SPNs on itself, but not on other accounts. You can validate this by viewing the "Effective Access" of the SELF principal (in ADSI Edit - Not in ADUC!) of any given account in that OU. You'll also notice that SQL Server starts logging success messages regarding SPN registration instead of failure.