Hola, chicos,
¡Buenas noches!

CLR o xp_cmdshell: ¿Cuál es la mejor manera de ejecutar scripts?

En este post demostraré cómo ejecutar scripts PowerShell y Prompt-DOS (MS-DOS) a través de la base de datos SQL Server usando SQL CLR (C#), una característica de SQL Server que permite a la base de datos ejecutar códigos escritos en el lenguaje de programación C#, del Microsoft .NET Framework, para realizar tareas que una base de datos generalmente no podría realizar usando solo Transact-SQL (Obtenga más información sobre el CLR accediendo al post Introducción a SQL CLR (Common Language Runtime) en SQL Server).

Actualmente, podemos ejecutar scripts de Powershell a través de trabajos del Agente SQL Server, pero no es tan práctico como crear un procedimiento almacenado en el CLR. Si no tiene una biblioteca CLR, puede ejecutar estos scripts utilizando el peligroso procedimiento almacenado xp_cmdshell, que suele ser uno de los primeros elementos que los DBA deshabilitan por razones de seguridad en las instancias, y que permite que cualquier usuario con el rol de administrador de sistemas ejecute cualquier comando de MS-DOS a través de SQL Server.

Según tengo entendido, la forma más segura de ejecutar scripts de Powershell o MS-DOS es encapsulando los códigos en el CLR, para realizar una tarea específica en lugar de crear un SP genérico donde se pasa el script y el CLR lo ejecuta.

El proceso de publicar una nueva versión es un poco laborioso y requiere varios privilegios y conocimientos técnicos específicos para hacerlo. Debido a que el proceso de publicación requiere que todos los objetos se eliminen y se vuelvan a crear, los permisos de usuario se pierden, por lo que es necesario realizar una copia de seguridad de los permisos para volver a aplicarlos después de publicar la nueva versión. Además, ninguna sesión puede utilizar ningún objeto CLR; de lo contrario, Visual Studio no podrá eliminar el objeto. Esto es necesario para publicar cualquier cambio en cualquier objeto CLR.

Por esta razón, creo que no cualquier usuario realizará esta actividad, y el proceso de publicación debe tener una ventana disponible acordada para ello, lo cual es muy diferente a ejecutar un script a través de xp_cmdshell, donde cualquier usuario sysadmin puede ejecutar cualquier comando, sin que nadie se dé cuenta.

Además, si el usuario afirma que es necesario ejecutar xp_cmdshell para realizar alguna actividad, será necesario agregarlo a la función de administrador del sistema o tener acceso de CONTROL a toda la instancia. Para tener acceso a un procedimiento CLR, que ejecuta el mismo código PowerShell o MS-DOS, encapsulado como un Procedimiento Almacenado específico para realizar una determinada acción, simplemente otorgue el privilegio EXECUTE en ese SP al usuario.

Cómo ejecutar scripts de MS-DOS desde SQL Server

Como mencioné en la introducción anterior, haré que el código fuente de C# esté disponible para ejecutar scripts de MS-DOS a través de la base de datos de SQL Server a través de SQL CLR. Pero no recomiendo usar este código para crear un SP genérico, donde el usuario pasa el script como parámetro y el CLR lo ejecuta, de lo contrario no tendría sentido crear un procedimiento CLR para esto, bastaría con habilitar xp_cmdshell.

El código fuente de la clase Retorno, que uso para mostrar alertas y mensajes de error en la base de datos a través del CLR, se puede ver en la publicación. SQL Server: cómo enviar advertencias y mensajes de error a la base de datos usando CLR (C#)

En este ejemplo, demostraré cómo ejecutar una consulta de forma asincrónica, ejecutándola usando sqlcmd.exe para hacerlo:

using System;
using System.Data.SqlTypes;
using System.Diagnostics;
using Bibliotecas.Model;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void stpExecuta_Query_Async(SqlString Ds_Servidor, SqlString Ds_Query)
    {

        try
        {

            if (Ds_Servidor.IsNull)
                Retorno.Erro("Favor informar o servidor onde que você deseja executar a query");

            if (Ds_Query.IsNull)
                Retorno.Erro("Favor informar a query que você deseja executar");


            var scriptProc = new Process
            {
                StartInfo =
                {
                    FileName = @"sqlcmd.exe", // Arquivo ou comando que será executado
                    UseShellExecute = false, // Indica se deve usar o shell do sistema operacional para iniciar o processo
                    Arguments = "-S " + Ds_Servidor.Value + " -U " + Servidor.Ds_Usuario + " -P " + Servidor.Ds_Senha + " -Q \"" + Ds_Query.Value + "\"",
                    CreateNoWindow = true // O processo será executado sem criar uma nova janela
                }
            };

            scriptProc.Start();
            
        }
        catch (Exception e)
        {
            Retorno.Erro("Erro : " + e.Message);
        }
    }
}

Usando la biblioteca de procesos, puede ejecutar cualquier comando de MS-DOS, como dir, cp, del y ejecutar archivos binarios (por ejemplo: sqlcmd.exe, powershell.exe, etc.)

El parámetro UseShellExecute indica si el shell del sistema operativo debe iniciar el proceso o no (en este caso, el ejecutable en sí es quien lo ejecuta: sqlserv.exe). Si ingresa VERDADERO en este parámetro, el directorio de inicio tiende a ser diferente. Además, al permitir que el shell del sistema operativo ejecute el proceso, no podrá capturar la respuesta de los comandos ejecutados ni los mensajes de error devueltos.

Cómo ejecutar scripts de PowerShell desde SQL Server

Tal como hice con la ejecución de scripts de MS-DOS, haré que el código fuente de C# esté disponible para ejecutar scripts de PowerShell a través de la base de datos de SQL Server a través de SQL CLR. Pero no recomiendo usar este código para crear un SP genérico, donde el usuario pasa el script como parámetro y el CLR lo ejecuta, de lo contrario no tendría sentido crear un procedimiento CLR para esto, bastaría con habilitar xp_cmdshell.

Próximamente haré varias publicaciones creando SP's y funciones para realizar ciertas tareas usando scripts de PowerShell y MS-DOS, usarán los SP's en esta publicación.

Como puede ver en el código fuente a continuación, usaré la biblioteca Process nuevamente, como lo hice en el ejemplo anterior, pero esta vez crearé archivos con nombres aleatorios (Guid.NewGuid()) con la extensión .ps1 (tiene que ser esa extensión) que contiene los scripts de PowerShell que quiero ejecutar y siempre usaré el binario powershell.exe para ejecutar estos archivos de powershell.

public static string ExecutaScriptPowerShell(string dsScript, string servidor = "")
{

    var idArquivo = Guid.NewGuid();
    var arquivo = $@"C:\Temp\{idArquivo}.ps1";


    using (var fileStream = new FileStream(arquivo, FileMode.Create))
    {
        using (var sw = new StreamWriter(fileStream, Encoding.GetEncoding("UTF-8")) { NewLine = "\r\n" })
        {
            sw.Write(dsScript);
        }
    }


    string argumentos;
    if (string.IsNullOrEmpty(servidor))
    {
        argumentos = @"-ExecutionPolicy ByPass -File """ + arquivo + @""""; // -ExecutionPolicy Unrestricted
    }
    else
    {
        // Para funcionar, precisa executar antes no powershell da máquina destino: Enable-PSRemoting –Force
        argumentos = @"-ExecutionPolicy ByPass -Command { Invoke-Command -ComputerName " + servidor + @" -FilePath """ + arquivo + @""" }";
    }


    string output;
    string msgErro;

    using (var scriptProc = new Process { StartInfo = { FileName = "powershell", Arguments = argumentos, UseShellExecute = false, RedirectStandardOutput = true, RedirectStandardError = true, StandardOutputEncoding = Encoding.GetEncoding(850), CreateNoWindow = true } })
    {

        scriptProc.Start();
        scriptProc.WaitForExit(1000 * 60); // 60 segundos

        File.Delete(arquivo);
        //Retorno.Mensagem(argumentos);

        output = scriptProc.StandardOutput.ReadToEnd();

        msgErro = scriptProc.StandardError.ReadToEnd();
    }


    if (!string.IsNullOrEmpty(msgErro))
        output += "\nERRO: " + msgErro;


    return output;

}

En el ejemplo anterior, devuelvo mensajes de advertencia y error y los asigno a variables para poder usar esta información devuelta.

Como ya mencioné en la parte de ejecución del script MS-DOS, para poder recuperar esta información, el parámetro UseShellExecute debe estar establecido en FALSE, es decir, ejecutado por el ejecutable de SQL Server, para que tenga control sobre el proceso. Si utiliza el parámetro TRUE, el shell del sistema operativo ejecuta el proceso y CLR no tendrá control sobre los datos de salida del proceso.

El código fuente de la clase Retorno, que uso para mostrar alertas y mensajes de error en la base de datos a través del CLR, se puede ver en la publicación. SQL Server: cómo enviar advertencias y mensajes de error a la base de datos usando CLR (C#)

Espero que te haya gustado esta publicación. Pronto haré nuevas publicaciones utilizando los conceptos que se ven aquí y sería genial tener ya una publicación en el servidor base para esto.

Si tienes alguna duda déjala aquí en los comentarios.
¡Abrazo!

sql server clr ejecutar powershell ms-dos script de solicitud shell clr c# csharp

sql server clr ejecutar powershell ms-dos script de solicitud shell clr c# csharp