Whoa, this is overly complicated process for a relatively basic outcome. It must have taken a long time figuring out how to get this working. Thank you for the comprehensive guide 🙂
StealthAUDIT, a best in its class Data Access Governance (DAG) tool utilizes Windows Management Instrumentation (WMI) extensively to gather various pieces of information from the targeted Windows servers. While local WMI querying is straightforward to implement and troubleshoot, remote WMI querying is another story. Setting up remote WMI query security is a pretty daunting task if you are not willing to use an account that is either part of the Domain Administrators group or Local Administrators group.
After I ran into such a situation with one of our customers, I decided to discuss my experience in setting up remote WMI Namespace querying as a standard domain user. For the purposes of this blog, I will walk you through the steps of creating a standard domain user and setting up permissions for the user to query Microsoft SQL Server WMI namespace class on a Windows Server 2012 R2 running Microsoft SQL Server 2016.
WMI is a collection of predefined classes that are logically grouped based on the Common Information Model (CIM). WMI classes are considered system classes and unlike the provider classes, the WMI classes are not declared in a Managed Object Format (MOF) file. WMI helps administrators consolidate the management of devices and applications within a network or domain from systems running on Windows. Besides using WMI to query relevant data from a Windows system, it also supports the following actions:
Step 1.) The first step is to create a standard user in active directory. Using the Active Directory Users and Computers applet, I created a new user called stealthbits_svc and did not assign any additional group memberships other than the Domain Users default group as shown below.
Step 2.) Now, I would like to assign necessary permissions to the stealthbits_svc domain user. This user is not added to any local groups on the Microsoft SQL Server host that I would like to query using WMI Namespace, which is the whole point of this exercise. The specific WMI namespace classes that I am interested in querying are ROOT\Interop and ROOT\Microsoft\SqlServer as shown below. I found a nifty tool called WMI Explorer that allows me to review the WMI classes contained with a namespace. It also allows me to traverse through the methods for each class and since I can log in using different credentials, it allows me to quickly check the permissions for a given user.
Step 3.) The next step is to ensure that some of the pre-requisites are met on the remote Microsoft SQL Server that I will be querying from.
Step 4.) WMI security on the local Microsoft SQL Server is controlled by WMI Control MMC snap-in and DCOM security settings. As a first step, I configured the DCOM security settings to allow remote access by using the Component Services applet (dcomcnfg.exe). Navigate to the My Computer node as shown below and right-click on Properties.
Step 5.) In the My Computer Properties screen, navigate to the COM Security tab. Click on Edit Limits in the Access Permissions section. In the Group or user names: section, click on the Add button and add the domain user created in Step 1. In the Permissions for Everyone section, ensure the Local Access and Remote Access check-boxes are checked to Allow as shown below.
Step 6.) In the My Computer Properties screen, choose the COM Security tab. Click on Edit Limits in the Launch and Activation Permission section. In the Group or user names: section, click on the Add button and add the domain user created in Step 1. In the Permissions for Everyone section, ensure the Local Launch, Remote Launch, Local Activation, and Remote Activation check-boxes are checked to Allow as shown below.
The tell-tale sign of not changing the settings and adding the desired user to the permission list is that you will get 0x80070005 E_ACCESS_DENIED error when a WMI query is executed against the remote server. More information on troubleshooting WMI Access Denied errors can be found here. Assigning the permission outlined in Steps 5 and 6 will resolve the 0x80070005 E_ACCESS_DENIED error.
Step 7.) In the WMI Control snap-in MMC, set the permission to the required namespace(s). Start the WMI Control applet by running wmimgmt.msc command in the Run window.
Step 8.) In WMI Control applet, right-click on WMI Control (Local) node and choose Properties.
Step 9.) In the WMI Control (Local) Properties window, navigate to the required namespace and click on the Security button permission for the required namespace(s) one at a time. In my case, I want to query the Interop and Microsoft namespaces for Microsoft SQL Server related information.
Step 10.) Next, I chose the Interop and Microsoft namespaces and clicked on the Security button to add the user created in Step 1 and assigned the required permissions by checking the appropriate check boxes in the Allow column.
Step 11.) Please note that the permissions assigned in Step 10 are at the namespace level and the subsequent classes that are part of the same namespace will not inherit the permissions set at the namespace level. Inheritance will require setting up the Advanced security setting. Click on the Advanced button to get to the Advanced Security Settings for the Microsoft screen. In the Permissions tab, click on the Add button to all the users created in Step 1 as shown below.
Step 12.) After going through all the steps above and attempting to execute a Powershell script that queries the Microsoft namespace, it returned no results. I have included part of the Powershell script that I used below.
$creds = Get-Credential $hostname = @' <ip address or hostname of the SQL Server> '@.Trim() $results = New-Object System.Collections.ArrayList $e = $null #Get-WmiObject -Authentication $namespaces = Get-WmiObject -Namespace "root\microsoft\SQLServer" -Query "SELECT Name FROM __NAMESPACE WHERE Name LIKE 'ComputerManagement%'" -Impersonation 3 -ComputerName $hostname -Credential $creds | select "Name"
Step 13.) Even after assigning full permissions to the entire Microsoft and Interop namespaces, I was not able to get the results that I was looking for. The next step is modifying the Discretionary Access Control List (DACL) and granting permissions to the user created in Step 1. You can find more information on DACL security here. I got the SID for the user created in Step 1 as follows:
Step 14.) Using the sc.exe (Service Controller) utility, I extracted the permissions for SCMANAGER and MSSQLSERVER services as follows. Redirecting the output to a text file serves as a backup in case I ever need to revert the changes. Then I opened both the text files and added the SID obtained in Step 13.
C:\SC SDSHOW SCMANAGER > SCMANAGER.TXT C:\SC SDSHOW MSSQLSERVER > MSSQLSERVER.TXT
Step 15.) Modify the Security Descriptor Definition Language (SDDL) security settings for SCMANAGER and change it as follows:
Current Setting for SCMANAGER: "D:(A;;CC;;;AU)(A;;CCLCRPRC;;;IU)(A;;CCLCRPRC;;;SU)(A;;CCLCRPWPRC;;;SY)(A;;KA;;;BA)(A;;CC;;;AC)S:(AU;FA;KA;;;WD)(AU;OIIOFA;GA;;;WD)" Modified Settings where I set the SID of the domain user is as follows and set it using sdset option” C:\SC SDSET SCMANAGER "D:(A;;CC;;;AU)(A;;CC;;;S-1-5-21-2248144405-2051730382-3067780714-361)(A;;CCLCRPRC;;;IU)(A;;CCLCRPRC;;;SU)(A;;CCLCRPRC;;;S-1-5-21-2248144405-2051730382-3067780714-361)(A;;CCLCRPWPRC;;;SY) (A;;KA;;;BA)(A;;CC;;;AC)S:(AU;FA;KA;;;WD)(AU;OIIOFA;GA;;;WD)"
Step 16.) Modify the SDDL security setting for MSSQLSERVER service and change it as follows:
Current Setting for MSSQLSERVER: "D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;IU)(A;;CCLCSWLOCRRC;;;SU)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)" Modified Settings where I set the SID of the domain user is as follows and set it using sdset option” C:\SC SDSET MSSQLSERVER "D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;S-1-5-21-2248144405-2051730382-3067780714-3618)(A;;CCLCSWLOCRRC;;;IU)(A;;CCLCSWLOCRRC;;;SU) S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)"
If you prefer to change the permissions for a built-in group rather than a specific user the 2 letter group abbreviation can be specified instead of the user SID in Steps 15 & 16. Details on the SDDL and what the abbreviation means can be found here.
Step 17.) All right, after all this I was able to successfully run my Powershell script and execute a WMI query against a remote Microsoft SQL Server as a standard domain user.
To learn more about how Stealthbits can help with auditing your Microsoft SQL and Azure SQL databases, visit our website: https://www.stealthbits.com/stealthaudit-for-sql-product
Sujith Kumar has over 25 years of professional experience in the IT industry. Sujith has been extensively involved in designing and delivering innovative solutions for the Fortune 500 companies in the United States and across the globe for disaster recovery and high availability preparedness initiatives. Recently after leaving Quest Software/Dell after 19 years of service he was working at Cirro, Inc. focusing on database management and security. His main focus and area of interest is anything data related.
Sujith has a Master of Science in engineering degree from Texas A&M University and a Bachelor of Science in engineering degree from Bangalore University and has published several articles in referred journals and delivered presentations at several events.
Proper data security begins with a strong foundation. Find out what you're standing on with a free deep-dive into the security of your Structured and Unstructured Data, Active Directory, and Windows infrastructure.Read more
Start a Free Stealthbits Trial!
No risk. No obligation.