By: Andy Novick | Comments (12) | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > 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"
About the author
Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
