Sql-server – SQL Agent Powershell Job Failing with Non-Admin Proxy

permissionspowershellsql server

I'm attempting to create a SQL Agent job that will run a PowerShell script that is saved on my system. However, the job fails with the following error:

A job step received an error at line 1 in a PowerShell script. The corresponding line is 'g:\PowerShellShare\Get-LastDBBackups.ps1'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'AuthorizationManager check failed.

If I add the proxy account to local admins, it works, so it appears to be a permissions problem (leaving the account in local admin isn't an option for my environment).

The Windows Security log shows multiple Audit Failures with Event ID 4656 every time I run the job, all of them are from the proxy account attempting to access an object:

Object:
Object Server:      SC Manager
Object Type:        SERVICE OBJECT

A quick sc sdshow scmanager showed that Authenticated Users should have the access to scmanager:

D:(A;;CCLCRPRC;;;AU)(A;;CCLCRPRC;;;IU)(A;;CCLCRPRC;;;SU)(A;;CCLCRPWPRC;;;SY)(A;;KA;;;BA)S:(AU;FA;KA;;;WD)(AU;OIIOFA;GA;;;WD)

Most puzzlingly, if I RUNAS the proxy account on the server and run the script, it runs just fine. It doesn't matter what script I attempt to run via the SQL Agent Job, attempting to execute any script results in the same behavior. I can however run other PowerShell commands, just not execute any scripts.

Hoping someone has run into this before and could point me in the right direction.

UPDATE: It seems the major problem is that when I'm executing from the SQL Agent Job, my proxy account isn't a member of the interactive implicit security group. It looks like that group is granting the proxy whatever permissions it needs to run the job successfully when I test it using RUNAS. I'm not able to track down all the permissions this group allows.

The Audit Failures I was seeing were for the WinHttpAutoProxySvc and CryptSvc services. I used sc sdshow and sc sdset to modify the permissions to those specific services so that the Authenticated Users permissions matched those of the Interactive Users group. The audit failures are now gone, but I still get the same error message in SQL when I execute the job.

Best Answer

Check your execution policy within Powershell; sounds like it might be failing because the default setting is "Restricted", which only allows Powershell to run as an interactive shell, and does not execute scripts at all. See the following link for details:

http://technet.microsoft.com/en-us/library/ee176961.aspx

EDIT: Just realized the above link only describes syntax for "Set-ExecutionPolicy"; this link should provide some additional context also:

http://technet.microsoft.com/en-us/library/ee176949.aspx

Related Topic