SQL Agent Job Proxy Accounts
| Previous xp_cmdshell Proxy Account | Change Proxy Account Password Next |
|---|
This section contains the following items:
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.
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.
Please note: 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
The recommended approach is to configure one or more Proxies for each application that needs to run SQL Agent jobs. Each Proxy would be a separate Windows account, and each account would be given the rights (file access, etc) needed for its tasks.
The account used for the Proxy should not have any Administrator rights within Windows, and should not have sysadmin rights within SQL Server. This will allow application jobs to be run with a low privilege account, and will encapsulate all the authorities needed for the job within a single account.
-
Using xp_cmdshell Proxy for all SQL Agent Proxies
It is possible to use the ##xp_cmdshell_proxy_account## credential for all SQL Agent proxies
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 resulting in loss of Separation of Responsibilities
-
Use dedicated SQL Agent Proxy for each Subsystem
This approach would result in one Proxy for each SQL Agent Subsystem. All jobs that needed to use a proxy for that Subsystem would use the dedicated Proxy defined for that Subsystem
The main problem with this approach is that each Proxy would require authorities for many applications. It would become difficult to monitor what authorities a given proxy should have, and to remove these when no longer required.
It would also fragment the authorities required by a given job, again leading to problems keeping track of the authorities needed
-
The recommended approach is the most secure method of dealing with SQL Agent proxies
The first step is to create a Credential for the SQL Agent Job Proxy.
Every Job Owner account should have a credential defined for it in order to both isolate and encapsulate the authorities required by a given job.
A credential links the Proxy account to a Windows account. The Windows account should be given the required authorities (file access, etc) needed to do the tasks for which it will be used.
-
Start SQL Server Management Studio
For older versions of Windows, select Start -> Programs -> Microsoft SQL Server -> SQL Server Management Studio
Connect to the instance, navigate to Credentials and select New Credential
-
Set the credential name to be the same as the Windows account name that will be used
Click on the ellipses button by the Identity field
-
Set the Location to the domain, enter the job owner account, and then click OK
-
Type the password for the account, and then click OK
Save the password in the DBA Password Store. If the password for a proxy ever needs to be changed, see Change Proxy Account Password
The next stage is to create the SQL Agent account for the Proxy and link it to the Credential. The processing in this section can be performed using the spCreateAgentProxy stored procedure.
-
Navigate to Logins and select New Login
-
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
-
Set the Default database to the application database
-
The next stage is to configure the proxy 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
-
Click on the ellipses in the Default Schema column in the window above
Type dbo, click on Check Names then click OK
-
Assign the database roles and default schema
Use the values specified by your Application Support Team for this account, then click OK to save the new login
The next stage is to associate the Proxy with an SQL Agent subsystem. It is this association that creates the required proxies.
-
Navigate to SQL Server Agent -> Proxies
Right-click on the required subsystem (e.g. Operating System (CmdExec)) and select New Proxy
-
Type the name of the proxy
This should be the same as the Credential and account name. 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, or they can be added separately as given in Add Additional SQL Agent Subsystems to Proxy
-
Click Browse
-
Select the required credential name, and then click OK
Click OK again to return to the New Proxy Account window
-
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
-
Set the Principal type to SQL Login
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
-
Click OK until you return to SQL Server Management Studio
It may be that the Proxy needs to be added to multiple Subsystems, either at the time of creating the Proxy or at a later date
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
-
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
-
Select the additional subsystems you want to add to the proxy
The Job Owner account and the Proxy are two separate entities within SQL Server. The Job Owner account must be authorised to use the Proxy.
-
Run the following SQL statement within SSMS:
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'APPHelpDesk_SQLJobs', @login_name=N'ROOT\APPHelpDesk_SQLJobs'
The setup of the SQL Agent Job Proxy account is now complete. SQL Server Management Studio can now be closed
The Proxy can then be used in jobs as described below:
The proxy now needs to be associated with the required job steps. This will ensure that steps in the jobs owned by that account run with a predictable and consistent security profile
The only exception to the Proxy authority is if a job step runs the xp_cmdshell routine. In this case the command run by xp_cmdshell will use the CmdExec proxy authorities
-
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
-
Select the Steps page
Highlight the step to be configured and click Edit
-
Set the Run as field to the required proxy.
-
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
Copyright FineBuild Team © 2012 - 2021. License and Acknowledgements
| Previous xp_cmdshell Proxy Account | Top | Change Proxy Account Password Next |
|---|