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 Thursday, August 23, 2012 10:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
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
Post #1349258
Posted Thursday, August 23, 2012 11:02 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 10:16 PM
Points: 158, Visits: 774
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 !!!
Post #1349260
Posted Thursday, August 23, 2012 11:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
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
Post #1349265
Posted Thursday, August 23, 2012 11:23 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 10:16 PM
Points: 158, Visits: 774
Great info man !!! Thanks :)

$hell your Experience !!!
Post #1349268
Posted Friday, August 24, 2012 9:21 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
I'll peer more down there.. I'd run straight into security issues on the PnP stack!
Post #1349750
Posted Friday, August 24, 2012 9:40 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
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..
Post #1349768
Posted Friday, August 24, 2012 10:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
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
Post #1349798
Posted Friday, August 24, 2012 10:47 PM
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 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..
Post #1349999
Posted Sunday, August 26, 2012 8:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
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
Post #1350116
Posted Monday, September 3, 2012 9:16 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 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?
Post #1353581
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse