I started to ask this on StackExchange DBA but decided it was probably going to be more of a Windows Server security type of question.
SQL Server 2016 SP1+CU on Server 2012 R2.
I'm trying to run a PowerShell step in a SQL Agent job using a proxy user, and I'm running into an issue with SQL trying to do housekeeping before executing any code.
So, the proxy user is included in the SQL Agent PowerShell subsystem. I can make a sample job with just one step, to run "Get-Date". The job errors out:
Executed as user: Domain\ProxyUser. A job step received an error at line 1 in a PowerShell script. The corresponding line is 'set-executionpolicy RemoteSigned -scope process -Force'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Access denied '. Process Exit Code -1. The step failed.
MachinePolicy, UserPolicy, and LocalMachine are all set at RemoteSigned, so it's not like there's a scoping problem, and that would produce a different error anyway.
If I put the proxy user in local admins on the machine, the problem goes away and the script runs normally. I see this access in the Windows Security logs on the system:
Object: Object Server: Security Object Type: File Object Name: \Device\ConDrv Object Handle: 0x4 Process Information: Process ID: 0x6350 Process Name: C:\Windows\System32\conhost.exe Requested Operation: Desired Access: DELETE READ_CONTROL WRITE_DAC WRITE_OWNER SYNCHRONIZE ReadData (or ListDirectory) WriteData (or AddFile) AppendData (or AddSubdirectory or CreatePipeInstance) ReadEA WriteEA Execute/Traverse DeleteChild ReadAttributes WriteAttributes Privileges: SeTakeOwnershipPrivilege
It looks like it's basically the same problem that @MaddHatter had four years ago at this link:
Is the only choice to put this user in local admins? Seems like a rather hamfisted approach to the problem. How can I best tailor the permissions for these proxy users so that the job can run?