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.

Processor Shopping for SQL Server 2012

AMD vs. IntelI almost never talk about SQL Server here, which is a shame, because I think SQL Server is amazing.  If you're planning on deploying SQL Server 2012, and you haven't picked out your hardware yet, then I hope this post finds you in time and helps you make your decision about what processor architecture to choose.  (I hope the graphic doesn't give it away...)  Also, keep in mind the date in which this is written - computer hardware changes rapidly.



You know you pretty much have two choices in CPUs: Intel or AMD.  There are several factors to weigh here: performance, hardware cost, and licensing cost.  So let's break those down and compare:

Performance: Keep in mind that we're designing a SQL Server here.  Different SQL Servers are under different types of workloads, but OLTP (online transaction processing) is one very common type. The TPC (Transaction Processing Performance Council) introduced the TPC-E benchmark in 2007, which simulates an OLTP workload on a SQL server.  What we end up with is a pretty solid method for benchmarking SQL servers of varying hardware configurations running identical workloads.  If you visit the website, it's pretty hard not to notice that the top 10 highest-performing servers and the top 10 best price/performance all belong to Intel processors with no exception.  But just for fun, let's see the numbers:

System Processor TPC-E Sockets Total Cores Score/Core
HP Proliant DL380 G7 Intel Xeon X5690 1284.14 2 12 107.01
IBM System x360 M4 Intel Xeon E5–2690 1863.23 2 16 116.45
HP Proliant DL385 G7 AMD Opteron 6282SE 1232.84 2 32 38.53
HP Proliant DL585 G7 AMD Opteron 6176SE 1400.14 4 48 29.17
IBM System x3850 * 5 Intel Xeon E7–4870 2862.61 4 40 71.57
NEC Express 5800/A1080a Intel Xeon E7–8870 4614.22 8 80 57.68

The trends evident from that table are that AMD prefers more cores per socket, AMD cores tend to perform much worse per core than Intel cores on an OLTP workload, and that crazy numbers of processor cores present with diminishing returns regardless of the manufacturer.  So far things are not looking good for AMD.  AMD can pack more cores on a die, but that just simply does not make up for their gap in single-threaded performance.

Hardware Cost: Let's get right down to some hardware prices. I'm going to price only the processors themselves, not the entire servers, because there are so many customizable options and accessories to choose from when speccing out an entire server and that would take me way longer than what I wanted to spend on this blog post.

Processor CDW.COM Price
Intel Xeon X5690 $1886.99
Intel Xeon E5–2690 $2332.99
AMD Opteron 6282SE $1287.99
AMD Opteron 6176SE $1505.99
Intel Xeon E7–4870 $5698.99
Intel Xeon E7–8870 $7618.99

AMD has a bit of a price advantage here, especially when you start getting to the high-end processors, but it's all for nothing once you take into account the 3rd piece of the puzzle:

Licensing: To be frank, Microsoft SQL Server 2012 Enterprise Edition is very expensive.  SQL used to be licensed on a per-socket basis.  SQL 2012 is now licensed per physical core.  This means "logical" cores such as those created by Intel's Hyperthreading are essentially free in regards to SQL 2012 licensing.  (There is the alternative Server + CAL licensing model as seen with the Business Intelligence Edition, but that's kinda' out of the scope of this article.  Enterprise Edition is where it's at.)  Each physical socket in your SQL server must use a minimum of 4 core licenses, and then you license two cores at a time after that for any additional cores more than 4 you have on your processor.

If you're thinking ahead, you can already tell this is bad news for AMD-based servers aspiring to run SQL 2012.  AMD processors have more cores, which equals higher SQL licensing costs, with lower performance per core to boot.  Microsoft realized this, and so they did AMD a favor by specifically giving most AMD processors a 25% discount on licensing costs.  But even with that discount, the numbers still speak for themselves, and AMD still comes out way behind:

AMD Opteron 6282SE 16 $82,488 2 $164,976 Intel Xeon E5–2690 8 $54,992 2 $109,984 Intel Xeon E5–4650 8 $54,992 4 $219,968 Intel Xeon X7560 8 $54,992 4 $219,968 Intel Xeon E7–4870 10 $68,740 4 $274,960 AMD Opteron 6180SE 12 $61,866 4 $247,464 AMD Opteron 6282SE 16 $82,488 4 $329,952

Processor Cores Per Socket Cost Total Sockets Total License Cost per Server
Intel Xeon X5690 6 $41,244 2 $82,488
AMD Opteron 6282SE 16 $82,488 2 $164,976
Intel Xeon E5–2690 8 $54,992 2 $109,984
Intel Xeon E5–4650 8 $54,992 4 $219,968
Intel Xeon X7560 8 $54,992 4 $219,968
Intel Xeon E7–4870 10 $68,740 4 $274,960
AMD Opteron 6180SE 12 $61,866 4 $247,464
AMD Opteron 6282SE 16 $82,488 4 $329,952

It just got really hard for me to recommend an AMD processor for use in a SQL Server 2012 server under almost any circumstances.  Let's take our Intel Xeon X5690 and our AMD Opteron 6282SE, which both have pretty similar TPC-E benchmark scores... only the AMD costs $82,488 more to license!  This is with AMD's 25% discount!  These are full retail prices of course, but the concept is the same, regardless of your Enterprise Agreement.

So, my fellow IT pros... please do the math before you pull the trigger on that new server, and make sure your $2000 in hardware savings isn't steamrolled by $80,000 of extra licensing costs.

* Citation - these numbers are from the book Professional SQL Server 2012 Internals and Troubleshooting by Bolton, Langford, Berry, et al.

SQL Server - Unable to Generate SSPI Context

The different sorts of authentication mechanisms in play in a Windows network can be pretty complex.  So when someone asked me, "Why do I get a 'Could not generate SSPI context' error when I try to log in to a SQL server?" I knew that there could be several answers to that question.  Go ahead and Google it yourself -- you won't get a definite *This is absolutely your problem* sort of answer.

First I remembered that there was a situation where RSA SecureID tokens (essentially certificates for our purposes) were used for various authentication tasks in the domain, and if one tried to authenticate to a SQL Server with Windows authentication without having one's RSA token plugged in, the "Could not generate SSPI context" error would be generated. Plug the SecureID device into a USB slot, and you'd log in to the SQL server just fine. But I knew that policy was not an issue in this situation...

Then I thought about how services not having their SPNs registered with Active Directory can cause authentication problems.  Specifically, if a SQL Server doesn't have its SPN registered properly in Active Directory, Kerberos authentication cannot be used.  But that still shouldn't prevent you from authenticating whatsoever... it'll just drop you down to NTLM instead of Kerberos.

Also, I was able to perform a logon with Windows auth to the same SQL Server at the same privilege and security level as the user, so I knew it had to be something at their end.

The only other thing I could think of was that something was just wrong with their security token that was confusing their SSPI?  Maybe it was corrupt somehow?  I'm not sure.  So, I recommended that the user run "klist purge" to purge all their domain controller-issued tickets, knowing that they would be refreshed as soon as they requested access to a domain resource...

Bingo.  Problem solved.

Windows 2008 R2 + SQL 2008 R2 + Password Policy = Security Event Log Out of Control

I was asked to troubleshoot an interesting problem today where the Windows Security event log was being "flooded" by one particular sort of event.  By flooded, I mean about 20 duplicate entries logged per second.  The biggest problem with this is that it was making the Security log on that server useless, as the log would fill up within 45 minutes at that rate.  Click to enlarge the screenshot below:

event log ss*Names were changed to protect the guilty*

 The operating system is Windows 2008 R2. The server is a domain member. The server also runs SQL Server 2008 R2.  The server is a cluster node in a failover cluster.  I started Googling and Binging the event ID and description, and I didn't get much at first.

As an aside, I can't believe I just used the word "Binging," and I much prefer Google for almost everything, but if you want to search Technet, MSDN, and other Microsoft sites, the built-in Bing search on those sites actually does tend to produce better results on those sites than a general Google search. For me anyway. Maybe a " xyz" search on Google would do just as well. Anyway... onward:

So the only solid clue I found in my searching was this Technet article. The Windows Password Policy Checking API was being called at a staggering rate, but why?  By whom?  How do I make it stop?  The same SQL service account was being named in the events, so it obviously must have something to do with SQL.  Well, I could turn off the auditing of "Other Account Management Events," either by way of domain GPO or local security policy on that server... but that would only suppress the logging of that behavior.  It does nothing to stop the actual behavior.  Plus I would also lose any other events of that same category on the system.

I also knew that there was an "Enforce Password Policy" option that can be configured on each SQL account.  So I fired up SQL Management Studio on that server and did some testing, and as it turns out, that option was enabled on several accounts, including service accounts that are designed to hit the database rapidly.  It appears that every time an authentication attempt is made by one of those SQL accounts that has that Enforce Policy option checked, the SQL service makes a call to that Windows API to do some password policy checking, and that event is logged.

I tactically identified a few key service accounts that I knew to be very active on that database, and I disabled the "Enforce Password Policy" option on those accounts one by one.  I confirmed that with each account I changed, the rate at which those Security event 4793's were coming in decreased. Until finally, they stopped completely.


That's all for today.  On one final note, I wish we did cool things like this in the United States, especially as a resident of a state that cuts science funding.