Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Creating a SQL Server proxy account to run xp_cmdshell


By:   |   Read Comments (2)   |   Related Tips: More > Security

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

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!

Solution

For tips on how to enable xp_cmdshell please see my previous tip or this tip on how to enable xp_cmdshell via the Surface Area Configuration tool in SQL Server 2005. 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!

USE MASTER;
GO

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
CREATE ROLE [CmdShell_Executor] AUTHORIZATION [dbo]
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
  • Learn the various techniques for enabling xp_cmdshell with this tip.
  • Learn how you can run a DOS command from SQL Server without using xp_cmdshell at MSSQLTips.
  • Read about how you can execute command line scripts via SQL Agent jobs here.
  • Review the previous tip in this series


Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

Hi

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
RECONFIGURE WITH OVERRIDE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
EXEC master..xp_cmdshell @CMD 
..etc

How do we let our login do that as well?

Thanks 

 


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

Hi,

 

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.

 

Steve


Learn more about SQL Server tools