Execution-Policy Error running Powershell Script in SQL Server Agent

Running a powershell script from SQL Server Agent in 2014 using my AD account via a credential. I am getting the following error.


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.

My searches on Google, haven't turned up anything useful. I can run the script from the Powershell console via SSMS at my workstation without any issues.

Execution policy is set at unrestricted

PS C:\WINDOWS\system32> Get-ExecutionPolicy
Unrestricted

The line mentioned in the error output must be getting added automatically by SQL Server because RemoteSigned -scope process -Force is not anywhere in the code.

Is there anything else I need to set in SQL Server Agent, aside from my using AD account to run the job?

Here is the powershell row from msdb.dbo.syssubsystems

C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\SQLPS.exe

Update

Here is the version

PS SQLSERVER:\SQL\CD000023\CEF_2014_1> $PSVersionTable.PSVersion

Major  Minor  Build  Revision
-----  -----  -----  --------
2      0      -1     -1

Update 01/03/2015

This script creates a table serverlist based on the registered servers of a central management server. It then connects to each of those servers and identifies the port that its listening on.

```
# connection parameters
Param (
[string] $CMSServer="someuser\someinstance", # CMS server that stores serverlist
[string] $CMSDatabase="msdb", # database where the serverlist is stored
[string] $CMSUser="someuser", # username to connect to the cms server
[string] $CMSPassword="somepassword", # password to connect with the cmsuser
[string] $CMSTable="dbo.serverlist", # name of table that stores instances
[string] $CMSTableNoSchema="serverlist", # name

The error you are receiving was actually noted in a connect item but Microsoft shows it as closed won't fix. What is missed in this connect item is the fact that the subsystem for SQLPS is set via a registry key. When and what actually sets this I do not know.

This registry key is found at the path below and on my local box is set to RemoteSigned. Now I do not generally advise changing registry keys but you can try changing this to RemoteSigned and you will likely find your scripts will run without error. It might require restart of SQL Agent service, don't know.

HKLM\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps120

Now, with using Unrestricted you can actually cause scripts to receive a prompt when executing the PowerShell script. This may be what is actually generating the error because SQL Agent cannot respond to the prompt or does not know how to handle it. There is really no reason to use that policy setting as RemoteSigned is a sufficient policy to allow scripts you wrote and setup on the server to execute without being prompted.

I would except if you dug into the full error being returned it might contain text similar to the below message. This is the prompt you can receive when setting the execution policy to Unrestricted:


Security Warning


Run only scripts that you trust. While scripts from the Internet can
be useful, this script can potentially harm your computer. Do you want
to run


[D] Do not run [R] Run once [S] Suspend [?] Help (default is "D"):

StackExchange Database Administrators Q#87329, answer score: 3