InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 23:32:31 GMT20http://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspxSorry for the delay. This one was covered by new emails in my inbox and I am just getting to it. I did not try piping the output to a log file however the 'step output' captured in the job history showed the expected output. Essentially I captured the wmi object to a variable same as what you have then I simply dumped the variable on the next line as:$VolumesWed, 05 Sep 2012 22:05:19 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspxHi 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?Mon, 03 Sep 2012 09:16:37 GMTRich Jameshttp://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspxNo 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.Sun, 26 Aug 2012 08:53:33 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspxThanks 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..Fri, 24 Aug 2012 22:47:54 GMTRich Jameshttp://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspx[quote][b]Rich James (8/24/2012)[/b][hr]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?[/quote]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 [i]MyServer\Users[/i] Local Group containing [i]MyDomain\Domain Users[/i] 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 [i]Remote Enable[/i] permission on ROOT\CIMV2 namespace to my domain user- Ran the job -> step output contained volume info as expectedFri, 24 Aug 2012 10:14:27 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspxOPC.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 [i]less[/i] privileges generally now than the proxy account..Fri, 24 Aug 2012 09:40:51 GMTRich Jameshttp://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspxI'll peer more down there.. I'd run straight into security issues on the PnP stack!Fri, 24 Aug 2012 09:21:27 GMTRich Jameshttp://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspxGreat info man !!! Thanks :)Thu, 23 Aug 2012 11:23:09 GMTLaerte Poltronieri Junior-367636http://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspxTo 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 [i]Remote Enable[/i].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 [i]Remote Enable[/i].Thu, 23 Aug 2012 11:17:27 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspxHumm 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 ?Thu, 23 Aug 2012 11:02:56 GMTLaerte Poltronieri Junior-367636http://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspxLaerte, 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 [i]Remote Enable[/i] on the ROOT\CIMV2 namespace.Artifacts:[code="sql"]-------------------------------------------------------------- create credentialUSE [master]GOCREATE CREDENTIAL [MyAuthenticatedUser] WITH IDENTITY = N'MyComputer\MyAuthenticatedUser', SECRET = N'mypassword'GO-------------------------------------------------------------- create proxyUSE [msdb]GOEXEC msdb.dbo.sp_add_proxy @proxy_name = N'MyAuthenticatedUser', @credential_name = N'MyAuthenticatedUser', @enabled = 1GOEXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name = N'MyAuthenticatedUser', @subsystem_id = 12GO-------------------------------------------------------------- create jobUSE [msdb]GOBEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0IF 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 ENDDECLARE @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 OUTPUTIF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollbackEXEC @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 QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback:IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:GO[/code]Thu, 23 Aug 2012 10:54:48 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspxHey, in fact I was just saying about the WMI permissions and to be honest I dont read all the posts hehehe.Sorry :)Thu, 23 Aug 2012 02:27:42 GMTLaerte Poltronieri Junior-367636http://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspxHi 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!Thu, 23 Aug 2012 02:18:31 GMTRich Jameshttp://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspxHave you tried ?[url=http://vniklas.djungeln.se/2012/08/22/set-up-non-admin-account-to-access-wmi-and-performance-data-remotely-with-powershell/]http://vniklas.djungeln.se/2012/08/22/set-up-non-admin-account-to-access-wmi-and-performance-data-remotely-with-powershell/[/url]Thu, 23 Aug 2012 01:09:37 GMTLaerte Poltronieri Junior-367636http://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspxYou'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?Wed, 22 Aug 2012 11:23:42 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspxThanks 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).Wed, 22 Aug 2012 11:18:05 GMTRich Jameshttp://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspxI haven't gotten around to moving WMI calls to a non-admin account yet, but what I've been looking at in preparation:[url=http://technet.microsoft.com/en-us/library/cc787533%28v=ws.10%29.aspx]http://technet.microsoft.com/en-us/library/cc787533%28v=ws.10%29.aspx[/url][url=http://www.poweradmin.com/help/enableWMI.aspx]http://www.poweradmin.com/help/enableWMI.aspx[/url]Wed, 22 Aug 2012 11:03:13 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspxA 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: 0x0Process Information: Process ID: 0x208 Process Name: C:\Windows\System32\services.exeAccess 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: 0This 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.Wed, 22 Aug 2012 08:52:47 GMTRich Jameshttp://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspxThanks 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..Wed, 22 Aug 2012 03:24:54 GMTRich Jameshttp://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspxI 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: [u][url=http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=177597]running powershell scripts from Job agent (sqlteam.com Forums)[/url][/u]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 successfullyTue, 21 Aug 2012 13:55:29 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1347664-1351-1.aspxIt 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,RichTue, 21 Aug 2012 03:50:58 GMTRich James