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
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14875 Visits: 14396
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
Laerte Poltronieri...
Laerte Poltronieri Junior-367636
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 836
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 !!!
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14875 Visits: 14396
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
Laerte Poltronieri...
Laerte Poltronieri Junior-367636
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 836
Great info man !!! Thanks Smile

$hell your Experience !!!
Rich James
Rich James
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 182
I'll peer more down there.. I'd run straight into security issues on the PnP stack!
Rich James
Rich James
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 182
OPC.Three..
The difference I see between your setup and mine from this is that I'm using a Domain account, rather than a local machine account.
Do you get the same behaviour with a Local account as with a Domain one?

I've set the perms on the root of the WMI tree, and so far nada. All the changes in the scripts that have been linked to are stages that I've followed to date, but it's still not happy. If it's not repeatable elsewhere with a domain account as a proxy (2008 functional level), then I'll spawn more text boxen here and see if there's a flaw in domain or local config.

But, again, perplexed on the running on this; the SQL Server Agent is running as a domain account, and the proxy is a domain account.

The last error I got from this was the system telling me that it wasn't happy with the way that the PnP subsystem was being accessed, which is a remote symptom of 2003+ trying to access as an unprivileged user.
I don't get this when running as the SQL Agent account itself, which has less privileges generally now than the proxy account..
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14875 Visits: 14396
Rich James (8/24/2012)
OPC.Three..
The difference I see between your setup and mine from this is that I'm using a Domain account, rather than a local machine account.
Do you get the same behaviour with a Local account as with a Domain one?

That is a valid point so I just tried this with a Domain Account and it did work. Here is what I did:

- Drop the test Agent Job
- Drop the test Proxy
- Drop the test Credential
- Create the Credential with the Domain Account (only has access to server based on MyServer\Users Local Group containing MyDomain\Domain Users Domain Group)
- Create Proxy using Credential
- Create Agent Job with PowerShell Step that uses Proxy
- Ran the job -> step output contained 'Access Denied'
- Granted WMI Control Remote Enable permission on ROOT\CIMV2 namespace to my domain user
- Ran the job -> step output contained volume info as expected

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Rich James
Rich James
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 182
Thanks OPC.Three.. That points to the problem being local to us.. Much appreciated you spending the time to replicate this and provide proof that it should work..
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14875 Visits: 14396
No problem. Happy to help. I am learning from this too.

Maybe you could try it with a local login just to see if you can isolate it to being a domain login issue. I am no expert on Domain Policy but maybe it affects a domain login different than a local one.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Rich James
Rich James
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 182
Hi OPC.. I've tried the code from your earlier posting. While it registers a success (in the SQL Server Agent result), I added my usual logging to the process (always send to a text file), and got the following text within the log file:

The job script encountered the following errors. These errors did not stop the script:
A job step received an error at line 1 in a PowerShell script. The corresponding line is '$Volumes = get-WMIObject WIN32_Volume |where-object {$_.FreeSpace -ne $null};'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Access denied

Do you get a clean process log file too?
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