This project has moved and is read-only. For the latest updates, please go here.

Previous xp_cmdshell Proxy Account Change Proxy Account Password Next

SQL Agent Job Proxy Accounts

The SQL Agent Proxy accounts allow users without sysadmin authority to own and run SQL Agent jobs.

The SQL Agent contains a number of subsystems, each one dedicated to a particular type of work. A proxy account is required in order for an account without sysadmin authority to use these subsystems.

The recommended approach is to configure each non-sysadmin account that needs to run SQL Agent jobs as a proxy account. This will allow all the authorities required by the job to be contained within the job owner account.

The first step in creating a SQL Agent proxy is to create a credential. Therefore, every Job Owner account would have a credential defined for it in order to both isolate and encapsulate the authorities required by a given job.

The credentials created for a given job owner are then associated with each type of SQL Agent subsystem used by the job owner. It is this association that creates the required proxies. This will ensure that all steps in the jobs owned by that account run with a predictable and consistent security profile. The only exception is if a job step runs the xp_cmdshell routine, where just the command run by xp_cmdshell will use the CmdExec proxy authorities.

In practice, it is rare to create SQL Agent proxies before the server is handed over for application use. Typically, SQL Agent proxies will be created during normal SQL Server use after user databases, logins, jobs, etc have been created. The Application Support Team are often responsible for specifying the account name to be used for the SQL Agent proxy account, along with the Windows and SQL Server database roles it needs.

It is possible to use the ##xp_cmdshell_proxy_account## credential for all SQL Agent proxies, or even set up separate credentials for each type of SQL Agent subsystem. However, if the xp_cmdshell proxy was used for all SQL Agent subsystem proxies, this would give unnecessary additional rights to the xp_cmdshell proxy. If separate proxies were set up for each SQL Agent subsystem and all jobs used these proxies, this would both give unnecessary additional rights to the various proxies and fragment the authorities required by a given job. The recommended approach is the most secure method of dealing with SQL Agent proxies.
  • The terminology used in Microsoft documentation is sometimes inconsistent regarding the use of the terms Subsystem and Proxy. A SQL Agent Subsystem is the process that performs a particular type of work, such as running SSIS packages, etc. A Proxy is the authority used to access the Subsystem.

There are four steps needed to prepare a SQL Agent Job Proxy for use:

Create the SQL Agent Job Proxy
Associate Proxy with SQL Agent Subsystems
Associate Proxy with Job Steps
Add Additional SQL Agent Subsystems to Proxy

Create the SQL Agent Job Proxy

The processing in this section can be performed using the spCreateAgentProxy stored procedure.

1) Start SQL Server Management Studio (Start -> Programs -> Microsoft SQL Server -> SQL Server Management Studio).

Connect to the instance using Object Explorer, navigate to Credentials and select New Credential. A credential links the Proxy account to a Windows account with the required authorities.
SSMSNewCredential.png
2) Set the credential name to be the same as the Windows account name that will be used, and then click on the ellipses button by the Identity field.
SSMSCredentialName.png
3) Set the Location to the domain, enter the job owner account, and then click OK.
SSMSCredentialAccount.png
4) Type the password for the account, and then click OK.
SSMSCredentialPassword.png
Save the password in the DBA Password Store. If the password for a proxy ever needs to be changed, see Change Proxy Account Password.
5) Navigate to Logins and select New Login.
SSMSCredentialLogin.png
6) Locate the required proxy account login in the normal manner.

Ensure that Map to Credential is checked, and select the credential created for the account. Click Add to complete the mapping.
SSMSCredentialMap.png
7) Set the Default database to the application database.
SSMSCredentialDatabase.png
8) The next stage is to configure the job owner account so it can manage jobs.

Select the User Mapping page. Enable access to msdb, and grant use of the SQLAgentUserRole. The SQLAgentUserRole authority is required both to allow the account to manage the jobs it owns, and to allow the associated credential to function as a SQL Agent proxy.
Refer to Books Online to decide if you also need to associate the proxy with the SQLAgentReaderRole or SQLAgentOperatorRole
SSMSCredentialRole.png
9) Click on the ellipses in the Default Schema column. Type dbo, click on Check Names then click OK.
SSMSCredentialSchema.png
10) Assign the database roles and default schema specified by the Application Support Team for this account. Then click OK to save the new login.
SSMSCredentialSave.png

Associate Proxy with SQL Agent Subsystems

The next stage is to associate the proxy with one or more SQL Agent subsystems.

1) Navigate to SQL Server Agent -> Proxies, and right-click on the required subsystem (e.g. Operating System (CmdExec)) and select New Proxy.
JobProxyCreate.png
2) Type the name of the proxy, which should be the same as the Credential and account name, then click the ellipses button by the Credential Name field.

If this proxy is to be used for other subsystems, these can be specified in the list of subsystems. To add an additional subsystem at a later stage, see Add Additional SQL Agent Subsystems to Proxy.
JobProxyName.png
3) Click Browse.
JobProxyCredential.png
4) Select the required credential name, and then click OK. Click OK again to return to the New Proxy Account window.
JobProxyCredentialSelect.png
5) The proxy must be associated with a SQL login to obtain the authorities it will use within SQL Server. (The proxy obtains the authorities it will use within Windows from the credential.)

Select the Principals page and click Add.
JobProxyAssociate.png
6) Set the Principal type to SQL Login, and select the login with the same name as the proxy. This ensures that any authorities granted to the account are also available to the proxy.
JobProxyPrincipal.png
7) Click OK until you return to SQL Server Management Studio.

Associate Proxy with Job Steps

The proxy must now be associated with the required job steps.

1) Either create a new SQL Agent job or edit an existing one. Set the job owner to match the proxy you are going to use.
JobProxyGeneral.png
2) Select the Steps page, highlight the step to be configured and click Edit.
JobProxyStep.png
3) Set the Run as field to the required proxy.
JobProxyRunAs.png
4) Repeat this process for all other job steps that need to be run using this proxy. Click OK until the job is saved.

SQL Server Management Studio can now be closed.

Add Additional SQL Agent Subsystems to Proxy

The quickest way to add additional SQL Agent subsystems to an existing proxy is to open the Properties page for an existing subsystem for the proxy, and add the new subsystem.

1) Start SQL Server Management Studio. Connect to the instance using Object Explorer, and then navigate to an existing subsystem for the proxy. Right-click on the subsystem and select Properties.
AddProxyProperties.png
2) Select the additional subsystems you want to add to the proxy.
AddProxySubsystem.png
SQL Server Management Studio can now be closed.

Copyright © 2012 Edward Vassie. License and Acknowledgements
Previous xp_cmdshell Proxy Account Top Change Proxy Account Password Next

Last edited Nov 21, 2012 at 6:01 PM by EdVassie, version 3