sql server 2012 您为Analysis server 服务提供的凭据无效 为sql server服务指定的凭据无效

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: