August 21, 2012 at 3:50 am
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
August 21, 2012 at 1:55 pm
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
August 22, 2012 at 3:24 am
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..
August 22, 2012 at 8:52 am
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.
August 22, 2012 at 11:03 am
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
August 22, 2012 at 11:18 am
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).
August 22, 2012 at 11:23 am
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?
August 23, 2012 at 1:09 am
August 23, 2012 at 2:18 am
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!
August 23, 2012 at 2:27 am
August 23, 2012 at 10:54 am
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
August 23, 2012 at 11:02 am
August 23, 2012 at 11:17 am
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
August 23, 2012 at 11:23 am
August 24, 2012 at 9:21 am
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