Sql-server – What permissions does the SQL Agent’s user need to execute Powershell scripts

permissionspowershellsql-server-agent

I'm trying to create a job with a Powershell step. To recreate this:

  1. create new job, give it a name
  2. add a new job step, name it, type powershell, run as the SQL Agent, script: $test="test"
  3. set the target as the local server.
  4. save and execute it.

When I run it, it fails with:

Executed as user: DOM\ServiceAcct. 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: 'Security error. '. Process Exit Code -1. The step failed.

The trouble is, that's not a line in my script.

From what I've read, it has to do with the SQLPS provider – I suspect it's executing a wrapper script first. Which is fine, but it sounds like the service account is not permitted to run this line?

I did log into the machine as this account and Get-ExecutionPolicy -List shows:

        Scope ExecutionPolicy
        ----- ---------------
MachinePolicy    Unrestricted
   UserPolicy       Undefined
      Process       Undefined
  CurrentUser       Undefined
 LocalMachine    RemoteSigned

I can set the policy to remotesigned, but it doesn't persist – the next powershell window I open is reverted.

Anyone have any tips? Oh, this is SQL 2012, I don't know if that matters though.

Additionally:
I found an answer to a similar question that suggests editing the registry. However, my HKLM\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps110\ExecutionPolicy value was already RemoteSigned. I set it to Unrestricted and my test works, but that doesn't seem like best practice. I'd expect a simple script to run by default.

Best Answer

Don't use the PowerShell job step type. It doesn't behave the same as "regular" PowerShell, it's stuck at a specific (old) version of PowerShell, and it's got a host of other limitations. TL;DR: You won't be happy with the results.

Instead, use a CmdExec job step and have it execute powershell.exe. This will let you run the latest PowerShell, load any module, and have total flexibility in how things execute. Full details, including using a Proxy, are available at https://dbatools.io/agent/

Related Question