What permissions does the SQL Agent's user need to execute Powershell scripts?

Question

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\PowerShell1円\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.

Answer 1

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/

• end-user: Ok, to be clear, your suggestion is to use a CmdExec to launch a separate shell instance, and execute a .ps1 file in that shell. However, I want to contain the contents of that .ps1 script inside the job step. The script itself is unique to the job, and I don't want to have a bunch of files in an arbitrary folder that different jobs find and execute.