If you have yet to find out
about the little extended stored procedure gem known as “xp_cmdshell”, then
this article is for you. In this article, I will cover what “xp_cmdshell” is
and different things you can use it for. If you already know something about “xp_cmdshell”
then you might still want to browse this article to find out if there are
additional things you might be able do with “xp_cmdshell” that you don’t
already know.
“xp_cmdshell” is an extended
stored procedure provided by Microsoft and stored in the master database. This
procedure allows you to issue operating system commands directly to the Windows
command shell via T-SQL code. If needed the output of these commands will be
returned to the calling routine.
Now not just anyone can run
this extended stored procedure. If you want to execute this extended stored procedure,
you will either need to be a member of the sysadmin role, or have the xp_sqlagent_proxy_account
set up on your SQL Server. If a login executing this extended stored procedure
is a member of the sysadmin role then the submitted command will run under the
security context associated with the SQL Server Service account in which it
runs. If the login executing this procedure is not a member of the sysadmin
role, then the command uses the xp_sqlagent_proxy_account login security
context for determining whether operating system commands can and cannot be
run. If there is no xp_sqlagent_proxy_account then using this procedure will
fail for all users not in the sysadmin role.
On the surface being able to
run this extended stored procedure does not seem like much, but if the
MSSQLSERVER service account has local administration rights then you can use
this extended stored procedure to perform any windows operating system
command. Therefore, under this circumstance the xp_cmdshell can create quite a
security hole. Saying that, you need to be careful how you set up your
environment to make sure someone does not corrupt your system by using the
almighty powerful xp_cmdshell extended stored procedure. So let’s look at
different examples of ways to use xp_cmdshell.
Let’s start out with a
simple directory search example. Say you do not have access to the physical
SQL Server machine, but you want to see all the files in the “C:temp”
directory that have a “.sql” extension. You can do this by issuing the
following command:
exec master.dbo.xp_cmdshell 'dir c:temp*.sql'
Now that wasn’t too exciting,
so let’s come up with a more useful example. Imagine you are in the process of
building a new SQL Agent job using Enterprise Manager. To organize the output
from all the steps in this new job, you want the output of each step to go into
a common directory on the server, where each step has its own output file. The
only problem is the new directory does not exist. Assume you do not have
access to the server, or do not want to walk down the hall to the computer room
to create this new directory. No problem you can just enter the following
command in Query Analyzer from the privacy of your office to accomplish
creating the new directory:
exec master.dbo.xp_cmdshell 'mkdir "c:tempSQL Agent Outputnew_job"'
As you can see, it is very
easy to execute DOS commands using xp_cmdshell. So can xp_cmdshell also run
executables? The answer is YES! Suppose you have a DTS Package that you want
to run via a stored procedure. There is no “sp_executeDTS” stored procedure,
but you can use the following xp_cmdshell command to run your DTS package:
exec master.dbo.xp_cmdshell 'dtsrun -E -Sserver1 -N"Export Invoices"'
This command executed the “dtsrun”
executable with the necessary parameters to run the “Export Invoices” DTS
package. Are you starting to see how the xp_cmdshell extented store procedure
can provide you with some alternatives and also expands the functionality you
can build into a T-SQL script.
For my last example, suppose
you have a T-SQL script stored on a hard drive that you would like to execute
via a number of different stored procedures. This T-SQL script does a number
of T-SQL commands and you what those same commands to be executed in many
different stored procedures. In addition, the T-SQL script is generated by some
other organization on a daily basis, so you are unable to include the lines of
code in each stored procedure, without changing the stored procedures daily. Therefore,
you want to use xp_cmdshell to execute the T-SQL script. To perform this execution
you perform the following command in each of your stored procedures:
exec master.dbo.xp_cmdshell 'osql -E -Sserver1 -i c:tempnightly.sql'
Here I have used xp_cmdshell
to execute the SQL Server osql utility to process the T-SQL commands contained
in the script file “c:tempnightly.sql”. See how easy it is to execute a
T-SQL batch script via a stored procedure using the xp_cmdshell extended stored
procedure!
If you use xp_cmdshell in a
script, you need to be aware that if the operating system command fails, your
script will not detected that the error occurred. If you want to perform
something differently if the operating system command fails then you will need
to code your xp_cmdshell execution so you can capture the error. Let me
demonstrate what I am talking about.
Say you want to copy an operating system file before you
perform some work, but if the operating system file you want to copy does not
exist to copy you do not want to perform the additional work. If you execute xp_cmdshell like so:
exec master.dbo.xp_cmdshell 'copy c:tempdoesnotexist.txt c:tempworkfile.txt' Print 'Copy worked now we can do some more stuff.' ...
The “Print” statement above
will be executed regardless of whether the file to be copied exists or not. Therefore,
if you need to control your logic based on whether or not the “copy” command
actually worked, then you should code your xp_command shell execution like
this:
DECLARE @rc int
EXEC @rc = master.dbo.xp_cmdshell ‘copy c:tempdoesnotexist.txt c:tempworkfile.txt’
print @rc
IF @rc <> 0
BEGIN
PRINT ‘Copy Failure Skip work’
END
ELSE
BEGIN
Print ‘Copy worked now we can do some more stuff’
….
END
Here I captured the return
code of the execution of xp_cmdshell in a variable @rc. This allows me to then
check the value held by variable @rc and then I programmatically determine
whether or not some additional code should be run or not.
When you use the extended
stored procedure xp_cmdshell it runs commands in the background. Because of
this, xp_cmdshell “MUST NOT” be used to run programs that require user input.
If you try to execute a program that requires user input, the xp_cmdshell
process will hang. The process hangs because the program is waiting for user
input, but xp_cmdshell will never display the user interface to allow the user
to enter data.
If you write lots of T-SQL
scripts to automate your Database Administration task then you probably already
know about the extended stored procedure xp_cmdshell. This extended stored procedure
allows you additional flexibility to get at operation system resources via
T-SQL, which is extremely valuable for managing your SQL Server environment. If
you have not yet experimented with the power of xp_cmdshell, then take a few
minutes to determine how it allows you to enter command level statements via
T-SQL.
