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?

Solution 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...

Solution 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

Solution 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");
            }
        }
    }
}

Solution 4

Preet's solution worked for me, but I had to configure Ole Automation to work on SQL Server 2008 R2. Note also that the SaveToFile doesn't work--neither does it give an error message--unless SQL Server has permissions to that directory. In my case I used the data folder of the SQL Server instance which worked fine.

EXECUTE SP_CONFIGURE 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE WITH OVERRIDE
GO

DECLARE @IMG_PATH VARBINARY(MAX)
DECLARE @ObjectToken INT

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

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:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\myassembly.dll', 2
        EXEC sp_OAMethod @ObjectToken, 'Close'
        EXEC sp_OADestroy @ObjectToken  

EXEC sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE WITH OVERRIDE
GO

EXECUTE SP_CONFIGURE 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

Solution 5

I have found a simpler solution to this problem, which was necessary because sp_OACreate does not seem to be available for SQL Server 2017 (at least, not the Linux version).

You can just use the BCP utility to write the assembly to a file on disk, like so:

/opt/mssql-tools/bin/bcp "SELECT content FROM sys.assembly_files WHERE name = '${ASSEMBLY_NAME}'" \
    queryout /tmp/my_assembly.so -f bcp.fmt \
    -S localhost -U sa -P "${SA_PASSWORD}" -d master

And use this format file (bcp.fmt):

13.0
1
1       SQLBINARY           0       0       ""   1     content                          ""

The resulting file (/tmp/my_assembly.so) can be used in the creation of an assembly, like so:

CREATE ASSEMBLY [MyAssembly] AUTHORIZATION [dbo]
FROM '/tmp/my_assembly.so' WITH PERMISSION_SET = SAFE;

View more solutions

Comments

[Blerim J]

awesome. Simple and clean. Thank you.

[piers7]

This approach works fine when executed externally too - you don't need to go the SQLCLR route. See my answer below

[Sayed Abolfazl Fatemi]

Fastest solution that worked for me. Thank you very much.