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.

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.

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. 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. Now you have successfully created the Agent proxy. To demonstrate I have created a job CmdJobTest with a step as below.

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.

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 Twitter |  Follow me on FaceBook