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 Answer 1

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"
3
  • 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. Feb 27, 2017 at 1:29
  • But if you run the powershell script using CmdExec does it work?
    – taborda
    Feb 27, 2017 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. Feb 28, 2017 at 3:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .