Hey guys,
Goodnight!

CLR or xp_cmdshell: What is the best way to run scripts?

In this post I will demonstrate how to execute PowerShell and Prompt-DOS (MS-DOS) scripts through the SQL Server database using SQL CLR (C#), a feature of SQL Server that allows the database to execute codes written in the C# programming language, from the Microsoft .NET Framework, to perform tasks that a database would generally not be able to perform using only Transact-SQL (Learn more about the CLR by accessing the post Introduction to SQL CLR (Common Language Runtime) in SQL Server).

Currently, we can run Powershell scripts through SQL Server Agent Jobs, but it is not as practical as creating a Stored Procedure in the CLR. If you don't have a CLR library, you can run these scripts using the dangerous stored procedure xp_cmdshell, which is usually one of the first items that DBA's disable for security reasons in the instances, and which allows any MS-DOS command to be executed through SQL Server by any user of the sysadmin role.

In my understanding, the safest way to execute Powershell or MS-DOS scripts is by encapsulating the codes in the CLR, to perform a specific task instead of creating a generic SP where you pass the script and the CLR executes it.

The process of publishing a new version is a bit laborious and requires several privileges and specific technical knowledge to do so. Because the publishing process requires that all objects be dropped and created again, user permissions are lost, making it necessary to make a backup of permissions to reapply after publishing the new version. Furthermore, no session can be using any CLR object, otherwise Visual Studio will not be able to drop the object. This is necessary for publishing any changes to any CLR object.

For this reason, I believe that not just any user will perform this activity, and the publishing process must have an available window agreed upon for this, which is very different from executing a script through xp_cmdshell, where any sysadmin user can execute any command, without anyone being aware.

Furthermore, if the user claims that it is necessary to run xp_cmdshell to perform some activity, they will need to be added to the sysadmin role or have CONTROL access to the entire instance. To have access to a CLR procedure, which executes the same PowerShell or MS-DOS code, encapsulated as a specific Stored Procedure to perform a certain action, simply grant the EXECUTE privilege in that SP to the user.

How to run MS-DOS scripts from SQL Server

As I mentioned in the introduction above, I will make the C# source code available for executing MS-DOS scripts through the SQL Server database through the SQL CLR. But I don't recommend using this code to create a generic SP, where the user passes the script as a parameter and the CLR executes it, otherwise, it wouldn't make sense to create a CLR procedure for this, it would be enough to enable xp_cmdshell.

The source code of the Retorno class, which I use to display alerts and error messages to the database via the CLR, can be viewed in the post SQL Server – How to send warnings and error messages to the database using the CLR (C#)

In this example, I will demonstrate how to execute a query asynchronously, executing it using sqlcmd.exe to do so:

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);
        }
    }
}

Using the Process library, you can execute any MS-DOS command, such as dir, cp, del and execute binary files (Ex: sqlcmd.exe, powershell.exe, etc.)

The UseShellExecute parameter indicates whether the operating system shell should start the process or not (in this case, the executable itself is the one executing it – sqlserv.exe). If you enter TRUE in this parameter, the home directory tends to be different. Furthermore, by allowing the OS shell to execute the process, you will not be able to capture the feedback of executed commands or returned error messages.

How to run PowerShell scripts from SQL Server

Just as I did with executing MS-DOS scripts, I will make the C# source code available for executing PowerShell scripts through the SQL Server database through the SQL CLR. But I don't recommend using this code to create a generic SP, where the user passes the script as a parameter and the CLR executes it, otherwise, it wouldn't make sense to create a CLR procedure for this, it would be enough to enable xp_cmdshell.

Soon, I will make several posts creating SP's and functions to perform certain tasks using PowerShell and MS-DOS scripts, they will use the SP's in this post.

As you can see in the source code below, I will use the Process library again, as I did in the example above, but this time, I will create files with random names (Guid.NewGuid()) with the .ps1 extension (it has to be that extension) containing the PowerShell scripts that I want to execute and I will always use the powershell.exe binary to execute these powershell files.

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;

}

In the example above, I am returning warning and error messages and assigning them to variables so that I can use this returned information.

As I already mentioned in the MS-DOS script execution part, in order to retrieve this information, the UseShellExecute parameter must be set to FALSE, that is, executed by the SQL Server executable, so that it has control over the process. If you use the TRUE parameter, the operating system shell runs the process, and the CLR will have no control over the process's output data.

The source code of the Retorno class, which I use to display alerts and error messages to the database via the CLR, can be viewed in the post SQL Server – How to send warnings and error messages to the database using the CLR (C#)

I hope you liked this post. Soon, I will make new posts using the concepts seen here and it would be cool to already have a base server post for this.

If you have any questions, leave them here in the comments.
Hug!

sql server clr run powershell ms-dos prompt script shell clr c# csharp

sql server clr run powershell ms-dos prompt script shell clr c# csharp