在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: