Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server – Como executar scripts PowerShell e Prompt-DOS (MS-DOS) utilizando CLR (C#)

Post Views 4,299 views
Reading time 5 minutes

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:

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.

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