Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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()
share|improve this question
    
How are you trying to run the script via SQL Agent? Is it via PowerShell step or CmdExec step calling PowerShell.exe? –  Shawn Melton Dec 31 '14 at 15:10
    
Did you check the execution policy on the server where SQL Server is running? If so what are they set to? –  Shawn Melton Dec 31 '14 at 15:12
    
Hello @ShawnMelton I am using the PowerShell Step. The execution policy is set to unrestricted. –  Craig Efrein Dec 31 '14 at 15:18
    
I think you might want to verify what information you are trying to pull (and why to me). The CONNECTIONPROPERTY value you are pulling on a server I tested it is on invalid port. It shows a value of -8647 but sys.dm_exec_connections shows a local port value of 56889 for my session, which is correct for my server. –  Shawn Melton Jan 4 at 1:44
    
@ShawnMelton is local_tcp_port better when SQL Server is listening to a dynamic port? Since here we have to use a fixed endpoint, I didn't notice the difference. The reason I'm using CONNECTIONPROPERTY is I ran into some compatibility issues with 2005 and 2008, perhaps of my own doing. –  Craig Efrein Jan 5 at 9:01

1 Answer 1

up vote 3 down vote accepted

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.

enter image description here 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"):

share|improve this answer
    
Hi Shawn, thanks for the answer. Running the script via Task Scheduler executes without error. The script does execute SQL commands on other distant servers, so perhaps I'm running into permissions issues on those other machines? No idea, but I have found a solution. –  Craig Efrein Jan 2 at 9:18
    
No one can answer that question since you did not provide your script or what it is doing. –  Shawn Melton Jan 2 at 14:47
    
Hello Shawn, I added in the script to my question. –  Craig Efrein Jan 3 at 7:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.