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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
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
Portuguese 
English