Delegating the Permissions for Service Accounts to Dynamically Register Their Own SPNs #274

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:"

Write SPN permission

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:

Registered an SPN

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:

SPN Registration Fail

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

Legacy Service Accounts OU

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.



Supersymmetry Outlook Add-In v1.0

Update Oct. 4th, 2014: You want the updated version of this addin, v1.1.3.17!

Like millions of others, I use Outlook as an email client, especially at work.  I was drafting an email at work the other day, and after quickly proofreading it, I sent it out.  Only after sending it, of course, did I spot an error.  I had used a parenthesis to start a parenthetical clause (like this,) only I forgot to use the accompanying closing parenthesis at the end of the statement, so it came out (like this.

I realized that I do this quite a bit in my writing, particularly when I'm rapid-firing work emails.  And not just with parentheses, but also with quotation marks, and occasionally curly braces and square brackets.  There are no red squiggly underlines for this and spellcheck won't help you here.

So I wrote an Outlook 2013 Add-In that will catch me if I attempt to send an email that contains an unmatched set of quotation marks, parentheses, curly braces or square brackets.  Notice the popup when I hit the Send button:

Email draft with a mistake in it


It requires Outlook 2013, .NET 4.5, and Windows Vista or later. It should work on both 32-bit and 64-bit machines, though I didn't test it on 32-bit. You may need to install Visual Studio 2010 Tools for Office Runtime, depending on whether you already installed it when you installed Microsoft Office or not.  If you download the package, and your computer already recognizes the *.vsto file extension, then you probably already have the necessary VSTO runtime installed.  On my development machine, I had to uninstall VSTO, delete the "C:\Program Files\Common Files\Microsoft Shared\VSTO" directory, then reinstall VSTO, or else I got an error when trying to install the add-in.  However, on a fresh test machine that never had Visual Studio installed and only had MS Office installed, I did not get the error and only needed to double-click the *.vsto file and everything worked.

Installation

Download the ZIP archive here:

Supersymmetry-v1.0.zip (49.5KB)

Unpack the ZIP archive somewhere... I chose %APPDATA%\Supersymmetry because that's a good place to put per-user add-ins that doesn't require administrator privileges to write to.  Once you have unzipped the files to a directory, double-click the Supersymmetry.vsto file.

I signed the manifest using a code signing certificate that chains up to the Baltimore CyberTrust Root CA.

Publisher Has Been Verified

You may or may not have the certificate chain in your trusted CAs store.  If you would rather compile the code yourself, send me an email and I will just send you the source code.  The source code is so stupid-simple that I don't feel it deserves a Github repo.  Getting Visual Studio set up just right and figuring out the idiosyncrasies of "ClickOnce" deployment was way more involved than actually writing the code.

Uninstallation

Just go to "Programs and Features" in the Control Panel and click Supersymmetry from the list and click Uninstall.

Limitations

When you click the Send button on an email, the add-in currently scans the entire previous thread embedded with the message, not just the part that you just typed.  That means that the add-in will catch quotation mark and parentheses mistakes that other people made earlier on in the email thread, in addition to your own.  When I think of the best way to filter out these older original messages, I will add that to version 1.1.

Update Oct. 4th, 2014: You want the updated version of this addin, v1.1.3.17!

Removing Stale Remote Desktop Licensing Service Connection Points From Active Directory

I was doing some work with Remote Desktop Services today, and wanted to share a quick script I used that can keep your Remote Desktop license server service connection points tidy.

If you use Remote Desktop Services, formerly known as Terminal Services, then you have likely needed to deal with license servers and CALs, etc.  If you have a large and/or mature environment, chances are Remote Desktop License servers have come and gone in your environment.  The license servers get upgraded, or migrated, or retired, etc.  In any case, when you remove the Remote Desktop Licensing role from a server, it does not remove the corresponding "service connection point" from Active Directory.  Service Connection Points are simply objects in Active Directory that clients can use to auto-discover services throughout the domain, such as Exchange, SharePoint, Rights Management Services... and Remote Desktop Licensing servers.  When configuring a Remote Desktop Session Host, you might notice that when you go to point the session host to a license server, the list of available license servers is automatically populated from the SCP objects in Active Directory, and will contain stale/defunct license servers if the SCPs were never cleaned up.

So, how do we clean them up?  Simple:

$RDPSCPs = Get-ADObject -Properties * -Filter {(objectClass -EQ 'serviceConnectionPoint') -AND (Name -EQ 'TermServLicensing')}
Foreach ($SCP In $RDPSCPs)
{
    If ($(Get-ADComputer $SCP.serviceDNSName.Split('.')[0] -Properties LastLogonDate).LastLogonDate -LT (Get-Date).AddDays(-180))
    {
        Write-Warning "Deleting Remote Desktop Licensing Service Connection Point for the stale server: $($SCP.serviceDNSName.Split('.')[0])."
        # Add the -Confirm:$False parameter to the line below if you do not want to be prompted for confirmation.
        Remove-ADObject $SCP.ObjectGUID
    }
}

We simply find all the objects in Active Directory that are of the object class "serviceConnectionPoint" and with a name of "TermServLicensing."  That's important because there are many other types of SCPs other than just RDP License servers.  Then for each one of the SCPs found, we see if the server it refers to has not logged on to the domain in over 180 days.  If it has not, then we delete the SCP.

NTHashTickler_C v1.0

Last time, I unveiled my multithreaded brute force NTLM hash cracker written in C#.  However, I was not very happy with the performance... specifically the scalability... it has some pretty massive concurrency issues that are apparent when executing on a machine with lots of processors.  For instance, the program barely ran any faster on a machine with 8 CPUs than it did on a machine with 4 relatively slower CPUs.  There is obviously a big threading issue there causing a problem with diminishing returns. 

Now don't get me wrong - I love C#, but I had a hunch that if I wanted to be serious about performance I needed to go native.  So I spent about a day porting it to standard C, and now both single-threaded and mult-threaded performance is about seven times better than in C#.  Again, I'm not bashing C#, it's just that different types of problems call for different types of tools.  And like I said last time, I realize there already several other apps out there that do this, but I've taught myself a lot of C doing this, which makes it worth it to me.

As usual, you can view the source on Github if you're interested.


NTHashTickler

And to go along with my Get-MD4Hash Powershell cmdlet that I wrote last time, here is a multithreaded brute-force NT hash cracker written in C#.  I call it NTHashTickler:

https://github.com/ryanries/NTHashTickler


First of all, I realize that there are already many tools out there that already do this exact same thing - and they do a much better job at it that I have been able to do so far.  However, I wrote this tool as a learning experience for myself to exercise some basic speed and multithreading concepts in C#.  How many threads it will use equals the number of logical processors in your machine.  It uses Microsoft's native BCrypt implementation of the MD4 hashing algorithm.  I use the .NET framework's RandomNumberGenerator class to get my randomness, which is a lot slower than the basic Random class, but I sacrificed speed in this instance for better random numbers. I use memcpy straight out of the C runtime for comparing byte arrays, which I do not think can be beat in terms of speed.  However, I'm sure that there are still some massive opportunities for improvements both in terms of speed and in coming up with a more clever algorithm for generating random plain-texts other than just dumb randomness.