Custom Assemblies
In the previous section, we covered two techniques
for gaining code execution from stored procedures.
In this section, we are going to explore a different technique
that also allows us to get arbitrary code execution,
this time using managed code.
Before we begin, let’s discuss this technique.
If a database has the TRUSTWORTHY property set,
we can use the CREATE ASSEMBLY statement,
to import a managed DLL as an object
inside the SQL server
and execute methods within it.
To take advantage of this, we'll need to perform several steps.
To begin, we will create a managed DLL
by creating a new "Class Library (.NET Framework)” project
As part of the C# code, we'll create a method,
that must be marked as a stored procedure.
We can implement any method we want inside the class.
In this example, we are going to write code that starts a command prompt
and executes the command given
inside the execCommand argument.
We'll also return the result so our C# console application can print it.
The Process class is used to start a process,
while allowing us to supply arguments through the StartInfo property.
We'll use the FileName,
and Arguments properties,
of StartInfo
to specify “cmd.exe”
and the command to execute respectively.
Additionally, we set UseShellExecute to “false”
to ensure that the command prompt is created directly
from cmd.exe.
We also set RedirectStandardOutput to “true”
so the output from the command prompt
does not get printed to the console,
but stored in a pipe instead.
Calling the Start method,
creates the process and executes the command supplied
in the execCommand argument.
Any output generated as a result of the command line input
is not sent to the console.
However, we can retrieve it using the Pipe property
of the SqlContext class.
The Pipe property is actually an embedded object instantiated from the SqlPipe class
which allows us to record SQL data
and return it to the caller.
We will use a combination of SendResultsStart,
SendResultsRow,
and SendResultsEnd
to start recording,
record data,
and stop recording
respectively.
These APIs record data into an SqlDataRecord
type object.
To send the output from the command prompt to the SQL record,
we copy the contents of the Process object
StandardOutput property
into the record.
It is then returned as part of the result set from the SQL query.
Finally, we force the cmd.exe process to wait
until all actions are completed,
and subsequently we close it.
We'll compile the DLL
and copy it to our local Tools folder.
This is the assembly that we're going to load into the SQL server and then execute.
The next step is to find a suitable target database inside the SQL server,
since we can only create a procedure from an assembly
if the TRUSTWORTHY property is set.
Recall that by default, only the msdb database has this property enabled,
but custom databases may use it as well.
With this in mind, we are going to target msdb.
Creating a stored procedure from an assembly is not allowed by default.
This is controlled through the CLR Integration setting,
which is disabled by default.
Luckily, we can enable it with sp_configure
and the clr enabled option.
Microsoft SQL server 2017 introduced an additional security mitigation known as
CLR strict security
This mitigation only allows signed assemblies by default.
CLR strict security can be disabled through sp_configure
with the clr strict security option.
In summary, we must execute these SQL statements before we start
creating the stored procedure from an assembly.
With all the security considerations taken care of,
we can import the assembly with the CREATE ASSEMBLY statement,
which has this prototype.
We must supply a custom assembly name,
a file location,
and specify the PERMISSION_SET
to be UNSAFE
to allow execution of unsigned .NET code.
In this example, we are going to copy the compiled assembly,
to dc01
in the C:\Tools.
While this is not something we’d use in a real-world scenario,
it will help us understand the technique..
Later in the section, we'll improve our technique
and learn how to avoid this step.
Once the DLL has been imported, we need to create a procedure
based on the cmdExe method
with the CREATE PROCEDURE statement.
To do so, we first specify the “CREATE PROCEDURE” statement
followed by the name we want to assign to our custom procedure,
followed by the arguments.
We'll then specify the function name in our newly imported assembly,
which will be executed when our procedure is invoked.
The last half of the SQL query starts with the AS keyword.
Here we specify the location of the C# method
to create a procedure from.
This is marked by the EXTERNAL NAME prefix
since it is non-native.
As the final step,
we must invoke the newly-created procedure
and supply an argument.
Let’s compile our application and try it out.
This proves that we obtained code execution through our custom assembly.
Very Nice!
It's not possible to call CREATE ASSEMBLY on the same assembly multiple times
without removing the previous one.
Instead, the DROP ASSEMBLY statement must be used to drop it.
In addition, an assembly cannot be dropped
if a procedure that requires it has been created.
In that case, the DROP PROCEDURE statement must be used first.
Let’s build this and run it before we continue.
In our technique to get code execution from a custom assembly,
we initially copied the compiled assembly to the hard drive
of the SQL server,
which is not realistic.
Let’s explore a better alternative.
It is possible to directly embed the assembly
in the CREATE ASSEMBLY SQL query.
Instead of specifying the file path in the FROM clause
we can supply the binary content of the assembly
as a hex string.
To convert the assembly into a hexadecimal string,
we use this small PowerShell script.
With the assembly converted to a hexadecimal string,
we only have to update our CREATE ASSEMBLY statement.
We have already cleaned up our procedure and assembly on the SQL server
so we’ll build our application.
Now we can execute the query with the embedded assembly
an attempt code execution.
Once more, we have arbitrary code execution
but this time
without having to write an assembly to disk
on the target!
In this section, we covered how to gaincode execution
on the SQL server host operating system
using a custom assembly,
which allows us to reuse our previous C# code.