Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Get-WMIObject Win32_Volume fails on proxy account from Agent Job Expand / Collapse
Author
Message
Posted Tuesday, August 21, 2012 3:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 9:21 AM
Points: 100, Visits: 182
It may just be a case of me missing something, but I get unexpected behaviour when running a job as a proxy from the SQL Agent. The job itself is a simple one in Powershell that grabs partition usage (and other telemetry, such as BlockSize etc.)

The line it has a problem with is:
$Volumes=get-WMIObject WIN32_Volume |where-object {$_.FreeSpace -ne $null};

The initial test was run on the given test server using the credentials of the proxy account (in a terminal server window, interactive with the desktop). The result was a clean run, all the necessary info displayed.

I then put this as a Powershell Job Step, using that same proxy account. The result was an "Access Denied" message on attempting to query the WMI Object.

Running the same task as the SQL Agent account (which also has non-sysadmin privileges, but more privileges on the database engine than I'd like) results in this working correctly.
Setting the Proxy account as to a local administrator makes the job work correctly under the proxy account. As you can imagine, this is a no-go.

Playing around with various other local group memberships makes no difference.

There are articles about using PowerShell v2 to get the protected connections for WMI, but utilising that route results in the error that this is a local connection, so the settings for a remote query aren't applicable (yes, I ran this as a command via the CMD process, invoking powershell to execute a file as well).
CMD usage with V2 gives the same results as using this via the sqlps embedded mini shell, even with COM execution, and explicit permissions on the WMI stack.

So, currently my options seem to be to give the proxy a local system admin for the windows server, or allow it to run as the SQL Agent service account, neither of which are very secure solutions.. I strongly suspect I'm missing something; anyone able to enlighten me?

Cheers,

Rich
Post #1347664
Posted Tuesday, August 21, 2012 1:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:33 AM
Points: 7,107, Visits: 12,660
I doubt it, but if it makes you feel any better I can recreate your issue on Windows Server 2008 R2 and SQL Server 2008 R2. I created a Local Service Account only belonging to Local Users Group, SQL Credential, Agent Proxy, Agent Job, and received the "Access Denied" messages.

When I run the same command at a PS prompt running under my Service Account it works fine.

I could not figure it myself and found a few posts online with others having the same problem but no solutions. Here is one that I am now watching: running powershell scripts from Job agent (sqlteam.com Forums)

If you find the solution please post back.

EDIT:

PS for me it has something to do with getting to the WMI subsystem because I can use Select-Object and call executables in my PowerShell Step successfully


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1348072
Posted Wednesday, August 22, 2012 3:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 9:21 AM
Points: 100, Visits: 182
Thanks for that.. It does help focus the mind to know it's not something simple I've overlooked, and is repeatable elsewhere. :)

This makes me wonder whether it's an intentional block, or whether there's something going awry..

EDIT: Yep, it's the same for me in that it's WMI access that's the block in this case, the rest of the powershell script runs just fine..
Post #1348275
Posted Wednesday, August 22, 2012 8:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 9:21 AM
Points: 100, Visits: 182
A little bit of further info..

Turning on object fail auditing gives rise to event 4656:


Object:
Object Server: SC Manager
Object Type: SC_MANAGER OBJECT
Object Name: ServicesActive
Handle ID: 0x0

Process Information:
Process ID: 0x208
Process Name: C:\Windows\System32\services.exe

Access Request Information:
Transaction ID: {00000000-0000-0000-0000-000000000000}
Accesses: Connect to service controller
Enumerate services

Access Reasons: -
Access Mask: 0x5
Privileges Used for Access Check: -
Restricted SID Count: 0

This seems to imply that there's a failure to access the service controller to enumerate the object. It's strange that this should occur on a connection from the SQL job (that's meant to be local, and responds as such) and not a SQLPS, or even a SQLPS initiated from the management studio that's supposed to operate in the same context.
I'll kick this around as soon as I get time, unless anyone's come up with an answer they're willing to share. I suspect it's going to mean editing the auth strings for the SCM.
Post #1348470
Posted Wednesday, August 22, 2012 11:03 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:59 AM
Points: 876, Visits: 2,419
I haven't gotten around to moving WMI calls to a non-admin account yet, but what I've been looking at in preparation:
http://technet.microsoft.com/en-us/library/cc787533%28v=ws.10%29.aspx

http://www.poweradmin.com/help/enableWMI.aspx
Post #1348581
Posted Wednesday, August 22, 2012 11:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 9:21 AM
Points: 100, Visits: 182
Thanks Nadrek. I'd actually already discounted that (as that's largely for remote access to WMI, and you'll need Powershell V2, not the in built SQLPS mini shell, due to packet protection issues to get at some of the objects), which was why I was pointing out the remote and local differentiation (the connection of a proxy account seems to be registering as a local connect). I've already got full permissions on the tree given to the proxy account, and it still didn't like running from the job (though is absolutely fine from an interactive PS window). That, though is something I think will be popping up with related questions, which can now all be solved through this thread.. :) Very useful addition.. :)

From what I'm seeing, it's a problem with the way the proxy is being invoked; the only thing I can immediately think of (barring me missing something, which is always an option) is that some authentication token isn't being created correctly, or there's authentication state that's entered by using a proxy, which invalidates some of the privileges on an interactive local connection...

The trace I had put it with the Service Manager denying access... I'll play with those access strings when I get back to the desk (and get some time to poke my nose round in it again).
Post #1348589
Posted Wednesday, August 22, 2012 11:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:59 AM
Points: 876, Visits: 2,419
You're welcome - all of my work is on the remote end, not the local end, but I do hope that the links are helpful to others.

For your specific question, when you stated "Setting the Proxy account as to a local administrator makes the job work correctly under the proxy account" in the OP, I'd assumed that it was likely to be some permissions for the proxy account, since permissions changes to the proxy account appear to change it from failing to succeeding (and back). Perhaps something tucked away in Group Policy?
Post #1348592
Posted Thursday, August 23, 2012 1:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 10:06 PM
Points: 158, Visits: 776
Have you tried ?

http://vniklas.djungeln.se/2012/08/22/set-up-non-admin-account-to-access-wmi-and-performance-data-remotely-with-powershell/


$hell your Experience !!!
Post #1348888
Posted Thursday, August 23, 2012 2:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 9:21 AM
Points: 100, Visits: 182
Hi Laerte,
That, from a quick scan of the code, seems to be an automation of the previous links about allowing remote connections.. Interesting code thought!
Post #1348924
Posted Thursday, August 23, 2012 2:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 10:06 PM
Points: 158, Visits: 776
Hey, in fact I was just saying about the WMI permissions and to be honest I dont read all the posts hehehe.Sorry :)

$hell your Experience !!!
Post #1348932
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse