Application of creden in SQL Server Agent

SQL Server Agent is a Windows service used to execute various management tasks. These tasks may involve accessing windows resources (such as creating or deleting files ). However, the user permissions in SQL Server are valid only within the scope of SQL Server and cannot be extended to SQL Server. This means that when the security context of the job is not authorized, the job will fail. So we need to find another way to solve this problem: creden.

First, let's take a look at the definition of creden:

Creden are records of the authentication information (creden。) required to connect to external resources of SQL Server. This information is used internally by SQL Server. Most creden contain a Windows user name and password.

Users who connect to SQL server by using the information stored in creden can access resources outside the server instance. If the external resource is windows, this user passes authentication as the Windows user specified in the credential. A single credential can be mapped to multiple SQL Server login names. However, one SQL Server login name can only be mapped to one credential.

 

It can be seen that creden。 can solve this problem well. It should be noted that the SQL Server Agent does not directly use creden, but encapsulates it in proxy for use.

 

Here is an example to demonstrate how to use creden:

Login1 is a logon user in SQL Server. Its task is to regularly clear files in the folder D: \ backup. The SQL Server Agent can help login1 to complete this task.

The procedure is as follows:

1. Create a credential and bind the relevant Windows user (the user must have the permission to change the folder D: \ backup) to the credential.

2. Create a proxy and associate it with the creden.

3. Specify the agent subsystem of the proxy application

4. Authorize login1 to use the agent.

5. Grant login1 the permission to create a job.

6. Use login1 to create a job

First, log on to SQL Server as an administrator.

Create creden:

Choose ojbect explorer> SQL server instance> Security> Credentials

In the displayed window, enter the creden name, and the related Windows users (the user must have the permission to change the folder D: \ backup) and password.

Next, create a proxy.

Click SQL Server Agent-> proxies-> new proxy

Enter the proxy name, credential, and subsystem in the general column of the pop-up window.

After entering the information, click the principals column to specify the Logon account that has the right to call the proxy.

Currently, login1 can call the newly created proxy1, but still cannot create a job. If you log on to mssm with login1, you will find that the SQL Server Agent is hidden.

Go to the MSDB database, create a matched user for login1, and add it to the sqlagentoperatorrole role.

Choose object explorer> sqlserver instance> databases> MSDB> Security> Users> new user.

In the displayed window, enter the user name, login name, and role.

 

Log on to login1 and create a job.

Choose object explorer> sqlserver instance> SQL Server Agent> jobs> new job.

In the general dialog box, enter the job name.

Click the steps column to edit the steps for deleting files. We need to specify the proxy (creden) We need in run)

In this way, our job is roughly completed. When the job runs to step 1, the SQL Server Agent performs the delete operation in the security context of stswordman-PC \ testuser1.

The following are related SQL scripts.

 

Use MSDB
Go

-- Create credential
If exists (select 1 from SYS. Credentials where name = 'cred1 ')
Drop credential cred1
Create credential cred1 with identity = 'swordman-PC \ testuser1 ',
Secret = '000000_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,
@ Policy_level_eventlog = 0,
@ Policy_level_email = 0,
@ Policy_level_netsend = 0,
@ Policy_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'delete ',
@ 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: