Extracting a .NET Assembly from SQL Server 2005

Question

I am trying to help a personal friend (who now also is a client) with a SQL CLR related problem. He has a SQL Server with a database that has 3 .NET assemblies embeded in it. He asked me to help him extract the assemblies from within the database and save them as .dll files on the disk. Is this even possible?

Answer 1

Yes, this is possible. The actual binary representation of the assemblies live in the SQL catalog for your server. Namely, if you run a join between sys.assembly_files and sys.assemblies you can get to all the information you need. The assemblies binary is in the content column of the sys.assembly_files view.

But in order to extract the binary representation from SQL Server and into a file on disk you will have to write some .NET code that needs to run on the same database where the assemblies you refer to are located now. In Visual Studio start a SQL CLR project and add a class to it with the following code:

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Permissions;

namespace ExtractSqlAssembly {
    [PermissionSet(SecurityAction.Demand, Unrestricted = true, Name = "FullTrust")]
    public partial class SaveSqlAssembly {

        [SqlProcedure]
        public static void SaveAssembly(string assemblyName, string path) {
            string sql = @"SELECT AF.content FROM sys.assembly_files AF JOIN sys.assemblies A ON AF.assembly_id = A.assembly_id where AF.file_id = 1 AND A.name = @assemblyname";
            using (SqlConnection conn = new SqlConnection("context connection=true")) {
                using (SqlCommand cmd = new SqlCommand(sql, conn)) {
                    SqlParameter param = new SqlParameter("@assemblyname", SqlDbType.VarChar);
                    param.Value = assemblyName;
                    cmd.Parameters.Add(param);

                    cmd.Connection.Open();  // Read in the assembly byte stream
                    SqlDataReader reader = cmd.ExecuteReader();
                    reader.Read();
                    SqlBytes bytes = reader.GetSqlBytes(0);

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

Then build the project and deploy it to your database. Make sure that the CLR Enabled configuration option is enabled on the SQL Server. This is probably already enabled, since you have assemblies on it. In case clr execution is not enabled you can run the following code on SSMS to enable it:

sp_configure 'clr enabled', 1
go

reconfigure
go

One more thing that you need to be aware of is the by default SQL server may not allow you to write to the disk from the .NET code. If you get a FileIO security error when you run the code above by calling the stored procedure in SSMS, you will need to configure the proper permission set for the assembly. You can do this via SSMS: right-click the new assembly and look at the Permission Set in the Properties dialog. Set it to External Access. Now you should be able to export your assemblies by running the following code in SSMS:

exec SaveAssembly 'AssemblyName', 'f:\path\to\assemblyname.dll'

Hope this works for you...

Answer 2

Yes.

do a select * from sys.assembly_files to find the id of the assembly you want

DECLARE @IMG_PATH VARBINARY(MAX)
DECLARE @ObjectToken INT

SELECT @IMG_PATH = content FROM sys.assembly_files WHERE assembly_id = 65536

EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
        EXEC sp_OASetProperty @ObjectToken, 'Type', 1
        EXEC sp_OAMethod @ObjectToken, 'Open'
        EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
        EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, 'c:\temp\myassembly.dll', 2
        EXEC sp_OAMethod @ObjectToken, 'Close'
        EXEC sp_OADestroy @ObjectToken

Answer 3

Jonas' approach works fine as a Console app or Linqpad script also - there's no need for the code to be executed locally within the SQL process, as he implies. eg extracting the tSQLt assembly (a testing tool) from a database:

void Main()
{
    var assemblyName = "tSQLtCLR";
    var serverName = "localhost";
    var databaseName = "MyDb";
    var targetDir = Environment.ExpandEnvironmentVariables("%TEMP%");
    var targetFile = Path.Combine(targetDir, assemblyName) + ".dll";

    var sql = @"SELECT AF.content FROM sys.assembly_files AF JOIN sys.assemblies A ON AF.assembly_id = A.assembly_id where AF.file_id = 1 AND A.name = @assemblyName";

    var connectionString = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=true", serverName, databaseName);
    using(var connection = new System.Data.SqlClient.SqlConnection(connectionString)){
        connection.Open();

        var command = connection.CreateCommand();
        command.CommandText = sql;
        command.Parameters.Add("@assemblyName", assemblyName);

        using(var reader = command.ExecuteReader()){
            if(reader.Read()){
                var bytes = reader.GetSqlBytes(0);

                File.WriteAllBytes(targetFile, bytes.Value);
                Console.WriteLine(targetFile);
            }else{
                throw new Exception("No rows returned");
            }
        }
    }
}