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:
SQL Agent Powershell Job Failing with Non-Admin Proxy
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?