Creating a SQL Server proxy account to run xp_cmdshell

By:   |   Comments (2)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > Security


In my previous tip, I showed how you could dynamically execute a SQL Server Integration Services (SSIS) package from T/SQL. What I did not discuss were the processes by which you could reduce the security risk that enabling xp_cmdshell produces, and in doing so what the ramifications are on security inside the database being accessed via SSIS. It's time now to take care of those topics!


For tips on how to enable xp_cmdshell please see my previous tip or this tip on how to enable xp_cmdshell. For the purpose of this discussion we are instead going to look at what's next - how do you better-secure the SQL Server instance after enabling xp_cmdshell.

The process we're going to employ is the creation of a proxy account by which xp_cmdshell will be run. This will allow xp_cmdshell to be run without the need to employ an account with System Administrator privileges or elevated domain or local administrator rights, vastly reducing the risk of privilege elevation to the domain and a hacker's paradise happening in your datacenter.

Step one is to create a login you'll assign as your proxy. The important thing here is to ensure it is not a domain admin!


CREATE LOGIN [fake_domain\shellProxyUser] FROM WINDOWS;

After doing so you'll need to create a proxy for the xp_cmdshell to run as, since this is going to be solely a domain user account without local admin rights to the SQL Server you'll greatly reduce the exposure an out-of-the box activated xp_cmdshell instance provides:

EXEC sp_xp_cmdshell_proxy_account 'fake_domain\shellProxyUser','reallystrongpassword'

Now it is simply the matter of creating users in your master database (where xp_cmdshell resides) and assigning execute rights to the xp_cmdshell extended stored procedure. I prefer to do this via a Database Role membership as outlined below. The code below assumes you already have a valid login created on the SQL Server instance:

--Create the database role and assign rights to the role
GRANT EXEC ON xp_cmdshell TO [CmdShell_Executor]

--Then once done create users and assign to CmdShell_Executor
CREATE USER [fake_domain\kalamazoo_users_group] FROM LOGIN [fake_domain\kalamazoo_users_group];
EXEC sp_addrolemember [CmdShell_Executor],[fake_domain\kalamazoo_users_group];

CREATE USER [fake_domain\sheldoncoop] FROM LOGIN [fake_domain\sheldoncoop];
EXEC sp_addrolemember [CmdShell_Executor],[fake_domain\sheldoncoop];

CREATE USER [fake_domain\oliveford] FROM LOGIN [fake_domain\oliveford];
EXEC sp_addrolemember [CmdShell_Executor],[fake_domain\oliveford];

CREATE USER [fake_domain\seattle_user_group] FROM LOGIN [fake_domain\seattle_user_group];
EXEC sp_addrolemember [CmdShell_Executor],[fake_domain\seattle_user_group];

While this gives you a more secure execution of xp_cmdshell it will now cause failure when the package executes. What's that I say? Yes - failure. This is because an out-of-the-box enablement of xp_cmdshell runs under a System Administrator account, I've just shown how to avoid the "sledgehammer approach" of using a System Admin level login for one that will operate with the barest of user rights.

This issue can be overcome quite easily. You only need to grant the login defined in the call we originally made to sp_xp_cmdshell_proxy_account the appropriate rights to any securables touched in the SSIS package. This underlies yet another reason to use Database Roles in your SQL Server databases. If you have a role defined with all the grants required to run the SSIS package you simply need to add the proxy account as a member to the Database Role - otherwise you're about to (possibly) spend some time granting rights to tables, views, stored procedures, and the like to the proxy account.

So to summarize the process for reducing the possibility of rights escalation to your domain from improper use of xp_cmdshell:

  1. Create a login from an Active Directory account that is a domain user, but not either a domain or local administrator
  2. Execute sp_xp_cmdshell_proxy_account using the login's credentials you just created to create a non-system administrator proxy for xp_cmdshell
  3. Create a database role and grant execute rights to xp_cmdshell to that database role
  4. Add the necessary members to that role for anyone you are going to allow to run xp_cmdshell
  5. Finally, you'll need to ensure the proxy account has all necessary rights for running the code inside of the SSIS package being called
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, November 14, 2017 - 2:50:06 PM - Jim Back To Top (69731)


This is good, but the other thing that has to happen is to enable xp_cmdshell. In a procedure we have:

EXEC master.dbo.sp_configure 'show advanced options',1
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
EXEC master..xp_cmdshell @CMD 

How do we let our login do that as well?



Thursday, October 25, 2012 - 12:19:17 AM - Steve Back To Top (20081)



I have read your article and find it most interesting. However I have tried to remove the proxy user credentials by using the "NULL" option without success. The credentials disappear and sys.credentials has no records but xp_cmdshell still looks for the proxy user and throws an error. Should I restart the SQL instance? It didn't need a restart to create the proxy so shouldn't need one for the removal.



get free sql tips
agree to terms