1

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?

1

You can try to see what's going on with the SQLAgent (or maybe powershell) process using the Sysinternals Process Monitor:
https://technet.microsoft.com/en-us/sysinternals/processmonitor.aspx
With this tool you would be able to see where the "Access denied" is comming from.

Another option would be to use a "Operating System (CmdExec)" step instead of powershell and call the powershell script like this:

powershell.exe -File "C:\Path\To\File.ps"
|improve this answer|||||
  • Yeah, the CmdExec option is there but we really are trying to modernize and improve our existing scripting systems. This stuff used to be built on scheduled tasks and a dizzying array of batch files. I'll see what I can glean with Process Monitor, thanks for the tip. – Drew Lanclos Feb 27 '17 at 1:29
  • But if you run the powershell script using CmdExec does it work? – taborda Feb 27 '17 at 10:24
  • Yes. It's not a problem with the script, it's a problem with how SQL is creating the PowerShell session. If I take that part out of the equation and simply have SQL drop me a command shell which I then use to bootstrap PowerShell, the problem is effectively sidestepped. – Drew Lanclos Feb 28 '17 at 3:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.