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?

Community
  • 1
Drew Lanclos
  • 188
  • 1
  • 11

1 Answers1

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"
taborda
  • 11
  • 1
  • 4
  • 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 01: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 03:00