Get-WMIObject Win32_Volume fails on proxy account from Agent Job

  • 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

  • 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

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

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

  • 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

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

  • 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?

  • Have you tried ?

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

    $hell your Experience !!![/url]

  • 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!

  • 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 !!![/url]

  • Laerte, the article you linked to had a tip that worked for me. I did not have to grant all the perms noted in the article, specifically I did not need to add the Windows Account my SQL Server Credential is based on to any local Groups or grant all the WMI namespace perms listed.

    All I needed to do was open WMI Control (wmimgmt.msc) and grant my Windows Account Remote Enable on the ROOT\CIMV2 namespace.

    Artifacts:

    ------------------------------------------------------------

    -- create credential

    USE [master]

    GO

    CREATE CREDENTIAL [MyAuthenticatedUser]

    WITH IDENTITY = N'MyComputer\MyAuthenticatedUser', SECRET = N'mypassword'

    GO

    ------------------------------------------------------------

    -- create proxy

    USE [msdb]

    GO

    EXEC msdb.dbo.sp_add_proxy

    @proxy_name = N'MyAuthenticatedUser',

    @credential_name = N'MyAuthenticatedUser',

    @enabled = 1

    GO

    EXEC msdb.dbo.sp_grant_proxy_to_subsystem

    @proxy_name = N'MyAuthenticatedUser',

    @subsystem_id = 12

    GO

    ------------------------------------------------------------

    -- create job

    USE [msdb]

    GO

    BEGIN TRANSACTION

    DECLARE @ReturnCode INT

    SELECT @ReturnCode = 0

    IF NOT EXISTS ( SELECT name

    FROM msdb.dbo.syscategories

    WHERE name = N'[Uncategorized (Local)]'

    AND category_class = 1 )

    BEGIN

    EXEC @ReturnCode = msdb.dbo.sp_add_category

    @class = N'JOB',

    @type = N'LOCAL',

    @name = N'[Uncategorized (Local)]'

    IF (

    @@ERROR <> 0

    OR @ReturnCode <> 0

    )

    GOTO QuitWithRollback

    END

    DECLARE @jobId BINARY(16)

    EXEC @ReturnCode = msdb.dbo.sp_add_job

    @job_name = N'test ps',

    @enabled = 1,

    @notify_level_eventlog = 0,

    @notify_level_email = 0,

    @notify_level_netsend = 0,

    @notify_level_page = 0,

    @delete_level = 0,

    @description = N'No description available.',

    @category_name = N'[Uncategorized (Local)]',

    @owner_login_name = N'sa',

    @job_id = @jobId OUTPUT

    IF (

    @@ERROR <> 0

    OR @ReturnCode <> 0

    )

    GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep

    @job_id = @jobId,

    @step_name = N'test',

    @step_id = 1,

    @cmdexec_success_code = 0,

    @on_success_action = 1,

    @on_success_step_id = 0,

    @on_fail_action = 2,

    @on_fail_step_id = 0,

    @retry_attempts = 0,

    @retry_interval = 0,

    @os_run_priority = 0,

    @subsystem = N'PowerShell',

    @command = N'$Volumes = get-WMIObject WIN32_Volume |where-object {$_.FreeSpace -ne $null};

    $Volumes',

    @database_name = N'master',

    @flags = 0,

    @proxy_name = N'MyAuthenticatedUser'

    IF (

    @@ERROR <> 0

    OR @ReturnCode <> 0

    )

    GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_update_job

    @job_id = @jobId,

    @start_step_id = 1

    IF (

    @@ERROR <> 0

    OR @ReturnCode <> 0

    )

    GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver

    @job_id = @jobId,

    @server_name = N'(local)'

    IF (

    @@ERROR <> 0

    OR @ReturnCode <> 0

    )

    GOTO QuitWithRollback

    COMMIT TRANSACTION

    GOTO EndSave

    QuitWithRollback:

    IF (@@TRANCOUNT > 0)

    ROLLBACK TRANSACTION

    EndSave:

    GO

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Humm Interesting..I had read it but not tried. Let me understand, your proxy account MyAuthenticatedUser was just open WMI Control (wmimgmt.msc) and grant Remote Enable on the ROOT\CIMV2 namespace ?

    And it works ?

    $hell your Experience !!![/url]

  • To be complete, that was what I ended up with. What I actually did:

    - Added all the perms recommended in the article and got a successful run.

    - Removed MyAuthenticatedUser from the two Local Windows Groups and got a successful run.

    - Widdled down permissions on ROOT\CIMV2 until I all that was necessary to get a successful run was Remote Enable.

    Hopefully I did not pollute my environment however I think not. I say this because as I was widdling I was getting some successful runs and some failed runs until I narrowed it down to only needing Remote Enable.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Great info man !!! Thanks 🙂

    $hell your Experience !!![/url]

  • I'll peer more down there.. I'd run straight into security issues on the PnP stack!

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply