Olá pessoal,
Boa noite!

CLR ou xp_cmdshell: Qual a melhor forma de executar scripts?

Neste post vou demonstrar como executar scripts PowerShell e do Prompt-DOS (MS-DOS) pelo banco de dados SQL Server utilizando o SQL CLR (C#), um recurso do SQL Server que permite que o banco execute códigos escritos na linguagem de programação C#, do Microsoft .NET Framework, para realizar tarefas que um banco de dados geralmente não conseguiria executar utilizando apenas o Transact-SQL (Sabia mais sobre o CLR acessando o post Introdução ao SQL CLR (Common Language Runtime) no SQL Server).

Atualmente, podemos executar scripts Powershell através de Jobs do SQL Server Agent, mas não é tão prático quanto criar uma Stored Procedure no CLR. Se você não possui uma biblioteca CLR, pode-se executar esses scripts utilizando a perigosa stored procedure xp_cmdshell, que geralmente é um dos primeiros ítens que os DBA’s desabilitam por motivos de segurança nas instâncias, e que permite executar qualquer comando do MS-DOS pelo SQL Server por qualquer usuário da role sysadmin.

No meu entendimento, a forma mais segura de se executar scripts Powershell ou do MS-DOS, é encapsulando os códigos no CLR, para realizar uma tarefa específica ao invés de criar uma SP genérica onde você passe o script e o CLR execute.

O processo de Publicação de uma nova versão é um pouco trabalhoso e exige vários privilégios e conhecimento técnico específico para isso. Pelo processo de publicação exigir que todos os objetos sejam dropados e criados novamente, as permissões dos usuários é perdida, sendo necessário realizar um backup das permissões para reaplicar após a publicação da nova versão. Além disso, nenhuma sessão pode estar utilizando algum objeto do CLR, senão, o Visual Studio não conseguirá dropar o objeto. Isso é necessário para a publicação de qualquer alteração, em qualquer objeto do CLR.

Por este motivos, acredito que não é qualquer usuário que irá realizar essa atividade, e o processo de publicação deverá ter uma janela disponível acordada para isso, o que é bem diferente de executar um script através da xp_cmdshell, onde qualquer usuário sysadmin pode executar qualquer comando, sem que ninguém tome conhecimento.

Além disso, caso o usuário alegue que é preciso executar a xp_cmdshell para realizar alguma atividade, ele precisará ser adicionado na role sysadmin ou ter acesso de CONTROL em toda a instância. Já para ter acesso a uma procedure do CLR, que execute esse mesmo código PowerShell ou MS-DOS, encapsulado como uma Stored Procedure específica para realizar alguma determinada ação, basta conceder o privilégio de EXECUTE nessa SP para o usuário.

Como executar scripts MS-DOS pelo SQL Server

Como já comentei na introdução acima, vou disponibilizar o código-fonte em C# para a execução de scripts MS-DOS pelo banco de dados SQL Server através do SQL CLR. Mas não recomendo a utilização desse código para criar uma SP genérica, onde o usuário passe o script por parâmetro e o CLR execute, senão, não faria sentido criar uma procedure do CLR para isso, bastaria habilitar o xp_cmdshell.

O código-fonte da classe Retorno, que utilizo para exibir alertas e mensagens de erro para o banco de dados pelo CLR pode ser visualizado no post SQL Server – Como enviar avisos e mensagens de erro para o banco pelo CLR (C#)

Neste exemplo, vou demonstrar como executar uma query de forma assíncrona, executando-a utilizando o sqlcmd.exe para isso:

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

Utilizando a biblioteca Process, você pode executar qualquer comando MS-DOS, como dir, cp, del e executar arquivos binários (Ex: sqlcmd.exe, powershell.exe, etc)

O parâmetro UseShellExecute indica se o shell do sistema operacional que deve iniciar o processo ou não (nesse caso, quem executa é o próprio executável – sqlserv.exe). Caso você informe TRUE nesse parâmetro, o diretório inicial tende a ser diferente. Além disso, ao permitir que o shell do SO execute o processo, você não irá conseguir capturar o retorno dos comandos executados ou as mensagens de erro retornadas.

Como executar scripts PowerShell pelo SQL Server

Assim como fiz com a execução de scripts MS-DOS, vou disponibilizar o código-fonte em C# para a execução de scripts PowerShell pelo banco de dados SQL Server através do SQL CLR. Mas não recomendo a utilização desse código para criar uma SP genérica, onde o usuário passe o script por parâmetro e o CLR execute, senão, não faria sentido criar uma procedure do CLR para isso, bastaria habilitar o xp_cmdshell.

Em breve, farei vários posts criando SP’s e funções para executar determinadas tarefas utilizando scripts PowerShell e MS-DOS, eles irão utilizar as SP’s deste post.

Como vocês podem observar no código-fonte abaixo, vou utilizar a biblioteca Process novamente, assim como fiz no exemplo acima, mas desta vez, vou criar arquivos com nomes aleatórios (Guid.NewGuid()) com a extensão .ps1 (tem que ser nessa extensão) contendo os scripts PowerShell que desejo executar e vou utilizar sempre o binário powershell.exe para executar esses arquivos 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;

}

No exemplo acima, estou retornando mensagens de alerta e erros e atribuindo a variáveis de modo que eu possa utilizar essas informações retornadas.

Como já citei na parte de execução de scripts do MS-DOS, para que seja possível recuperar essas informações, é necessário que o parâmetro UseShellExecute seja definido como FALSE, ou seja, executado pelo executável do SQL Server, para que assim ele tenha controle sobre o processo. Se você utilizar o parâmetro TRUE, quem executa o processo é o shell do sistema operacional, e o CLR não terá controle aos dados de output do processo.

O código-fonte da classe Retorno, que utilizo para exibir alertas e mensagens de erro para o banco de dados pelo CLR pode ser visualizado no post SQL Server – Como enviar avisos e mensagens de erro para o banco pelo CLR (C#)

Espero que tenham gostado deste post. Em breve, farei novos posts utilizando os conceitos vistos aqui e seria legal já ter um post para servidor de base para isso.

Qualquer dúvida, deixem aqui nos comentários.
Abraço!

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

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