Tenth. SQL Server Agent uses a proxy account

This article is the tenth article of the SQL Server Agent series, please refer to the original text for details

In the first few of these series, 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 Script
Operating System (CMDEXEC)
Copy series 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 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 the security right to impersonate a database user to do so.
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, 10.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 10.1, with only the SQL Server Agent security account.
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) assigned to it by the Windows Administrator (Local policy--user rights assignment, 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. Related syntax refer to MSDN (http://msdn.microsoft.com/en-us/library/ms188763.aspx)

EXECSp_add_proxy[@proxy_name =] 'Proxy_name' ,    [@enabled =]is_enabled,[@description =] 'Description' ,    [@credential_name =] 'Credential_name' ,    [@credential_id =]credential_id,[@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 the code in Code1 and replace it with a valid domain, identity, and password in your environment.

 UseMSDB;GOCREATECredential[Cred1]  with IDENTITY =N'User-67np5r8lgk\clearfile', SECRET=N'123456'Declare @rc int=0;EXECSp_add_proxy[Proxy1],1,'This is a example proxy account',[Cred1],NULL,@rc;

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 will see the proxy account that you just created, as shown in 10.2.
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 Rw2008r2\proxygui as the account, with the same password, as shown in 10.3.

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 Rw2008r2\proxygui name, select the matching credentials, enter a description, if necessary select a job subsystem (10.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).

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 [Rw2008r2\proxydemo] to the PowerShell subsystem, you could run the following code:

EXEC @proxy_name = N'rw2008r2\proxydemo'@subsystem_id=

The subsystem_id of the

subsystem can be found in the MSDN documentation http://msdn.microsoft.com/en-us/library/ms186760.aspx
authorized proxy credentials
You may have noticed that there is a principal tab in the New Proxy Account dialog box. 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 system stored procedure sp_grant_login_to_proxy to implement, or SSMS. Open the Rw2008r2\proxydemo 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.

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 running identity to Proxydemo credentials (10.6). If you do not have this job step, you should create one of these steps.
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: exec msdb.dbo.sp_delete_proxy @proxy_name = ' Proxy1 '
Http://www.cnblogs.com/stswordman/archive/2008/10/06/1302684.html
Next article
The SQL Server Agent proxy account allows non-sysadmin users to impersonate other Windows security credentials with the ability to perform critical tasks with rights and 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 assignments and the difference between them and the jobs you create yourself.

Tenth. SQL Server Agent uses a proxy account