在SQLServerAgent中应用凭据.doc

在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）中使用.下面我用一个示例来演示如何使用凭据：Login 1是sql server中的一个登录用户，他的任务是定期清除文件夹d:\back叩中的文件。

Sql server agent可以很好的帮助Login 1完成此任务步骤如下1. 创建凭据，将相关的windows用户（该用户需要冇更改文件夹d:\backup的权限）绑定到凭据 中2. 创建代理，与凭据联系起来3. 指定代理应用的昭ent子系统4. 授权loginl使用代理.5. 授予loginl创建job的权限6. 使用loginl创建job首先以管理员的身份登陆SQL SERVER创建凭据：点击 Ojbect Explorer->sql server 实例->Security->CredentialsNotification Services SQL Server AgentNew Credentials.在弹出窗口内填写凭据名称，相关的windows用户（该用户需要有更改文件夹d:\backup的权限） 及密码接下來创建代理• i :丄 RepBc^Mion• •「-> R«p*c4Hion Qumw 只••£!••si :」Replk^lon ^r^>psho<• i 一 RepMc — Una^%4Qr»^dKrror Loq*

如果以login 1登陆MSSM, 你会发现sql server agent处于隐藏状态进入msdb数据库，在其中为login 1创建匹配的用户，然后将其加入SQLAgentOperatorRole角色 点击 Object Explorer->sqlserver 实例->Databases->msdb->Security->Users->New User在弹岀窗口内填写用户名称Jogin名称及角色.现在使用login 1登陆，创建job.点击 Object Explorer->sqlserver 实例->SQL Server Agent->Jobs>New JobOMm Exploetr a *Comect* 刃 J ▼皿 STSWQRPMAN.PC ($Ql Ww 9AB99 • loglnl) ■二 D«Uba$<$® N Security• «j $«vtf ObHg« Job QrttgoriM^ewHistcxyRefresh•在弹出窗口的General中填写job名称点击Steps栏，编写删除文件的脚步。

我们需要在Run as中指定我们需要的代理（凭据）这样，我们的job就大致完成了，在job运行到stepl步骤时，SQL Server Agent会以 stswordman-pc\testuserl的安全上下文执行删除操作下面是相关的sql脚本Use msdbGo-create credentialif exists(select 1 from sys.credentials where name=,cred 1 *)drop crcdcntial crcd 1Create credential credl with identity=,stswordman-pc\testuserl\ secret 二'123123_afgo—remove exist jobif exists(select 1 from sysjobs where name二TemoveFile')exec msdb.dbo.sp_deletejob @job_name =TemoveFile,go—remove exist proxycreate tabic #tmp_sp_hclp_p「oxy(proxyint null, name nvarchar( 128) null, crcdcntial_idcntity nvarchar( 128) nu11, enabled tinyint null, description nvarchar(1024) null, user_sidvarbinary(40) null, credentialjd int null, credential_identity.exists int null)insert into #tmp_sp_help_proxy(proxyjd, name, credential_identity, enabled, description, user_sid, credentiaMd, credential_identity_exists) exec msdb.dbo.sp_help_proxyif cxists(sclcct 1 from #tmp_sp_hc】p_p「oxy where namc-fproxy 1 *)exec msdb.dbo.sp_delete_proxy @proxy_name = °proxyT—create proxyexec msdb・dbo.sp_add_proxy@proxy_name = 'proxy 1*,@enabled = 1 ,@credential_name = 'cred Vgo-special the subsystemexec msdb.dbo.sp_grant_proxy_to_subsystem (gproxy^ame-N^proxy 1 ；@subsystem_id=3-grant permissionexec msdb.dbo.sp_grant」ogin_lo_proxy@login_name = logi nF,@proxy_name = 'proxy Vgo-grant the create job permission to login 1if exists(select 1 from sys.database_principals where narne=,user_loginl Jdrop user userjogin 1Create user user_k)ginl for login login 1Gosp_addrolcmcmbcr ^QLAgcntuscrRolc^uscr」ogin I1go-create job.execute as login二'login 1’goUSE [msdb]GO/****** Object: Job [removeFilel Script Date: 09/30/2008 21:50:09 ******/BEGIN TRANSACTIONDECLARE @RcturnCodc INTSELECT @ReturnCode = 0/****** Object: JobCategory [[Uncategorized (Local)JJJ Script Date: 09/30/2008 21:50:09 ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscalegories WHERE name-N'|Uncategorized (Local)]' AND category_class= 1)BEGINEXEC @ReturnCode 二 msdb.dbo.sp_add_category @class: NfJOB\ @type二N'LOCAL； @naine=N7Uncategorize d (Local)]'IF (@@ERROR <> 0 OR @RelumCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobld BINARY(16)EXEC @RcturnCodc = msdb.dbo.sp_addJob @job_namc-N'rcniovcFilc\@enabled=l,@no tify_level_eventlog^O,@notify_level_email=0,@notify_IeveLnetsend=0,@notify」cvcl_pagc 二 0,@delete_level=0,@description=N,remove file where located in d:\backup；@category_name-N,| Uncaiegorized (Local)@()wncr_l()gin_name二N'loginl； @job_id = @jobld OUTPUTIF (@@ERROR <> 0 OR @RcturnCodc <> 0) GOTO QuitWithRollback/****** Object: Step [removel Script Date: 09/30/2008 21:50:09 ******/EXEC @ReturnCode - msdb.dbo.sp_addjobstep @job_id=@jobid, @step_narne^NYemove\@slep_id=I,@cmdexe。