Using Proxy Accounts in SQL Server Agent Jobs

Using Proxy Accounts in SQL Server Agent Jobs

When using SQL Server, in many cases you will might need to set up a SQL Server Agent job that will be accessing a resource within the domain.

For example, you want to include a step in a SQL Server Agent job that based on some logic, will be handling a Windows service on a server within the domain by using the Operating System (CmdExec) SQL Server subsystem.

In order for the job to be successfully executed, the specific job’s execution context should be allowed access to the target resource in the domain.

If you are using a domain user as a service account for the SQL Server Agent in the specific instance, you can assign the necessary access rights to that user account.

However, there is also another way which I personally prefer; using a Proxy Account for executing the specific job step 🙂

In order to be able to do this you must perform the following actions within the instance of SQL Server:
  1. Create a credential
  2. Create a Proxy Account that uses the credential you created in the first step  

For creating a credential, in SSMS you navigate to: Security — Credentials
You can then create the credential by providing an identity (i.e. a domain user) along with its password.

For creating a Proxy Account, in SSMS you navigate to: SQL Server Agent — Proxies

You can then create a new Proxy Account by giving it a name and performing the following:
– Provide the credential you earlier created
– Enter a description (optional)
– Set the subsystems for which the Proxy Account will be active. These are:
——- ActiveX Script
——- Operating system (CmdExec)
——- Replication Distributor
——- Replication Merge
——- Replication Queue Reader
——- Replication Snapshot
——- Replication Transaction-Log Reader
——- SQL Server Analysis Services Command
——- SQL Server Analysis Services Query
——- SQL Server Integration Services Package

That’s it!

You can now proceed and set up the SQL Server Agent job along with its steps, and in the step you want to use the Proxy Account you select it in the “Run as” drop down box.

Whenever the specific job step runs, it will be executed in the context of the provided Proxy Account.

* Note that in order to be able to use a Proxy Account in a specific job step, the Proxy Account needs to be activated for the specific subsystem (i.e. Operating system – CmdExec).

I hope you found this post useful!


My Latest Projects:


Recommended eBooks on SQL Server:

Developing with SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Developing SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.