Replacing xp_cmdshell
Introduction:
This document provides various alternatives for xp_cmdshell. Xp_cmdshell is an extended stored procedure which is in Master database of a server. This extended stored procedure takes in a valid DOS command in the form of a string and executes this string at the DOS prompt.
Usage of xp_cmdshell is not suggested in stored procedures or other database objects because it provides low security and also needs admin right for execution.
It’s always good to place execution of DOS commands out of SQL Server.
Existing Scenario
1. Following is the format of the code of Stored Procedure Using Xp_Cmdshell
Some Processing
.
.
.
Build a String
Pass the String to Xp_Cmdshell for execution
Processing Continued
.
.
.
Proposed Scenario(s)
1. Setting Permissions to the extended stored procedure “xp_cmdshell”.
Permissions can be set to the Users/Databases Roles/public for accessing this extended stored procedure.
Comments on this approach:
This approach cannot be used if you want the fix for this issue out of SQL Server.
This approach requires admin rights.
Poor security.
2. Using an intermediate table
Processing
.
.
.
Build a String
Insert the String and other required details into an Intermediate Table (any user can update this table.)
Processing Continued
.
.
.
PARALLEL TO THIS
Create a New Job
Run it for every minute (depending on the requirement).
This job will fetch the string from the above intermediate table and pass it to the CmdExec Job which will execute it. (CmdExec Job is being called by some other job which means it executes completely under the SQL Server control)
Comments on this approach:
This approach cannot be used if you want the fix for this issue out of SQL Server.
This approach requires admin rights.
Poor security.
3. Using sp_OAMethod
Processing
.
.
.
Build a String
Insert the DOS Command, its input parameters and other required details into an Intermediate Table (any user can update this table.)
Processing Continued
.
.
.
PARALLEL TO THIS
Create a New Job
Run it for every minute (depending on the requirement).
This job will fetch the input parameters and DOS Command from the above intermediate table
[Create a DLL addressing each of the DOS commands’ (copy, move, bcp, jview, del and ftp) functionality. This DLL should support IDispatch interface]
Pass the DLL to sp_OACreate Store Procedure in Master DB [sp_OACreate will be accessible to only admin group of users but since it is being run by a job it will be in total control of SQL Server.]
Call the required function (based on the DOS Command) in the DLL through sp_OAMethod and pass the parameters to it.
Comments on this approach:
This approach cannot be used if you want the fix for this issue out of SQL Server.
This approach requires admin rights.
Poor security.
4. Moving xp_cmdshell functionality totally out of SQL Server
To move the xp_cmdshell functionality totally out of SQL Server we need to recode in .NET, etc., all the stored procedures and jobs that in turn use the xp_cmdshell using stored procedures. This is a disadvantage of this approach but ensures high security when compared to other approaches suggested.
Wherever an application is calling these xp_cmdshell using stored procedures, DLL of its application code should be called.
The following Namespaces should be included at the beginning of the .cs file.
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Threading;
using System.Diagnostics;
Following is the code written in C# for writing a DOS string into a batch file and executing it in the DOS shell.
///
/// Executes the DOS commands DOS shell.
///
///
public void execDOSCmd(string command)
{
try
{
Process p = new Process();
ProcessStartInfo psI = new ProcessStartInfo();
string targetDir;
TextWriter tw = new StreamWriter(sLocalPath + "\\" + sCommandFile);
targetDir = string.Format(@sLocalPath);
psI.UseShellExecute = true;
psI.WorkingDirectory = targetDir;
psI.Arguments = command;
psI.FileName = "ExecCmd.bat";
psI.CreateNoWindow = false;
p.StartInfo = psI;
tw.WriteLine(command);
tw.Close();
p.Start();
p.WaitForExit();
p.Close();
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
}
Comments on this approach:
This approach does not require admin rights.
Highly secure.
Strongly suggested.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|