Sql-server – 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 of table that stores instances
      [string] $UserName="remoteuser",              # username to connect to each instance
      [string] $Password="remotepassword",      # password to connect to each instance
      [string] $SrcDatabase="tempdb",               # database where listening ports are stored
      [string] $SrcTable="#listeningport"           # table where listening ports are stored


 )

 # load in the SQL Server Powershell Module
 [System.Reflection.Assembly]::LoadWithPartialName( `
  "Microsoft.SqlServer.Smo");


 # log file function
 $logfile = "c:\temp\get_server_ports_$(get-date -format `"yyyy_MM_ddtt`").txt"
 # initalize log file
 $logfile | out-file -Filepath $logfile 

  function log($string, $color)
{
   if ($Color -eq $null) {$color = "white"}
   write-host $string -foregroundcolor $color
   $string | out-file -Filepath $logfile -append
}

# CMS Server connection 
$CMSServerConnectionString = "Data Source=$CMSServer;Initial Catalog=$CMSDatabase;User Id=$CMSUser;PWD=$CMSPassword;"
$CMSServerConnection = new-object system.data.SqlClient.SqlConnection($CMSServerConnectionString);
$CMSServerConnection.Open() 

# create SMO objects so that tables can be created and dropped
$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($CMSServerConnection)
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item($CMSDatabase)

# drop and recreate the serverlist Table on the CMS server
$tb = $db.Tables[$CMSTableNoSchema]
IF ($tb)
    {$tb.Drop()}

# Create the serverlist Table on the cms server
$tb = new-object Microsoft.SqlServer.Management.Smo.Table($db, $CMSTableNoSchema)
$col1 = new-object Microsoft.SqlServer.Management.Smo.Column($tb, "server_name", [Microsoft.SqlServer.Management.Smo.DataType]::NChar(255))
$col2 = new-object Microsoft.SqlServer.Management.Smo.Column($tb, "server_port", [Microsoft.SqlServer.Management.Smo.DataType]::Int)
$tb.Columns.Add($col1)
$tb.Columns.Add($col2)
$tb.Create()

# collect the list of servers
$cmd4 = new-object System.Data.SQLClient.SQLCommand
$cmd4.CommandText = "
    insert into msdb.dbo.serverlist (server_name, server_port)
    select server_name, 1 from msdb.dbo.sysmanagement_shared_registered_servers_internal
"
$cmd4.Connection = $CMSServerConnection
$rowsInserted = $cmd4.ExecuteNonQuery()


# Create a Dataset to hold the DataTable from server_list
$dataSet = new-object "System.Data.DataSet" "ServerListDataSet"
$query = "SET NOCOUNT ON;"
$query = $query + "SELECT server_name "
$query = $query + "FROM   $CMSDatabase.$CMSTable where server_name not in(
    select server_name from $CMSDatabase.dbo.excludeServerList 
  )"

# Create a DataAdapter which you'll use to populate the DataSet with the results
$dataAdapter = new-object "System.Data.SqlClient.SqlDataAdapter" ($query, $CMSServerConnection)
$dataAdapter.Fill($dataSet) | Out-Null

$dataTable = new-object "System.Data.DataTable" "ServerList"
$dataTable = $dataSet.Tables[0]


# for each server
$dataTable | FOREACH-OBJECT {
 Try 
    {   #write-host "server_name: " $_.server_name
        log "server_name : $ServerBConnectionString" yellow
        $ServerBConnectionString = "Data Source="+$_.server_name+";Initial Catalog=$SrcDatabase;User Id=$UserName;PWD=$Password" 
        #write-host "ServerBConnection: " $ServerBConnectionString
        $ServerBConnection = new-object system.data.SqlClient.SqlConnection($ServerBConnectionString);
        $ServerBConnection.Open()

        # create SMO objects so that tables can be created and dropped
        $srv = new-Object Microsoft.SqlServer.Management.Smo.Server($ServerBConnection)
        $db = New-Object Microsoft.SqlServer.Management.Smo.Database
        $db = $srv.Databases.Item($SrcDatabase)

        # collect port number from server
        $cmd3 = new-object System.Data.SQLClient.SQLCommand
        $cmd3.CommandText = "
            SELECT
            @@SERVERNAME as servername,
            cast(CONNECTIONPROPERTY('local_tcp_port') as int) AS port
            INTO $SrcTable
        "
        $cmd3.Connection = $ServerBConnection
        $rowsInserted = $cmd3.ExecuteNonQuery()


        # get port number from table
        $cmd2 = new-object System.Data.SQLClient.SQLCommand
        $cmd2.CommandText = "SELECT port FROM $SrcTable"
        $cmd2.Connection = $ServerBConnection
        $port = [Int32]$cmd2.ExecuteScalar()

        #write-host "port: " $port
        log "port:  $port" yellow

        # update cms table
          $cmd = new-object System.Data.SQLClient.SQLCommand
          $cmd.CommandText = "UPDATE $CMSDatabase.$CMSTable SET server_port = $port WHERE server_name = '"+$_.server_name+"'"
          #write-host "success: " $cmd.CommandText
          $cmd.Connection = $CMSServerConnection
          $rowsUpdated = $cmd.ExecuteNonQuery()

        log "success:  $_.server_name" green        
        #write-host "success: " $_.server_name
        $ServerBConnection.Close()

    } Catch [System.Exception] 
  { 
    $ex = $_.Exception 
    #write-host "failure: " $ex.Message " on server " $_.server_name
    log "failure: $ex.Message on server $_.server_name" red 
    #Write-Host $ex.Message 
  } 
  Finally 
  { 
    #write-host "server_name: " $_.server_name
  } 


}

$CMSServerConnection.Close()