SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get-WMIObject Win32_Volume fails on proxy account from Agent Job


Get-WMIObject Win32_Volume fails on proxy account from Agent Job

Author
Message
Rich James
Rich James
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 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
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19599 Visits: 14398
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
Rich James
Rich James
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 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. Smile

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..
Rich James
Rich James
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 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.
Nadrek
Nadrek
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2495 Visits: 2733
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
Rich James
Rich James
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 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.. Smile Very useful addition.. Smile

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).
Nadrek
Nadrek
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2495 Visits: 2733
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?
Laerte Poltronieri...
Laerte Poltronieri Junior-367636
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 836
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 !!!
Rich James
Rich James
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 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!
Laerte Poltronieri...
Laerte Poltronieri Junior-367636
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 836
Hey, in fact I was just saying about the WMI permissions and to be honest I dont read all the posts hehehe.Sorry Smile

$hell your Experience !!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search