SQL Server Agent中憑據應用

Sql server Agent是一種windows服務，用於執行各種管理工作。這些任務可能會涉及到一些對windows資源的訪問（例如建立/刪除檔案等）。但是SQL Server中使用者權限只在SQL Server範圍內有效，無法擴充到SQL Server以外，這就意味著當執行job的安全上下文缺少相應許可權時，job會失敗。所以我們需要尋找另外一種方法來解決這個問題：憑據。

首先看一下憑據的定義：

憑據是包含串連到 SQL Server 外部資源所需的身分識別驗證資訊（憑據）的記錄。此資訊由 SQL Server 在內部使用。大多憑據都包含一個 Windows 使用者名稱和密碼。

利用憑據中儲存的資訊，通過 SQL Server 身分識別驗證方式串連到 SQL Server 的使用者可以訪問伺服器執行個體外部的資源。如果外部資源為 Windows，則此使用者將作為在憑據中指定的 Windows 使用者通過身分識別驗證。單個憑據可映射到多個 SQL Server 登入名。但是，一個 SQL Server 登入名只能映射到一個憑據。

 

可以看出，憑據可以很好地解決這一問題。需要注意的是,SQL Server Agent並不直接使用憑據，而是將其封裝在代理（proxy）中使用 .

 

下面我用一個樣本來示範如何使用憑據：

Login1是sql server中的一個登入使用者，他的任務是定期清除檔案夾d:\backup中的檔案。Sql server agent可以很好的協助Login1完成此任務

步驟如下

1. 建立憑據，將相關的windows使用者（該使用者需要有變更檔夾d:\backup的許可權）綁定到憑據中

2. 建立代理，與憑據聯絡起來。

3. 指定代理應用的agent子系統

4. 授權login1使用代理.

5. 授予login1建立job的許可權。

6. 使用login1建立job

首先以管理員的身份登陸SQL SERVER

建立憑據：

點擊Ojbect Explorer->sql server執行個體->Security->Credentials

在快顯視窗內填寫憑據名稱，相關的windows使用者（該使用者需要有變更檔夾d:\backup的許可權）及密碼

接下來建立代理

點擊SQL Server Agent->Proxies->New Proxy

在快顯視窗的General欄內填寫代理名稱，相應的憑據及其對應的子系統

填寫完畢後點擊Principals欄，指定有權調用該代理的登陸帳戶

現在的login1已經可以調用建立的proxy1了，但是仍然無法建立job。如果以login1登陸MSSM，你會發現sql server agent處於隱藏狀態。

進入msdb資料庫，在其中為login1建立匹配的使用者，然後將其加入SQLAgentOperatorRole角色。

點擊Object Explorer->sqlserver執行個體->Databases->msdb->Security->Users->New User

在快顯視窗內填寫使用者名稱稱,login名稱及角色.

 

現在使用login1登陸，建立job.

點擊Object Explorer->sqlserver執行個體->SQL Server Agent->Jobs>New Job

.在快顯視窗的General中填寫job名稱

點擊Steps欄，編寫刪除檔案的腳步。 我們需要在Run as 中指定我們需要的代理（憑據）

這樣，我們的job就大致完成了，在job運行到step1步驟時，SQL Server Agent會以stswordman-pc\testuser1的安全上下文執行刪除操作。

下面是相關的sql指令碼。

 

Use msdb
Go

--create credential
if exists(select 1 from sys.credentials where name='cred1')
    drop credential cred1
Create credential cred1 with identity='stswordman-pc\testuser1',
secret='123123_a'
go

--remove exist job
if exists(select 1 from sysjobs where name='removeFile')
    exec msdb.dbo.sp_delete_job @job_name ='removeFile'
go
--remove exist proxy
create table #tmp_sp_help_proxy(proxy_id int null, name nvarchar(128) null, credential_identity nvarchar(128) null, enabled tinyint null, description nvarchar(1024) null, user_sid 
varbinary(40) null,  credential_id int null, credential_identity_exists int null)
insert into #tmp_sp_help_proxy(proxy_id, name, credential_identity, enabled, description, user_sid, credential_id, credential_identity_exists) exec msdb.dbo.sp_help_proxy
if exists(select 1 from #tmp_sp_help_proxy where name='proxy1')
    exec msdb.dbo.sp_delete_proxy @proxy_name = 'proxy1'

--create proxy
exec msdb.dbo.sp_add_proxy 
     @proxy_name =  'proxy1' ,
     @enabled =  1 ,
     @credential_name = 'cred1'
go

--special the subsystem
exec msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'proxy1',
 @subsystem_id=3   

--grant permission 
exec msdb.dbo.sp_grant_login_to_proxy 
    @login_name =  'login1',
    @proxy_name =  'proxy1'
go
--grant the create job permission to login1
if exists(select 1 from sys.database_principals where name='user_login1')
    drop  user user_login1
Create user user_login1 for login login1
Go
sp_addrolemember 'SQLAgentuserRole','user_login1' 
go

--create job.
execute as login='login1'
go
USE [msdb]
GO
/****** Object:  Job [removeFile]    Script Date: 09/30/2008 21:50:09 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 09/30/2008 21:50:09 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'removeFile', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'remove file where located in d:\backup', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'login1', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [remove]    Script Date: 09/30/2008 21:50:09 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'remove', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'CmdExec', 
        @command=N'del d:\backup\* /q', 
        @flags=0, 
        @proxy_name=N'proxy1'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'schedule1', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=4, 
        @freq_subday_interval=1, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20080930, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: