How to execute a DOS command when xp_cmdshell is disabled in SQL Server

Problem

For security reasons many sites disable the extended stored procedure xp_cmdshell, which is used to run DOS commands or executables. When you really have to run a DOS command or an executable from a stored procedure how can you get around this limitation without a breakdown in security.

Solution

Disabling xp_cmdshell is pretty much a standard security practice and in SQL Server 2008 it's disabled by default. That's a good idea, because xp_cmdshell allows running a DOS command or executable with the privileges of the SQL Server database engine, creating a "privilege elevation" vulnerability. Hackers have been known to execute such commands as "Format C:" using this security hole.

When SQL Server is running as an administrative user or under the system account anyone able to use xp_cmdshell can pretty much run any program or DOS command. That's a good reason to run SQL Server as a domain user with limited privileges. This limits any hacker to the privileges given to that user. That is still more than you want to let a hacker have access to, but it's better than administrative permissions.

With xp_cmdshell disabled, I tend to run into situations where SQL Server has to do something outside its own environment that just can't be done with T-SQL. The example in this article runs the DOS attrib command, which changes attributes on files. I use it to make certain input files read-only after they've been processed. With xp_cmdshell available I'd execute a command like this:

exec xp_cmdshell 'attrib "c:\temp\foo.bar" +r'

The +r asks that the read-only attribute be turned on.

To view the attributes of a file at a CMD prompt execute the command without any options like this:

c:\temp>attrib foo.bar
--the output would look like this
A R C:\temp\foo.bar

The A signifies the Archive attribute, R the Read-Only attribute. There are also S for system and H for hidden attributes for each file. The absence of the letter shows that S and H are not set.

To execute the attrib command securely I created a custom SQLCLR stored procedure dedicated to the task. I call my procedure file_attrib_dos_cmd and I built it with a Visual Studio 2010 SQLCLR project. I start by creating the project and selecting a database to connect to with the "Add Database Reference" dialog. I've covered creating stored procedures before in this article Writing to an operating system file using the SQL Server SQLCLR. I then add the stored procedure to the project with the menu command "Project/Add Stored Procedure..." and give it the same name file_attrib_dos_cmd. To allow the execution of the Process. Start framework method the project must be marked as "UnSafe" in the Database tab of the project properties. An unsafe SQLCLR procedure is no more "UnSafe" than an extended stored procedure and that's what file_attrib_dos_cmd replaces.

The method checks to be sure that the file exists. If it doesn't, it throws a SQL error in a way that works well in T-SQL. It then builds a ProcessStartInfo structure supplying cmd.exe as the name of the command to run. If we wanted to run an executable, it could be run directly, but cmd.exe is the program that implements DOS commands. The arguments are formatted for the attrib command and the process is started and the procedure waits for the command to complete. Executing the procedure doesn't produce any output. Here's a typical command:

A more complex alternative would have been to write a SQLCLR procedure that made the attribute changes directly. However, that would have been more complex and this method is easily extended to other commands as well as to running executable programs.

Next Steps

Disable xp_cmdshell if at all possible

Use SQLCLR procedures that are very specific to your need to accomplish

Also, use a unique name for the CLR procedure instead of a generic name like "xp_cmdshell2"

Last Update: 2010-08-11

About the author

Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.

That's fine but now we have someone building a CLR that is subject to DOS Injection. I could certainly be wrong (I'm not a programmer) but I don't see anything in the CLR that prevents an attacker from including a "&" or "&&" in the mix.

I think one advantage of turning it off is that if it is off it forces the DBA or whoever to see if there is another approach without having to use xp_cmdshell. I have see that once this is turned on that people sometimes go overboard and use it for more than they really need to.

I do agree if someone already has broken through your security and gains access to SQL Server as "sa" it doesn't do anything.

Everyone realizes that if an attacker gets in as "SA", then having xp_CmdShell turned off doesn't prevent the attacher from turning it on, RIGHT? And everyone also realizes that if a hacker can't get in as "SA" and you weren't dumb enough to give someone with lower privs the right to run xp_CmdShell, then a hacker can't use it even if it's turned on, RIGHT?

So, my question to everyone is... why do you waste time turning it off? It doesn't prevent any security problems. About the only thing turning it off does is give a false sense of security. Turning it off is a complete waste of time.

However, I'd prefer to have the SSIS package kicked off by a job to get the reporting and notification that go with SQL Agent, so you might try and negotiate for permissions for sp_start_job. I'd guess that the DBA's would prefer that also.

Your article caught my attention early this morning. I can't use xp_cmdshell (sys.admin), so I went to Agent Job, but to kick that off I need sp_start_job (also sys.admin or user on msdb). Here's what I'd like to do with your solution and I was wondering if you'd give it some thought and let me know if it is possible before I chase the rabbit.

Unless you take additional steps, the caller executing the SQLCLR stored procedure only needs execute permission on the procedure and the procedure will be executed as the service account that is running SQL Server, so it need Windows privilages to do what . That's always worked for me. I've restricted access to the SQLCLR sproc and done what I needed.

If you want to require that the user have permission to perform the action than you'll have to add code to check for the permissions. Another way is to write code to Impersionate a particualr windows user and regulate what the proc can do at the Windows level by the permissions granted to that user. In sort, it get's more involved but can be controlled at a fine grain.