2 thoughts on “Code Execution in SQL Server via Fileless CLR-based Custom Stored Procedures”

Share this…

Recently I was given the task of performing command execution on a compromised MSSQL server with the following restrictions:

  1. No use of the xp_cmdshell stored procedure.
  2. No writing anything to disk.

These restrictions matched those of a recent pentest that a member of my team, Lee Christensen (@tifkin_), was involved in. Through my research I found that there were basically two means to achieve this objective. The first is through the use of SQL Agent jobs, as documented here. The other option was to create a custom stored procedure using the Common Language Runtime (CLR), which would allow for the execution of code from any of the supported programming languages, such as C#. While both methods require the attacker to have the sysadmin role, each option also has their own unique restrictions. The SQL Agent method requires that the SQL Agent service be active (which it is not by default), and that the SQL Agent service account has the necessary privileges required for the command to execute successfully. The CLR stored procedure method requires the ability to create custom stored procedures, the ability to enable the use of CLR on the SQL Server, and requires that the SQL Server service account has the necessary permissions for the command/code to be executed. I also initially believed that that the stored procedure had to be available externally as a DLL file, and then loaded into the SQL Server for execution, thus failing the second restriction. This turned out not to be the case.

After successfully testing the SQL Agent job method via the CmdExec system, I was informed that tifkin_ had managed to bypass the need for a DLL file by loading the byte stream directly into the SQL server in a previous attack. I set out to replicate his work using my own research and methods. At this point the only reference I had seen to malicious custom stored procedures was in the PowerUpSQL command Create-SQLFileXpDll. This command has the restriction of using DLL files, and was therefore not suitable for this exercise. After some research, I found an invaluable StackOverflow article with the information I was seeking here. It turns out that Visual Studio will output a file that contains all the information necessary to create a CLR stored procedure without ever having to touch the disk of the victim SQL Server. Awesome!

The following are instructions you can follow to create your own assembly code for loading into a SQL Server:

    • Install Visual Studio along with the SQL Server Data Tools. For my own project I used Visual Studio Express 2013, since the server I was attacking was SQL Server 2012.
    • Create a new SQL Server project.
    • Under Project Settings, change the Permission Level to UNSAFE. Make sure the language is C#.
    • Add a SQL CLR C# Stored Procedure item. This will provide you with a template.
    • Add your own C# code to the template. For example, here is what my proof of concept code looked like, which created a text file in the c:\temp directory:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void SqlStoredProcedure1 ()
{
System.IO.File.AppendAllText("c:\\temp\\test.txt", "this is a test");
}
};

    • Once the code is ready, go ahead and build it, then browse the output folder. There should be a dacpac file within the bin directory. Extract the dacpac file.
    • Within the extracted contents of the dacpac file is a file named mode.sql. Open this file in a text editor. You will find the two instructions we need to create the stored procedure. The first loads the assembly, and appears as such:

CREATE ASSEMBLY [adduser] AUTHORIZATION [dbo] FROM 0x4[...snip...] WITH PERMISSION_SET = UNSAFE;

    • The second instruction will create a stored procedure from the loaded assembly, as appears as such:

CREATE PROCEDURE [dbo].[SqlStoredProcedure1] AS EXTERNAL NAME [adduser].[StoredProcedures].[SqlStoredProcedure1];

Now that we have the necessary Common Intermediate Language (CIL) instructions that form the assembly, we can go ahead and begin the attack. First, make sure that whichever account is connecting to the SQL Server has sysadmin privileges, and that the SQL Server service account has the privileges needed to execute your code. The first command to execute sets the SQL Server to allow for CLR instructions to be executed, and is as follows:
sp_configure @configname=clr_enabled, @configvalue=1
If this command executes successfully, then you can go ahead and execute the two previously created commands, in the order listed above. This will load the assembly into memory and create a stored procedure from that assembly. Finally, go ahead and execute the stored procedure using the following command:
EXEC [dbo].[SqlStoredProcedure1];
Assuming that all commands were successful, you should now see the results of your executed code. Amazing! There’s a lot more you can do with this than simple file creation, obviously. There are even ways to pass parameters into your code from the EXEC SQL statement. In my next post I will release a weaponized version of this technique that is capable of running any command of an attacker’s choosing.

In summary, it is not enough to simply disable the xp_cmdshell extended stored procedure on a SQL Server. The ability to create stored procedures must also be locked down, otherwise an attacker can create their own stored procedure that can natively execute C#, all using transactional SQL.

Source:https://sekirkity.com