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

作為大型 SSIS 部署的一部分，我有兩個 SSIS 包在夜間執行（通過 SQL Server 代理），沒有任何問題。一切都使用 Windows 身份驗證，並且計劃的作業由系統管理員（好吧，我）擁有並作為SQL Server 代理服務帳戶執行。

因此，數據基本上是source system ~> transit db ~> staging ~> NDS一夜之間的。

我關心的兩個 SSIS 包分別處理特定數據集的transit db ~> staging和部分。staging ~> NDS

域使用者（非系統管理員）在 中執行某些操作source system，並將感興趣的數據推送到 中transit db，因此我需要一種在工作時間獲取更新數據的方法來更新NDS：決定此人觸發的最簡單方法該 ETL 是通過點擊啟用宏的 Excel 工作簿中的按鈕，該工作簿通過 ODBC（使用 Windows 身份驗證）連接到 SQL Server 並執行儲存過程。

儲存過程如下所示：

create procedure dbo.UpdateMaterialInventory
as
begin
   execute msdb.dbo.UpdateMaterialInventory;
end

中的“姐妹”儲存過程

$$ msdb $$看起來像這樣：

create procedure dbo.UpdateMaterialInventory
with execute as 'SqlAgentProxy'
as
begin
   execute msdb.dbo.sp_start_job N'NDS-ManualMaterialInventory';
end

這

$$ SqlAgentProxy $$user 是我在其中創建的 Windows 使用者$$ msdb $$關閉域使用者的登錄，我授予execute了此UpdateMaterialInventory過程的權限。這避免了必須授予域使用者execute權限msdb.dbo.sp_start_job，這將是過度的。 SQL 代理作業NDS-ManualMaterialInventory歸域使用者所有，有 2 個步驟，每個步驟類型

$$ SQL Server Integration Services Package $$，設置為執行方式 SSISProxy。 SSISProxy是一個 SQL Server 代理代理，它映射到

$$ SQL Server Integration Services Package $$子系統，使用憑證名稱SSISProxyCredentials。域使用者的登錄已添加到代理帳戶 principals。 它們是使用在夜間執行整個 SSIS ETL 的同一域使用者的身份創建的，並且其密碼經過四重檢查SSISProxyCredentials。

現在，如果我執行這個：

execute as login=N'DOMAIN\thatperson'
exec NDS.dbo.UpdateMaterialInventory;
go

我得到這個輸出：

Job 'NDS-ManualMaterialInventory' started successfully.

然而，工作經歷講述了一個不那麼令人鼓舞的故事：

The job failed.  The Job was invoked by User DOMAIN\thatperson.
The last step to run was step 1 (Extract).

第 1 步詳細資訊：

Executed as user: {domain user that runs SSIS ETL overnight}.
Microsoft (R) SQL Server Execute Package Utility  Version 12.0.4100.1 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.
Started:  2:18:50 PM  Failed to execute IS server package because of error 0x80131904.
Server: {server name}, Package path: \SSISDB\Foo\Bar\foobar.dtsx, Environment reference Id: NULL.
Description: Login failed for user '{domain user that runs SSIS ETL overnight}'.
Source: .Net SqlClient Data Provider 
Started:  2:18:50 PM  Finished: 2:18:51 PM  Elapsed:  0.094 seconds.
The package execution failed.
The step failed.

作業失敗，任何地方都沒有記錄任何內容。

如果我將作業所有者更改為我自己，並將步驟的執行更改為SQL Server 代理服務帳戶，作業將執行、成功並將 1,067 行記錄到

$$ Metadata $$.$$ dbo $$.$$ sysssislog $$. 看起來代理/憑據的設置方式有些不對勁。我做錯了哪一部分？

這個問題看起來比實際更複雜。由於您使用的是 SQL 2014，因此您可能會被 2012 年引入的新安全功能所困擾。

唯一真正重要的是：

Server: {server name}, Package path: \SSISDB\Foo\Bar\foobar.dtsx, Environment reference Id: NULL.   
Description: Login failed for user '{domain user that runs SSIS ETL overnight}'.

您的代理使用者的登錄很可能無權訪問 SSISDB 目錄（即使他可能有權訪問 SQL Server）。

您需要將登錄名映射到 SSISDB 使用者並配置對 Integration Services 中 SSISDB 文件夾/項目的訪問權限。

請查看此 MSDN 部落格文章SSIS 目錄訪問控制技巧和SQL 2012 SSIS 目錄權限

實際載入包後，您可能會遇到其他安全上下文問題，但您應該從集成服務本身獲得更好的日誌記錄。

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