SQL Server Agent (10/12): Run a job with a proxy account

SQL Server Agent is the core of all real-time databases. Proxies have a lot of non-obvious uses, so the knowledge of the system is useful for developers or DBAs. This series of articles will be popular to introduce its many uses.

In this series of previous articles, you reviewed the security roles under the msdb library for authorizing access to SQL Server Agent. These roles include SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole. Each role grants the user certain permissions to use SQL Server Agent instead of being a member of the sysadmin server role. For full administrative control of SQL Server Agent, you still need members of the sysadmin role. Additionally, you review the security impact and selection of the SQL Server Agent service account. In this article, you will learn the concept of a proxy account for SQL Server Agent. The proxy account allows job steps to impersonate a specific Windows security account to perform actions on the job step. This is typically applied when the job owner's security credentials do not apply when the operation is performed.

What is a proxy account?

As mentioned above, a proxy account is a set of security credentials that are stored in Windows. These credentials can be used as the security context for the job. A proxy account can be used by one or more eligible subsystems.

subsystems for available proxy accounts

Not all subsystems can use a proxy account. The subsystems that can use a proxy account include:

• ActiveX scripts
• Operating System (CMDEXEC)
• Copying a series of tasks
• SQL Server Analysis Services Command (XML/A)
• SQL Server Analysis Services query (MDX)
• SQL Server Integration Services Package
• PowerShell

There is no Transact-SQL script (T-SQL) subsystem. T-SQL job steps typically run in the security context of the job owner and cannot be changed in SSMs. You can manually configure the job steps, use the sp_add_jobstep system stored procedure, simulate a database user with the Database_user_name parameter, and of course you need security permissions to impersonate the database user.

You will also notice that the replication subsystem is combined, even though there are several different subsystems that handle replication. In theory you can use a proxy account, but that is beyond the advanced configuration of this series range.
When you create a job step, 1 is shown (open the Job Selection steps page and click New ... button, select a subsystem that supports the proxy account, such as PowerShell. You will see a list of accounts that can simulate support job steps. If you do not create any proxy account credentials, the list should look like Figure 1, with only the SQL Server Agent security account.

Figure 1: Selecting a proxy account for the job step

Security Considerations for Proxy accounts

In order for a proxy account to run correctly, the account must have the "Log on as a batch job" (sebatchlogonright) permission that is assigned to it by the Windows Administrator (User Rights Assignment, local policies, local security policy, and so on). The SQL Server Agent service without privileges will not be able to impersonate the account to run the job step. It is also important to note that the proxy account is not automatically able to access your SQL Server. For example, if you want to use a CmdExec or PowerShell job step to log back on to SQL Server, the proxy account must be explicitly granted to log back on to your SQL Server (or to inherit access to the Windows group).

Create a proxy account

you can use Transact-SQL or SSMS to create a proxy account. Using Transact-SQL, use System stored procedure sp_add_proxy.

1 EXECSp_add_proxy2     [@proxy_name =] 'Proxy_name' ,3     [@enabled =]is_enabled,4     [@description =] 'Description' ,5         [@credential_name =] 'Credential_name' ,6     [@credential_id =]credential_id,7     [@proxy_id =]ID OUTPUT

Keep Proxy_name blank to ensure that the proxy is the same name as the credential. The credential name should come from the security credentials (created using the CREATE Credential Database definition statement).
For example, to create a [Proxy1] proxy account, password "Password1" (that is, an account created on your local SQL Server machine), you can run code 1 and replace it with a valid domain, identity, and password in your environment.

1  UseMSDB;2 GO3 CREATECredential[Pc201602202041\proxydemo]  with IDENTITY = 'Pc201602202041\proxydemo'4, SECRET= 'Password1';5 6 Declare @rc int=0;7 8 EXECSp_add_proxy[Pc201602202041\proxydemo],1,'This is a example proxy account',9 [Pc201602202041\proxydemo],NULL,@rc;

Code Listing 1: Creating Credentials and proxy accounts
It is worth noting that currently you have created a proxy account, but it is not associated with any particular subsystem. If you expand Agent-and unassigned proxies, you'll see the proxy account you just created, as shown in Figure 2.

An unassigned proxy account under illustration 2:ssms
Note that it is easier to create this using SSMS, but there is still a two-step process. You must first create a credential (security, credentials, new credentials, open New Credential dialog box). In this example, use Cred1 as the account number, using the same password as shown in Figure 3.

Create credentials under illustration 3:ssms
Click OK, and then navigate to the Proxy folder and right-click any agent subsystem (or proxy folder itself) to create a new agent. Pop-up the new proxy account, type the Proxy1 name, select the matching credentials, enter a description, if you need to select a job subsystem (as illustrated in Figure 4). If you do not select a subsystem, the proxy account will appear under an unassigned agent (similar to a proxy created with a statement).

Create a proxy account under illustration 4:ssms
The final step is to associate the proxy account with the subsystem, which is implemented through the system stored procedure sp_grant_proxy_to_subsystem. In the previous example, in order to assign the proxy account [Proxy1] to the PowerShell subsystem, you could run the following code:

@proxy_name=n'Proxy1@subsystem_id=     

The subsystem_id of the subsystem can be found in the MSDN documentation https://msdn.microsoft.com/zh-cn/library/ms186760.aspx

Authorization Agent Credentials

You may have noticed that the new Proxy Account dialog box has a Principal tab. By default, members of the sysadmin server role are eligible for proxy, but this does not apply to other people. If you want to have a non-admin user Access Proxy credentials (which is very likely, otherwise why create them), then you need to grant explicit access to each login you want to use the proxy.
You can use the system stored procedure sp_grant_login_to_proxy to implement, or SSMS. Open the Proxy1 proxy account under the PowerShell Agent folder and go to the Principal tab. By clicking Add (10.5), you can associate one or more security principals (logins) and your proxy account. Once this is done, any principal-owned jobs can use this proxy account. Note that there is a third tab, reference, which shows which job step uses this proxy account, and you can modify or delete the agent directly.

Figure 10.5 Associating a proxy account with a security principal (SQL login)

Using a proxy account

You can now modify the job step to use the proxy account. If you are following this series of articles, you should have a job called Shellout, and its second step S2 is the PowerShell subsystem. Open this job step and change the run identity to Proxy1 (10.6). If you do not have this job step, you should create one of these steps.

Figure 6: Update job steps with a proxy account
Now run the job again so that the job step is performed using the proxy account instead of the SQL Server service account. Remember that your proxy account must be able to log on to your DB instance, so make sure you have created a database login for your proxy account.

modifying and removing agents

Using SSMs to modify or remove an agent is intuitive (open the Proxy Account dialog box change), but for the complete corresponding Transact-SQL statement is:

'proxy1' 

Next trailer

The proxy account for SQL Server Agent allows non-sysadmin users to impersonate other Windows security credentials with the ability to perform critical tasks with privileges. When combined subsystems such as CmdExec and PowerShell, they allow the owner of the SQL Agent job to be granted lower privilege levels than the sysadmin.
In our next article, we'll look at the maintenance plan jobs and the difference between them and the jobs you create yourself.

Original address: http://www.sqlservercentral.com/articles/Stairway+Series/72461/

Reference article: http://www.cnblogs.com/Uest/p/4562219.html

SQL Server Agent (10/12): Run a job with a proxy account