xp_cmdshell
xp_cmdshell is essentially a mechanism to execute arbitrary calls into the system using either the SQL Server context (i.e. the Windows account used to start the service) or a proxy account that can be configured to execute xp_cmdshell using different credentials. Because of its nature, xp_cmdshell is very flexible, actually I would say it is too flexible as it allows users to execute any arbitrary command using the system (or proxy) context without any good way to limit this flexibility, pretty much opening the door for abusing it.
In many cases, people enable xp_cmdshell and grant access to it to non-sysadmin principals in order to perform one or two operations on the system without realizing that the user with access to it can execute any arbitrary command, and in some cases, effectively escalate his/her privileges to sysadmin or even box administrator - obviously a situation that is less than desirable. Xp_cmdshell is really difficult to control effectively, and even auditing its usage may still allow the attacker to abuse its power for some time until the trail of this abuse is found, and at that point the damage may already be done.
Generally speaking, you must avoid using xp_cmdshell, and if possible, you should remove any dependency on it. A good alternative may be to use CLR and create an external access/unsafe assembly (preferably using digital signatures to establish the trust) that executes exclusively the operation needed and under the complete control of the application developer. Among the advantages are:
· Besides the external access/unsafe trust mechanism (i.e. via signatures), there is no dependency on server settings
· Sysadmin can revoke this trust relationship as needed without affecting other applications
· Better granularity and isolation as the non-SQL operation is well defined by the application
But remember that CLR is not a silver bullet, given the level of trust you may be giving the application (perform certain operations on behalf of SQL Server service account or another privileged user in the OS), you need to make sure that the application is well written and doesn’t have security vulnerabilities (such as code injection, buffer overflows, etc.) and that it is well scoped (i.e. it doesn’t allow execution of arbitrary commands or opening arbitrary files in the system, etc).
If CLR is not an option because as a DBA you don’t want to enable CLR on the system then another option would be to create a T-SQL stored procedure with the command that needs to be invoked on the OS and use EXECUTE AS or digital signatures as a mechanism to temporary escalate the caller to a sysadmin member. When using this approach please be sure to avoid any user-controlled input (such as using parameters for the command to be executed); or if you need to programmatically create the command, make sure to validate and escape the input properly.
I am including a few links below as references on SQL CLR and digital signatures. I hope this information will be useful.
· Database Engine .Net Framework programming
· SQL Server 2005 CLR integration blog
o SQL Server 2005: procedure signing demo
· Understanding Execution context
Thanks a lot to Rick Byham, Laurentiu Cristofor, Ruslan Ovechkin, Jack Richins and Sameer Tejani for helping me in writing this entry.
-Raul Garcia
SDE/T
SQL Server Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
Comments
- Anonymous
July 30, 2008
PingBack from http://www.sqlserverfeeds.com/576/xp_cmdshell/ - Anonymous
February 19, 2009
The objective of this article is to help understand and create a CLR function to be executed as an SP - Anonymous
April 06, 2013
In one of your statements, you say "... and even auditing its usage ...". How do you audit the usage of xp_CmdShell? - Anonymous
June 01, 2013
SQL Trace or SQL Server Audit can be used to audit all executes of all procedures - see SCHEMA_OBJECT_ACCESS_GROUP. - Anonymous
June 06, 2017
Thank you Raul, this is most illuminating.