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