Home Forums Programming Powershell Get-WMIObject Win32_Volume fails on proxy account from Agent Job RE: Get-WMIObject Win32_Volume fails on proxy account from Agent Job

  • 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