What does the US DoD have to do with missing configuration items in SQL Server Configuration Manager?

A couple of months ago, I was having a hard time trying to figure out why -all of a sudden- SQL Server Configuration Manager failed to show some of the x64-specific configuration items that I needed for testing purposes. The environment was basically the same as before (Windows Server 2012 R2, SQL Server 2012), but still, when I ran the tool, the MMC snap-in loaded correctly (SQLServerManager11.msc) and everything was there except for my x64-specific stuff. If only there was an error message to give me a clue, but life is usually a bit more complicated than that.

Figure 1. No 64bit-related settings

Figure 2. This is how it should be

Initially I believed it was an installation / configuration issue, like not having a SQL Server component installed or the presence of some kind of magic registry key to avoid tampering with the settings that I needed. But I couldn’t find anything of interest in these areas.

So, I decided to check all the logs I could (SQL Server logs, event viewer) and found something interesting in the latter (under WMI-Activity), like IWbemServices::ExecQuery failed with exit code 0x80070005 (Access denied), hostprocess wmiprvse.exe, user NETWORK SERVICE.

Hm..that seemed unique enough to start searching for a solution online. With that I spent a couple of hours (days?) but I didn’t really make any progress. Everybody kept talking about access rights issues with regards to the snap-in, but most of these were dead-ends, as these guys actually got error messages inside the snap-in.

It was time to use process monitor to see what mmc.exe was doing at startup. I was mainly looking for file and registry activity but no luck. So I thought, why not try to monitor wmiprvse.exe instead. After all, this process was the one complaining in the first place.

Figure 3. Capturing file & registry activity for mmc.exe and wmiprvse.exe

Soon enough, I identified the problem. The built-in NETWORK SERVICE account had no rights to access C:\Program Files\Microsoft SQL Server\110\Shared\sqlmgmprovider.dll.
 

Figure 4. Figuring out the user principal for wmiprvse.exe

I checked the same thing on a working system and figured that the local Users group (that contained NT AUTHORITY\NETWORK SERVICE) was given read & execute permissions for the abovementioned Shared folder, but obviously this was not the case for the bad system. So I set the rights and voila, everything worked as expected, yaaaay!

Ok, problem solved for now, but I wanted to see the full picture. Who / what caused these access rights to go away? After all, it worked fine in older systems.

I was running out of ideas when eventually a colleague of mine mentioned something about system security hardening. I have to admit I had no clue what security measures were in place for our servers so after talking to a couple of experienced engineers, I discovered a document of ours describing some security aspects.

This was the first time that I heard about Security Technical Implementation Guides (STIGs). To keep the story short, I ended up at the STIGs MasterList website, where I downloaded the guidelines for Microsoft SQL Server 2012 STIG - Ver 1, Rel 15. Opening up the overview .pdf revealed that these guidelines were created by the Defense Information Systems Agency (DISA) for the US Department of Defense and were categorized as unclassified, i.e. open to the public.

Having a look at the instance-specific guidelines I came across the following rule:

Id: SV-53298r8_rule

Title: The OS must limit privileges to change SQL Server software resident within software libraries (including privileged programs).

The rule description is quite massive, so here comes the relevant part:

Locate the ...\Microsoft SQL Server\110\Shared folder

Right-click on the ...\110\Shared folder; click Properties. On the Security tab, verify that at most the following permissions are present:
  Trusted Installer (Full Control)
  CREATOR OWNER (Full Control)
  System (Full Control)
  SQL Server Service SID OR Service Account (Read & Execute) [Notes 1, 2]
  System Administrators (Full Control) [Note 3]
  Local Administrators (Read)
  SQL Server Analysis Services (SSAS) Service SID or Service Account, if SSAS is in use (Read & Execute) [Notes 1, 2]
  SQL Server SQL Agent Service SID OR Service Account, if SQL Server Agent is in use. (Read, Execute, Write) [Notes 1, 2]
  SQL Server FD Launcher Service SID OR Service Account, if full-text indexing is in use. (Read, Write) [Notes 1, 2]
  Users (Read, List Folder Contents, Read & Execute)
  [MsDtsServer110 (Read & Execute) is also permitted, if SSIS/DTS is in use.]
  [NT AUTHORITY\NETWORK SERVICE (Read & Execute) may also be required for SQL Server Configuration Manager to operate.]

As you can see these guys knew very well what they were doing.

So that was it. Due to following some security guidelines our system was “hardened” causing the mmc snap-in to fail silently.