Hello people,
Good night!
CLR or xp_cmdshell: What is the best way to run scripts?
In this post I will demonstrate how to run PowerShell and Prompt-DOS (MS-DOS) scripts from the SQL Server database using SQL CLR (C #), a feature of SQL Server that allows the database to execute code written in the C # programming language. , from the Microsoft .NET Framework, to perform tasks that a database would not normally be able to perform using Transact-SQL alone. Introduction to SQL Common Language Runtime (CLR) in SQL Server).
Currently, we can run Powershell scripts through SQL Server Agent Jobs, but it's not as practical as creating a Stored Procedure in CLR. If you do not 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 run by SQL Server by any user of the role sysadmin.
As I understand it, the safest way to run Powershell or MS-DOS scripts is to encapsulate the code in the CLR to perform a specific task instead of creating a generic SP where you pass the script and the CLR runs.
The process of Publishing a new version is a bit laborious and requires various privileges and specific technical knowledge to do so. Because the publishing process requires all objects to be dropped and re-created, users' permissions are lost, and permissions must be backed up to reapply after the new version is published. Also, no session can be using any CLR object, otherwise Visual Studio will not be able to drop the object. This is required to post any changes to any CLR object.
For this reason, I believe that it is not any user who will perform this activity, and the publishing process should have an agreed window available for it, which is quite different from running a script through xp_cmdshell, where any sysadmin user can execute any one. command without anyone being aware of it.
In addition, if the user claims that xp_cmdshell needs to be performed to perform any activity, they need to be added to role sysadmin or have CONTROL access throughout the instance. In order to have access to a CLR procedure that runs the same code as PowerShell or MS-DOS, encapsulated as a specific Stored Procedure to perform a certain action, simply grant the EXECUTE privilege on this SP to the user.
How to run MS-DOS scripts through SQL Server
As I mentioned in the introduction above, I will make the C # source code available for running MS-DOS scripts through the SQL Server database through SQL CLR. But I do not recommend using this code to create a generic SP, where the user passes the script by parameter and the CLR execute, otherwise it would not make sense to create a CLR procedure for that, just enable xp_cmdshell.
The source code of the Return class, which I use to display alerts and error messages to the database by CLR can be viewed in the post. SQL Server - How to send warnings and error messages to the bank through CLR (C #)
In this example, I will demonstrate how to execute a query asynchronously by executing it using sqlcmd.exe for this:
|
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); } } } |
Using the Process library, you can run any MS-DOS command, such as dir, cp, del, and execute binary files (eg sqlcmd.exe, powershell.exe, etc.).
The UseShellExecute parameter indicates whether the operating system shell that should start the process or not (in this case, the executable is the executable itself - sqlserv.exe). If you enter TRUE in this parameter, the home directory tends to be different. In addition, by allowing the OS shell to perform the process, you will not be able to capture the return of the executed commands or the error messages returned.
How to run PowerShell scripts from SQL Server
As I did with running MS-DOS scripts, I will make the C # source code available for running PowerShell scripts by the SQL Server database through SQL CLR. But I do not recommend using this code to create a generic SP, where the user passes the script by parameter and the CLR execute, otherwise it would not make sense to create a CLR procedure for that, just 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 of this post.
As you can see from the source code below, I will use the Process library again, just as I did in the example above, but this time, I will create files with random names (Guid.NewGuid ()) with the extension .ps1 (must be in this extension) containing the PowerShell scripts I want to run and I will always use the powershell.exe binary to execute these powershell files.
|
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; } |
In the example above, I am returning alert and error messages and assigning variables so that I can use this returned information.
As I mentioned in the MS-DOS script execution part, in order to retrieve this information, the UseShellExecute parameter must be set to FALSE, ie executed by the SQL Server executable, so that it has control over the process. If you use the TRUE parameter, the process executes the operating system shell, and the CLR has no control over the process output data.
The source code of the Return class, which I use to display alerts and error messages to the database by CLR can be viewed in the post. SQL Server - How to send warnings and error messages to the bank through CLR (C #)
I hope you enjoyed this post. Soon, I will make new posts using the concepts seen here and it would be nice to already have a base server post for this.
Any questions, leave it 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
