在SQL Server Agent中应用凭据.docVIP

在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-JobsNew Job .在弹出窗口的General中填写job名称 点击Steps栏，编写删除文件的脚步。 我们需要在Run as 中指定我们需要的代理（凭据） 这样，我们的job就大致完成了，在job运行到step1步骤时，SQL Server Agent会以stswordman-pc\testuser1的安全上下文执行删除操作。 下面是相关的sql脚本。 ? Use?msdbGo--create?credentialif?exists(select?1?from?sys.credentials?where?name=cred1)????drop?credential?cred1Create?credential?cred1?with?identity=stswordman-pc\testuser1,secret=123123_ago--remove?exist?jobif?exists(select?1?from?sysjobs?where?name=removeFile)????exec?msdb.dbo.sp_delete_job?@job_name?=removeFilego--remove?exist?proxycreate?table?#tmp_sp_help_proxy(proxy_id?int?null,?name?nvarchar(1