SQL Server – SQL Agent Proxies

I have seen many DBAs arguing on xp_cmdshell as a security risk or a bad practice. And on a strong recommendation of a DBA Architect VENDOR your company has chosen to disable xp_cmdshell for you environment and restrict all access for all accounts on SQL Server to SQL itself. Unfortunately you are needed to write a simple job to perform some task using cmd shell as a part of one of your SQL task. This is when the SQL Agent Proxies come handy. You will understand how to use it once you get to end of this post.

What is an SQL Agent Proxy?

SQL Agent Proxy is the security context in which your job runs. So simply put, if your job step is set to an Agent subsytem it will run under the proxy credentials instead of the Agent credentials. The interesting point here is the credential need not be a part of your SQL Server logins. Also the proxy defined for an Agent subsystem can be used only for that subsystem.

Let’s see a demo to understand how to use an Agent proxy.

To create a proxy we first need to add the Windows user as a credential. Once the credential is created you can map it to the Agent proxy.

1. Go to Security > Credentials > Right click and select New Credential.

1_SQL_Agent_Proxies

2. Add a Windows user which is not already a part of SQL Logins. You also need to provide the password of the account as it uses these credentials to validate the account.

2_SQL_Agent_Proxies

3_SQL_Agent_Proxies

3. Now the credential is created and we will be using it for creating the proxy for CmdExec. Go to SQL Server Agent > Proxies > Operating System (CmdExec) > Right click and select New Proxy.

4_SQL_Agent_Proxies

   

4. In the new Proxy window give the Proxy name, map it to the credential we have created and the Agent subsystem will be selected by default. Observe that you can select multiple subsystems and use a same Proxy.

5_SQL_Agent_Proxies

5. Now you have successfully created the Agent proxy. To demonstrate I have created a job CmdJobTest with a step as below.

6_SQL_Agent_Proxies

This shows that when you select the job step type as Operating System (CmdExec), either SQL Agent default account or the proxy for the CmdExec can be used. I already have another proxy for Replication snapshot which is not visible here.

6. When I run the job with default agent account and the proxy account you can see the difference in the job history log.

7_SQL_Agent_Proxies

So no worries if you have no xp_cmdshell or your OS level privileged account is not a part of SQL Logins. Agent subsystems and Proxies are your friend.

Happy Learning.

 

Regards

Manohar Punna

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

   

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

2 Comments on “SQL Server – SQL Agent Proxies”

Leave a Reply

Your email address will not be published.