Execution-Policy Error running Powershell Script in SQL Server Agent

Question

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()

Answer 1

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"):

Answer 2

I've suffered this issue too with specifically the built-in sql server job syspolicy_purge_history The problem is with SQLPS in SQL Server 2012 and above it seems. I've found 2008 R2 does not suffer the same issue.
SQL Server 2012 SQLPS was made more secure by adding in an explicit setting of the process execution policy for SQLPS to be remotesigned. The problem is, that the execution policy scope for Powershell is a hierarchy & performing a set-ExecutionPolicy with a more restrictive policy at a lower scope (e.g. process) will raise an error like the following:

so your options are:

• Make the SQLPS execution policy found in the registry at HKLM:SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps110\ExecutionPolicy match the LocalMachine, or if a GPO is configured the same as the MachinePolicy.
• Set your LocalMachine policy to be Bypass, in which case the error above goes away.

For me the former registry change is easier than going through the hassle of convincing the domain administrator to setup a different machine policy just for my servers.