Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

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

MSSQLTips author Andy Novick By:   |   Read Comments (12)   |   Related Tips: More > Functions - User Defined UDF
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.

Here is the code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Diagnostics;
using System.Text;
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void file_attrib_dos_cmd(SqlString DirectoryPath
                                          ,SqlString FileSpec
                                          ,SqlString arguments)
    {
        // Make sure the file exists
        string FullPath = Path.Combine(DirectoryPath.Value, FileSpec.Value);
        FileInfo fi = new FileInfo(FullPath);
        if (!fi.Exists)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = string.Format(
                         "raiserror('File ->{0}<- is not found.',16,1)",
                           FileSpec.Value);
                try { SqlContext.Pipe.ExecuteAndSend(cmd); }
                catch { return; }
            }
        }
        // ProcessStartInfo to run the DOS command attrib
        ProcessStartInfo pPStartInfo = new ProcessStartInfo("cmd.exe");
        pPStartInfo.WorkingDirectory = DirectoryPath.Value;
        pPStartInfo.UseShellExecute = true;
        // quote the file name incase it has spaces
        pPStartInfo.Arguments =string.Format(@" /C attrib "{0}""{1}",  
                                   FullPath, arguments.Value);
        // start a new process and wait for it to exit
        Process p = new Process();
        p.StartInfo = pPStartInfo;        
        p.Start();
        p.WaitForExit();
   
    }
};

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:

exec dbo.file_attrib_dos_cmd 'c:\temp\', 'foo.bar', '+r';
GO 
Command(s) completed successfully.

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: 8/11/2010


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

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Saturday, April 06, 2013 - 10:20:18 PM - Jeff Moden Read The Tip

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.


Monday, January 21, 2013 - 8:32:15 AM - Greg Robidoux Read The Tip

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.


Sunday, January 20, 2013 - 7:11:32 PM - Jeff Moden Read The Tip

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.


Thursday, August 12, 2010 - 7:36:31 AM - Andy Novick Read The Tip
Sounds worth a try. 

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.

 

 


Wednesday, August 11, 2010 - 5:07:01 PM - Tasman120 Read The Tip
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.

TestUser with limited to no permissions on Sql svr: Starts Gui program, click button, start stored procedure, use your solution to execute cmd line: dtexec (SSIS package).

Yes - I could execute SSIS package from GUI program, but business rules would like to use stored procedures to enable portability and support.

Thanks!


Wednesday, August 11, 2010 - 4:55:01 PM - Andy Novick Read The Tip
Good question.

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.

 


Wednesday, August 11, 2010 - 3:23:17 PM - Jeff Moden Read The Tip
Wouldn't it be simplier solution to temporarily enable xp_cmdshell and disable it immediately after dos command execution?

Uh huh... and what kind of privs do you need for that?


Wednesday, August 11, 2010 - 11:34:09 AM - Andy Novick Read The Tip
The listing has been corrected.  The missing characters are now back where they should be.

 


Wednesday, August 11, 2010 - 10:49:37 AM - Andy Novick Read The Tip
There are missing characters at the end of line 41 in the listing.  It should be:

pPStartInfo.Arguments =string.Format(@" /C attrib "{0}""{1}",  
                                   FullPath, arguments.Value);

So there is a missing


space left-brace one right-brace double-quote comma

at the end of the line.  We'll fix ASAP


 


Wednesday, August 11, 2010 - 10:30:25 AM - Andy Novick Read The Tip
Enabling and then disabling xp_cmdshell is simpler.  If you were only going to do this once that might be okay, if it is allowed at all.

When you have to run the command multiple times per day the enable, run, disable cycle isn't really an option.

 


Wednesday, August 11, 2010 - 10:28:45 AM - Tasman120 Read The Tip
This line seems to be missing a quote: pPStartInfo.Arguments =string.Format(@" /C attrib ""{0}""  
                                  
FullPath, arguments.Value);



Wednesday, August 11, 2010 - 9:45:07 AM - MazBros Read The Tip
Wouldn't it be simplier solution to temporarily enable xp_cmdshell and disable it immediately after dos command execution?




 
Sponsor Information