Sql-Server

在 SQL Server 代理中執行 Powershell 腳本的執行策略錯誤

  • March 22, 2016

2014 年使用我的 AD 帳戶通過憑據從 SQL Server 代理執行 powershell 腳本。我收到以下錯誤。

作業步驟在 PowerShell 腳本的第 1 行收到錯誤。對應的行是“set-executionpolicy RemoteSigned -scope process -Force”。更正腳本並重新安排作業。PowerShell 返回的錯誤資訊是：‘Security error.

我在Google上的搜尋，沒有發現任何有用的東西。我可以在我的工作站上通過 SSMS 從 Powershell 控制台執行腳本，而不會出現任何問題。

執行策略設置為無限制

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

錯誤輸出中提到的行必須由 SQL Server 自動添加，因為RemoteSigned -scope process -Force它不在程式碼中的任何位置。

除了使用 AD 帳戶執行作業之外，我還需要在 SQL Server 代理中設置什麼嗎？

這是來自的powershell行msdb.dbo.syssubsystems

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

更新

這是版本

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

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

2015 年 1 月 3 日更新

此腳本基於中央管理伺服器的註冊伺服器創建表 serverlist。然後它連接到每個伺服器並辨識它正在偵聽的埠。

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

您收到的錯誤實際上已在連接項中註明，但 Microsoft 將其顯示為closed won't fix. 此連接項中缺少的事實是子系統SQLPS是通過系統資料庫項設置的。我不知道何時何地實際設置了這一點。

此系統資料庫項位於下面的路徑中，並且在我的本地框中設置為RemoteSigned. 現在我通常不建議更改系統資料庫項，但您可以嘗試將其更改為RemoteSigned，您可能會發現您的腳本將無錯誤地執行。可能需要重啟 SQL Agent 服務，不知道。

在此處輸入圖像描述 HKLM\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps120

現在，通過使用，Unrestricted您實際上可以使腳本在執行 PowerShell 腳本時收到提示。這可能是實際產生錯誤的原因，因為 SQL 代理無法響應提示或不知道如何處理它。確實沒有理由使用該策略設置，因為該策略RemoteSigned足以允許您在伺服器上編寫和設置的腳本在沒有提示的情況下執行。

我會，除非您深入了解返回的完整錯誤，否則它可能包含類似於以下消息的文本。這是您在將執行策略設置為時收到的提示Unrestricted：

安全警告

僅執行您信任的腳本。雖然來自 Internet 的腳本很有用，但此腳本可能會損害您的電腦。你想跑嗎

$$ D $$別跑 $$ R $$執行一次 $$ S $$暫停$$ ? $$幫助（預設為“D”）：

引用自：https://dba.stackexchange.com/questions/87329

相關問答

Sql-Server

SQL Server 2014 SQL Job CmdExec 和訪問被拒絕

  • June 15, 2018
檢視問答
Sql-Server

SQL Server 2014 虛擬帳戶的文件權限

  • February 28, 2016
檢視問答
Sql-Server

從非系統管理員域使用者擁有的 SQL 代理作業執行 SSIS 包

  • November 15, 2015
檢視問答
Sql-Server

SET IDENTITY_INSERT 的權限是否低於 db_ddladmin？

  • April 3, 2022
檢視問答
Sql-Server

恢復 SA 密碼 - PSTools - 不工作

  • November 8, 2021
檢視問答
Sql-Server

授予對 SQLSERVERAGENT 虛擬帳戶的文件系統訪問權限

  • April 19, 2021
檢視問答