CLR Stored Procedure To Execute Command

I have written a CLR stored procedure to execute command passed to it as parameter. This is code for the CLR stored procedure; I have registered assembly as 'unsafe' in SQL Server

Solution 1:

While permissions might be an issue, you definitely seem to be invoking cmd.exe incorrectly. If you simply call it as:

cmd.exe mkdir folder_name

then you would get the behavior that you are seeing, namely the folder doesn't get created and the process continues without exiting.

You need to call cmd.exe passing in the command to run using the /C command-line switch, which instructs CMD to execute the provided command and then exit. Hence, calling the following:

cmd.exe /C mkdir folder_name

would work as you are expecting. So perhaps you need to use:

Arguments = "/C " + cmd

Also, there is a possibility that you might need another "WindowStyle" and/or other ProcessStartInfo property. If the above doesn't work, I will check what I have used in the past as I have gotten this to work.

P.S. You should use the Sql* types for parameters and return values for SQLCLR methods. So use SqlString instead of string and SqlInt32 instead of int. All of the Sql* types have a .Value property that returns the expected .NET native type. Hence you would use it as follows:

Arguments = "/C " + cmd.Value

Solution 2:

Sounds like an issue with impersonation or rather the lack thereof. SQL CLR doesn't forward your token (credentials) to other applications unless you explicity tell it to. You could try something like:

[SqlProcedure]
public static int ExecuteCommand(string cmd)
{
    int success = 0;
    Impersonate impersonatedUser = new Impersonate();
    try
    {
        EventLog.WriteEntry("MyAppName", "Starting execution " + cmd + " Username: " + System.Security.Principal.WindowsIdentity.GetCurrent().Name + " OR " + Environment.UserName, EventLogEntryType.Information);
        Process p = new Process();
        p.StartInfo = new ProcessStartInfo() { FileName = "cmd.exe", Arguments = cmd, WindowStyle = ProcessWindowStyle.Hidden };
        p.Start();

    }
    catch (Exception ex)
    {
        EventLog.WriteEntry("MyAppName", "Executed command : " + cmd + "  with error : " + ex.Message, EventLogEntryType.Error);
        success = 1;
    }
    finally
    {
        impersonatedUser.Undo();
    }
    return success;
}

Notice there are only a couple of lines different.

Impersonate impersonatedUser = new Impersonate();

this one tells SQL to impersonate the user that is executing the stored procedure. This will fail with SQL Authentication as SQL Authentication isn't actually a windows user.

finally
{
     impersonatedUser.Undo();
}

If you fail to revert the impersonation it can lead to other issues so putting this in the finally block will make the impersonation revert no matter what.

Here is a Microsoft Article on the subject that says a little more about it:

https://msdn.microsoft.com/en-us/library/ms345105.aspx