如何用預存程序以最小權限原則呼叫遠端SSIS

　　最近遇到一個問題，要在ASP網站上呼叫遠端SSIS，以執行轉檔的動作，遠端呼叫SSIS方法有好幾個，我這邊介紹如何用預存程序以最小權限原則呼叫遠端SSIS的方法，因為有安全性的顧慮啊，所以儘可能讓使用的權限愈小愈好囉

權限設到多小呢？我希望只給它執行該預存程序的權限就好，其他讀寫權限都沒有，看看可不可以作到吧

--1.首先請建立一個可以正常執行的作業test1，作業步驟是去執行一個已設計好的SSIS封裝mis99_test，作業的擁有者我先給sa，建立作業的語法如下，建好後，請啟動看看執行是否正常

USE [msdb]

GO

DECLARE @jobId BINARY(16)

EXEC  msdb.dbo.sp_add_job @job_name=N'test1',

      @enabled=1,

      @notify_level_eventlog=0,

      @notify_level_email=2,

      @notify_level_netsend=2,

      @notify_level_page=2,

      @delete_level=0,

      @category_name=N'[Uncategorized (Local)]',

      @owner_login_name=N'sa', @job_id = @jobId OUTPUT

select @jobId

GO

EXEC msdb.dbo.sp_add_jobserver @job_name=N'test1', @server_name = N'SRV999T'

GO

EXEC msdb.dbo.sp_add_jobstep @job_name=N'test1', @step_name=N'test1',

      @step_id=1,

      @cmdexec_success_code=0,

      @on_success_action=1,

      @on_fail_action=2,

      @retry_attempts=0,

      @retry_interval=0,

      @os_run_priority=0, @subsystem=N'SSIS',

      @command=N'/SQL "\mis99_test" /SERVER SRV999T /USER 帳號 /PASSWORD 密碼 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E',

      @database_name=N'master',

      @flags=0

GO

　　建立作業時，請注意一個地方，步驟裡[登入到伺服器]那，建議不要[使用Windows驗證]，請[使用SQL Server驗證]，要不然之後有可能會出現如下奇怪的錯誤

/*

訊息

以下列使用者的身分執行: SRV999T\test。Microsoft (R) SQL Server 執行封裝公用程式 32 位元版本9.00.4035.00  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    已啟動:  下午03:58:14  因為發生錯誤0xC0014062，所以無法載入封裝"\mis99_test"。 描述: LoadFromSQLServer 方法發現OLE DB 錯誤碼0x80040E4D (使用者'SRV999T\test' 的登入失敗。)。發出的SQL 陳述式失敗。  來源:   已啟動:  下午03:58:14  已完成: 下午03:58:14  經過時間:  0.469 秒.  無法載入封裝。.  步驟失敗。

*/

--2.接著建立預存程序[usp_Exectest1]，用途是去啟動第一個步驟建立的那個作業

CREATE PROCEDURE dbo.usp_Exectest1

AS

BEGIN

    SET NOCOUNT ON

    DECLARE @returncode int

    EXEC @returncode=msdb.dbo.sp_start_job 'test1'

    select @returncode  

END
GO

--3.假設網站會用到資料庫為test99，那就建立一個可以登入test99資料庫的帳號user1，權限只給該預存程序的執行權限就好，其他權限不用給

USE master

GO

create login user1 with password ='user1'

GO

use test99

GO

create user user1

GRANT EXECUTE ON dbo.usp_Exectest1 TO user1
GO

　　此時你可先試著用user1登入，執行exec dbo.usp_Exectest1看看，應會得到下列錯誤，因本來就沒有給sp_start_job執行權限，所以出現這錯誤挺正常的

/*

訊息229，層級14，狀態5，程序sp_start_job，行1

結構描述'dbo'，資料庫'msdb'，物件'sp_start_job' 沒有EXECUTE 權限。

*/

　　那問題來了，BOL關於sp_start_job裡有提到，依預設，只有系統管理員(sysadmin) 固定伺服器角色的成員，才能夠執行這個預存程序。其他使用者必須被授與msdb資料庫的下列其中一個 SQL Server Agent 固定資料庫角色。

• SQLAgentUserRole
• SQLAgentReaderRole
• SQLAgentOperatorRole

　　但我只要執行這個作業而已，一旦將user1加到上列角色中，就不符合最小權限原則了，會多一些不必要的權限啊，那要怎麼做呢？其實可以使用簽章來達到更細微的安全設定喔，甚麼是簽章？甚麼是憑證？不懂的可參考使用 EXECUTE AS 擴充資料庫模擬此篇，有較詳盡的解釋

--4.使用簽章的設定方法use marNanya

go

--建立憑證

create certificate agentProxy

    ENCRYPTION BY PASSWORD = 'ahog834ugihGa832hfiwea'

    with subject = 'agentProxy'

   , start_date='07/01/2011'

go

--將數位簽章加入預存程序
ADD SIGNATURE TO OBJECT::[usp_Exectest1]

      BY CERTIFICATE [agentProxy]

        WITH PASSWORD = 'ahog834ugihGa832hfiwea'

go

--移除憑證的私密金鑰

alter certificate [agentProxy]

  remove private key

go

--將憑證匯出至檔案

backup certificate [agentProxy]

 to file='c:\temp\agentProxy.cer'

go

use msdb

go

--從檔案建立憑證

create certificate [agentProxy]

  from file='c:\temp\agentProxy.cer'

go

--建立使用者並指定資料庫使用者的憑證

create user [agentProxyAuthenticator]

 from certificate [agentProxy]

go

--設定權限

--grant authenticate to [agentProxyAuthenticator]

grant execute on msdb.dbo.sp_start_job to [agentProxyAuthenticator]

go

　　此時你再試著用user1執行exec dbo.usp_Exectest1看看，會發現剛剛的錯誤訊息雖解決了，但又出現新的錯誤，如下

/*

訊息14262，層級16，狀態1，程序sp_verify_job_identifiers，行52

指定的@job_name ('test1') 並不存在。

*/

　　這錯誤不是說作業test1不存在喔，而是你沒權限啦，預設非系統管理員及非SQL Server Agent 固定資料庫某個角色只能執行自己的作業，如何：設定使用者建立及管理 SQL Server Agent 作業有寫，如果這些非管理使用者想要執行作業來執行其他作業步驟類型(例如，SSIS 封裝)，則他們需要有Proxy 帳戶的存取權。

--5.變更作業test1的擁有者為user1

EXEC msdb.dbo.sp_update_job @job_name=N'test1',

      @owner_login_name=N'user1'

GO

　　如果這時你等不及用user1去執行exec dbo.usp_Exectest1了，你會說好像可以執行了耶，但你若去作業活動監視器看是作業test1執行是失敗的，有下列錯誤
/*
已拒絕非系統管理員 (Non-SysAdmins) 在沒有 Proxy 帳戶之情況下執行 CmdExec 工作步驟的權限.  步驟失敗。
*/
　　都已經跟你說要建Proxy啦，還不聽是怎樣？

--6.建立 SQL Server Agent Proxy的方法

--得先建立認證

ALTER CREDENTIAL Local_user WITH IDENTITY = 'SRV999T\test',

    SECRET = 'test'

GO

　　關於認證對應的Windows使用者帳戶SRV99T\test，其權限只要Users群組即可，建立 SQL Server Agent Proxy裡雖有提到
　　認證中所指定的使用者，在 SQL Server 執行的電腦上必須要有「以批次工作登入」的權限。
　　但我自己測試的結果，在此例子SRV99T\test加與不加「以批次工作登入」權限，似乎沒什差別喔

--加入指定的Microsoft SQL Server Agent Proxy

EXEC msdb.dbo.sp_add_proxy  

   @proxy_name=N'MiniProxy',

   @enabled=1,

   @description=N'最小權限的',

   @credential_name=N'Local_user'

GO

--授與子系統的Proxy 存取權(一個Proxy可以授予好幾個子系統存取權)

/*subsystem_id 描述

2 Microsoft ActiveX Script

3 作業系統(CmdExec) 

4 複寫快照集代理程式

5 複寫記錄讀取器代理程式

6 複寫散發代理程式

7 複寫合併代理程式

8 複寫佇列讀取器代理程式

9  Analysis Services 命令

10 Analysis Services 查詢

11 SSIS 封裝執行

*/

EXEC msdb.dbo.sp_grant_proxy_to_subsystem

   @proxy_name=N'MiniProxy', @subsystem_id=11

GO

--授與Proxy 的安全性主體存取權(登入名稱,固定伺服器角色與msdb 資料庫中之資料庫角色)

EXEC msdb.dbo.sp_grant_login_to_proxy

    @login_name = N'user1',

    @proxy_name = N'MiniProxy'

GO

--更新Job中作業步驟的執行身分為Proxy

EXEC msdb.dbo.sp_update_jobstep @job_name=N'test1', @step_id=1 ,

      @proxy_name=N'MiniProxy'

GO

　　大功告成，此時你再用user1執行usp_Exectest1看看，應該執行成功囉，雖然步驟很多很麻煩，但為了安全還是麻煩點好囉