Reverse Engineer SQLCLR assembly in SQL Server

Question

I've come across an undocumented Production server with the Assemblies listed below. Any ideas how to reverse engineer the DBFunctions assembly or reproduce it on another SQL Server instance?

Answer 1

The answer to both how to reverse engineer and/or reproduce them on another SQL Server instance lies in the fact that an assembly in SQL Server is stored as bytes in a table: sys.assembly_files. When you have the bytes, you can deploy on another server instance via:

CREATE ASSEMBLY YourAssemblyName
FROM the-assembly-bytes-from-the-table
WITH PERMISSION_SET = ...

As for reverse engineer you can read the bytes from - for example - a C# console app and then create a FileStream from the bytes and write the FileStream to disk as .dll file:

static void Main(string[] args)
{
  string connString = "server=someServer;database=someDb;uid=<uid>;pwd=<pwd>;";
  string cmdString = "SELECT content FROM sys.assembly_files WHERE assembly_id = 65540 AND 
  file_id = 1";
  string filePath = @"<some-path-and-filename>";

  SqlBytes bytes;

  using (SqlConnection conn = new SqlConnection(connString)) 
  {
    conn.Open();
    using (SqlCommand cmd = conn.CreateCommand()) 
    {
      cmd.CommandText = cmdString;
      cmd.CommandType = System.Data.CommandType.Text;

      SqlDataReader reader = cmd.ExecuteReader();

      reader.Read();
      bytes = reader.GetSqlBytes(0);
    }
  }

  // write the byte stream out to disk
  FileStream bytestream = new FileStream(filePath, FileMode.CreateNew);
  bytestream.Write(bytes.Value, 0, (int)bytes.Length);
  bytestream.Close();
}

In the code above I have ascertained what the id of the assembly is by looking in sys.assemblies for the name of the assembly.

When you have written the bytes to the dll you can use a tool like dotPeek to reverse engineer the dll.

Answer 2

You can script the assembly itself in SSMS by right-clicking on it in Object Explorer (i.e. the screen-shot in the question) and selecting "Script Assembly as...".

However, that will only get the assembly, not the T-SQL wrapper objects that expose the methods inside the assembly as T-SQL Stored Procedures, Functions, etc. You can get those using the query I posted in this other S.O. answer:

SQL Server: How to list all CLR functions/procedures/objects for assembly

Depending on what version of SQL Server you are using and/or what PERMISSION_SET the assembly needs to be, you might need to handle security. It's always possible that the assembly is already signed and has an associated Asymmetric Key in the [master] DB. There are several ways this can be done and could be a longer discussion, so for now here are some resources:

• Quickest and easiest method, but far from ideal and not recommended for Production systems:
  • Enable TRUSTWORTHY for DB that Assembly is being created in
• Easy enough (especially for existing, unsigned assemblies):
  • Sign the assembly in-place in SQL Server. Please see my post: SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment. For moving an assembly, you might need to:
    1. Enable TRUSTWORTHY in the target DB
    2. Install the assembly in the target DB
    3. Create the Certificate in the target DB
    4. Sign the assembly with the certificate
    5. Copy the certificate to [master]
    6. Create a login from the certificate
    7. Grant the certificate-based login the UNSAFE ASSEMBLY permission
    8. Disable TRUSTWORTHY in the target DB
• Ideal solutions (mainly for new or current projects):
  • SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1
  • SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2

For more details regarding SQL Server 2017 and newer, please see my answer to the following question here on S.O.:

CLR Strict Security on SQL Server 2017

---

For more info on working with SQLCLR in general, please visit: SQLCLR Info