Wednesday, 21 March 2012

How do you secure the SQL Server instance after enabling xp_cmdshell ?

How do you allow non-administrator to use xp_cmdshell ?

From the day I started working with SQL server, I have heard about the security risk associated with enabling the xp_cmshell and used to restrain from using the xp_cmdshell. Now I have started using it as xp_cmdshell simplify many tasks for us.The security risk associated with xp_cmdshell can be reduced by following the steps given below.

By default the xp_cmdshell is disabled and this can be enabled by surface area configuration manager or  sp_configure.Enabling the xp_cmdshell is a risk as it open a windows command shell with same security context of the SQL server service account. In most of the scenario SQL server service  account will have elevated permission like member of local/domain administrator group.Using this elevated rights, malicious users  can do lot of damage like creating a log in account with administrator right ,changing the registry,deleting file/folders,accessing network paths,stealing data,etc. With  default configuration, SQL login need sysadmin or control server right to run the xp_cmdshell command.

The first step to reduce the security risk of enabling xp_cmdshell is replacing the SQL service account with minimal rights.It is always advised to run the SQL server and related services under the domain account with minimum privilege. This will helps to reduce the risk of accessing the xp_cmdshell by SQL login  with sysadmin or control server rights.

To allow non-administrator to run the xp_cmdshell, we have allow SQL server to open windows command shell with the security context of a less  privileged windows account by configuring server proxy account. To do that create a domain account/local machine account with very minimal permission and configure the proxy account as given below

EXEC sp_xp_cmdshell_proxy_account 'MyDomain\My.login','MyPassw0rd'

Now the non-administrator can run the xp_cmdshell command by creating a user for them in master database and granting the execute permission to xp_cmdshell in master database as given below

CREATE login cmdshelluser WITH password ='Password123'CREATE USER cmdshelluser  FOR login cmdshelluser GRANT EXECUTE ON xp_cmdshell TO cmdshelluser

Now the windows command shell opened by cmdshelluser  using the xp_cmdshell will have the security context of the proxy account configured earlier. To determine security context
EXECUTE AS LOGIN = 'cmdshelluser'
GOxp_cmdshell 'whoami.exe'

If non-administrators granted with execute permission on xp_cmdshell ran the xp_cmdshell before configuring the proxy account, SQL server will throw below error

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

Thank you for reading this article. Follow my blog  @  Facebook page


  1. That's very interesting post about the security. If the readers of this post also interested to understand basics and fundas of the databases, may visit :

  2. How to append the log in the same file using the xp_cmdshell command ?

  3. It's nice to see someone singing the praises of xp_CmdShell for a change, so well done there.

    However, I'd never grant non-DBA individuals with the privs to run it directly. I would only allow them through a properly written stored procedure.

  4. Enabling and using xp_cmdshell is just plain a bad idea, and here is one example: consider how you might audit a user's actions once control is passed from the database engine (executed query as loginA in the sysadmin Role) to the cmd-shell in the operating system (executed as the SQL Server service account). System changes can easily be obfuscated due to this behavior and the proxy account does not close the loop since you can only have one per instance, which in many corporate environments is unacceptable.

    Nice article but I would rather see folks start using PowerShell to bridge the "SQL Server/OS Gap" * instead of accessing the OS via a conduit like xp_cmdshell that is limited, at best, in what it can do towards running a secure SQL Server.

    * credit Michael Otey for the phrase:

  5. Dear Web site owner. My partner and i actually enjoy this post and the internet site all in all! Your piece of writing is really plainly composed as well as simply understandable. Your current Blog design is awesome as well! Would be awesome to know where I are able obtain it. Please maintain up the very good job. We all require far more such website owners like you on the net and much fewer spammers. Fantastic mate!

  6. Hey very nice blog!!
    Hi there,I enjoy reading through your article post, I wanted to write a little comment to support you and wish you a good continuationAll the best for all your blogging efforts.
    Appreciate the recommendation! Let me try it out.
    Keep working ,great job!
    Awesome post

  7. I agree that auditing who did what via xp_CmdShell simply doesn't exist but be advised that just having xp_CmdShell turned off provides absolutely no reasonable level of security. Anyone including but not limited to internal or external hackers that gain or already has access to your system with "SA" privs can enable and use xp_CmdShell in about 3ms, use it, and erase their tracks (they can even make a self deleting job to do whatever). Even if you're auditing for whether or not it's been turned on, that will only serve as fine testiment that you've been hacked and the damage will have already been done. If you want to disable xp_CmdShell and leave it that way, I have no problem with that but understand that will only keep the honest man honest and it doesn't even qualify as a "layer" of security because it's a simple switch setting that's easily changed by anyone with "SA" privs. Even if you could audit to a more significant level, an attacker isn't going to break into your system as himself... he's going to break in as one of the people on the server that has "SA" privs either by SQL Injection, rainbow table, or lucky guess and inhouse people can hide their tracks because they have the access.

    As for setting non-SA people up to use xp_CmdShell goes, I think that's probably one of the worst security violations you could have on your system and should never be done. Either setup a secure job to do the task and give them privs to execute that one job (which would also allow some deeper auditing) or learn how to set procs up to do such a thing (which should also contain logging using ORIGINAL_LOGIN {which won't help for most applications because of the way they're writte}).

    As for allowing users to use the DOS Prompt, PowerShell, or any of a dozen other scripting languages, there's little to be had in the form of auditing there, as well, but it's certainly more limiting insofar as privs go. Also remember that things like PowerShell also have WMI and other major server impacting capabilities and can cause quite a bit of damage even by accident. As with all ad hoc programming, someone needs to know what they're doing or they can bring down the enterprise if they have enough privs. Limit privs everywhere.

    The bottom line is, don't believe for a minute that simply disabling xp_CmdShell is going to provide any reasonable level of security. You MUST take other steps to protect your server. You MUST have policies that require good, strong passwords that must be changed every month or two. You MUST limit the number of people that have "SA" privs and those should usually only be trusted DBAs (if not all DBAs can be trusted, give them lesser privs that will still allow them to do their jobs). You MUST have a policy that anyone using other than their own login will be summarily terminated. You MUST limit what the SQL Server login actually has privs to. AND, you must NEVER EVER give an application SA privs. In fact, done correctly, there's really no reason for an application to have more than just PUBLIC privs. Of course, that would require the nearly exclusive use of stored procedures and, since that would kill ORMs, you should limit applications to only Read/Write and certain EXECUTE privs.

    As for me and much to the horror of many of my constituents, I write DBA procs that use xp_CmdShell to call PowerShell and I write application procs that call xp_CmdShell to work with files. BUT, even if I were dead set against the use of xp_CmdShell, I'd still take all of the security steps that I've outline above and more.

  8. I like it your blog. I have read your blog its very attractive and impressive.
    corporate training institutes in chennai corporate training companies in chennai corporate training companies in india corporate training companies in india corporate training in chennai corporate training companies in chennai corporate training in chennai | Angular 2 Corporate Training Angular 2 Corporate Training


  9. I loved the way you discuss the topic great work thanks for the share, Let me share this, Hadoop training in pune