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

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

SQL, sql server, sql server 2008, sql server 2008 R2, Oracle, Oracle Database, Oracle 11g, Oracle 10g, Oracle 12c, MySQL, Firebird, Consultoria, Consultor, Programador, Programação. Desenvolvedor, Analista de Sistemas, DBA, Criação de website, Criação de Sistema Web, Vitória, Vila Velha, Guarapari, Espírito Santo, ES, Consultoria SQL em VItória, Treinamento, Curso, Prestação de serviço, prestar serviço, freelancer, freela, banco de dados, consultoria em banco de dados, consultor de banco de dados

Deixe uma resposta