Tuesday, May 4, 2010

SQL WMI Permission Issue

WMI Permission / SQL WMI

When connecting to remote SQL WMI provider, what kind of security permission should be considered? In fact, it might not be simple as you think. Because there are various layers for the WMI request to go through.

Here is the list to address permission issue:

(1) Firstly, RPC calls should be allowed between the machines. To enable RPC communication, firewall should allow RPC/DCOM port. One way of doing it is:
C> netsh firewall set service remoteadmin

(2) Secondly, since WMI is based on RPC/DCOM, you need to configure DCOM security correctly on remote target machine. Run Dcomcnfg.exe and open [My Computer] Properties page. Go to [COM security] tab and click [Edit Limits] under [Launch and Activation Permission]. Choose the user and allow [Remote Launch] and [Remote Activation]. This will enable remote DCOM calls for the target machine.

(3) Once RPC/DCOM is enabled, it’s now WMI security turn. WMI security is namespace based, which means each WMI namespace has its own security settings. In order to enable WMI security, run WinMgmt.msc on target machine.

C:\Windows\System32> winmgmt.msc

In MMC, rightclick [WMI Control] and select [Properties]. Click [Security] tab and navigate WMI namespace where you want to set permission. Click [Security]

In Security tab, choose the user and allow [Enable Account], [Remote Enable], [Read Security] and optionally [Execute Methods] (if you need to execute WMI method).

Generally the three steps above are good enough to access remote WMI providers. With those settings, you can try WMI call from source machine to remote target machine, say, by using Powershell.

gwmi -namespace "root/Microsoft/SqlServer/ComputerManagement10" -computer RemoteServer -query "select * from ServerSettingsGeneralFlag"

One of interesting issue I recently happen to know is some classes (such as SqlService) in SQL WMI provider (sqlmgmprovider.dll) might need more permission. The case is a domain user tried to remote query SqService class, but strangely the query returns nothing (0 row) even if there exist SQL Services on the machine.

gwmi -namespace "root/Microsoft/SqlServer/ComputerManagement10" -computer RemoteServer -query "select * from SqlService"

Some investigation showed that it’s related to permission issue.

0:004> u .
00000000`49f61817 ff15dbf8fdff    call    qword ptr [svrenumapi100!_imp_OpenSCManagerW (00000000`49f410f8)]
00000000`49f6181d 4889442448      mov     qword ptr [rsp+48h],rax
00000000`49f61822 48837c244800    cmp     qword ptr [rsp+48h],0
00000000`49f61828 7507            jne     svrenumapi100!ServiceItemFactory::GetServicesOnMachine+0x61 (00000000`49f61831)
00000000`49f6182a 33c0            xor     eax,eax
00000000`49f6182c e90d030000      jmp     svrenumapi100!ServiceItemFactory::GetServicesOnMachine+0x36e (00000000`49f61b3e)
00000000`49f61831 c744245000000000 mov     dword ptr [rsp+50h],0
0:004> p
00000000`49f6181d 4889442448      mov     qword ptr [rsp+48h],rax ss:00000000`017fe258=0000000049f5e8f6
0:004> !gle
LastErrorValue: (Win32) 0x5 (5) - Access is denied.
LastStatusValue: (NTSTATUS) 0xc0000100 - Indicates the specified environment variable name was not found in the specified environment block.

SQL WMI provider is using svrenumapi*.dll which tries to open Service Control Manager(SCM) to enumerate SQL instances. Since the domain user for this case did not have enough permission to access SCM of the target machine, the access was denied. This permission issue can be solved if the domain user simply becomes local admin of the remote machine (or if the user has enough permission to enumerate all services including SQL services. That may require sophisticated understanding about serivce control manager security). A little more details explained here.

No comments:

Post a Comment