vendredi 2 janvier 2015

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
[string] $SrcTableNoSchema="#listeningport" # table where listening ports are stored without schema use for SMO objects

)

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




Aucun commentaire:

Enregistrer un commentaire