I'm trying to create a job with a Powershell step. To recreate this:
- create new job, give it a name
- add a new job step, name it, type powershell, run as the SQL Agent, script:
- set the target as the local server.
- 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.
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.