Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Exporting CLR Assemblies from SQL Server back to .dll files


By:   |   Last Updated: 2013-12-16   |   Comments (7)   |   Related Tips: More > Application Development

Problem

You have imported an assembly into a SQL Server database to use the CLR functions and stored procedures it contains. However later, you lost the original .dll file and you would like to create the .dll file again from what's in the database. Scripting the assembly using the create script option doesn't help, because it scripts out the assembly as binary sequence instead of creating the .dll file.  In this tip, we look at how you can recreate the .dll file.

Solution

There are several possibilities how to achieve the goal and export the assembly back to a .dll file. This tip will show one of the possible ways by using a simple SQL CLR stored procedure that we pass the assembly name and path where we would like to store the .dll file created again.

Getting Information About Assemblies

To be able to export the assemblies, we need to get information about them and of course their binary representation. Information about assemblies and their files are stored in two system table. In the sys.assemblies table information is stored about the assemblies itself, like name, id, permission set, timestamp of creation or latest modification. As a single assembly, can be represented by multiple physical files, a second system table sys.assembly_files exists. This table contains information about individual assembly files and also contains their content (binary representation) in a varbinary(max) field.

To get file names and their binary content for particular assembly we can use the below T-SQL code:

SELECT 
 af.name,
 af.content 
FROM sys.assemblies a
INNER JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id 
WHERE 
 a.name = 'Microsoft.SqlServer.Types'     
    

CLR Stored Procedure to save assembly

As mentioned above we will use a CLR Stored procedure to do the job.

First we create a new SqlConnection to SQL Server with connection string "context connection=true". This creates the new connection in the context of the current session from which the CLR stored procedure is being called.

Once the connection is created we can create a new SqlCommand which will execute the above T-SQL query to fetch all file names and binary content for the provided assembly name. In a loop we will iterate though all the rows returned by the SqlDataReader and save the binary data for each file using FileStream.

public class AssemblyExporter
{
    [SqlProcedure]
    public static void SaveAssembly(string assemblyName, string destinationPath)
    {
        string sql = @"SELECT af.name, af.content FROM sys.assemblies a INNER JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id WHERE a.name = @assemblyname";
        using (SqlConnection conn = new SqlConnection("context connection=true"))   //Create current context connection
        {
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                SqlParameter param = new SqlParameter("@assemblyname", SqlDbType.VarChar);
                param.Value = assemblyName;
                cmd.Parameters.Add(param);
                cmd.Connection.Open();  //Open the context connetion
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read()) //Iterate through assembly files
                    {
                        string assemblyFileName = reader.GetString(0);  //get assembly file name from the name (first) column
                        SqlBytes bytes = reader.GetSqlBytes(1);         //get assembly binary data from the content (second) column
                        string outputFile = Path.Combine(destinationPath, assemblyFileName);
                        SqlContext.Pipe.Send(string.Format("Exporting assembly file [{0}] to [{1}]", assemblyFileName, outputFile)); //Send information about exported file back to the calling session
                        using (FileStream byteStream = new FileStream(outputFile, FileMode.CreateNew))
                        {
                            byteStream.Write(bytes.Value, 0, (int)bytes.Length);
                            byteStream.Close();
                        }
                    }
                }
            }
            conn.Close();
        }
    }
}
    

Creating Assembly and Registering Stored Procedure

Once we build the AssemblyExporter assembly, we have to create the assembly in a SQL Server database and create the CLR Stored procedure. As the stored procedure access resources outside of SQL Server (it is saving the assembly files to disk), the assembly needs to be created with EXTERNAL_ACCESS permission set and the database has to be set to TRUSTWORTHY.

CREATE DATABASE [TestDB]
GO
ALTER DATABASE [TestDB] SET TRUSTWORTHY ON WITH ROLLBACK IMMEDIATE
GO
USE [TestDB]
GO
CREATE ASSEMBLY [AssemblyExporter]
AUTHORIZATION [dbo]
FROM 'C:\SQLCLR\AssemblyExporter.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
CREATE SCHEMA [AssemblyExporter] AUTHORIZATION [dbo]
GO
CREATE PROCEDURE [AssemblyExporter].[usp_SaveAssembly] (
 @assemblyName [nvarchar](128),  --Assembly Name
 @destinationPath [nvarchar](256) --Destination path for export
)  
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [AssemblyExporter].[AssemblyExporter].[SaveAssembly]
GO
    

Testing Export

Once the assembly and CLR stored procedure created, we can test it as follows passing in the name of the assembly we want to recreate and the path where we want the .dll file created.

EXECUTE [AssemblyExporter].[usp_SaveAssembly] 
   @assemblyName = 'Microsoft.SqlServer.Types'
  ,@destinationPath = 'C:\SQLCLR'
GO
    

Please note, the current implementation accesses the disk drive under the credentials of the account the SQL Server service is running. Therefore to be able to export the assembly, the SQL Server service account needs appropriate access to the destination directory.

Next Steps
  • Take a look on MSDN on sys.assemblies (Transact-SQL) and sys.assembly_files (Transact-SQL).
  • Don't forget to check CLR Stored Procedures on MSDN.
  • You can download a complete sample Visual Studio 2013 project here. For easier deployment create a C:\CLRSQL directory prior building the project. Project has a post build action which automatically copies compiled AssemblyExporter.dll file to this directory for easier deployment. You can use the script in AssemblyExporter.sql for easy creation of the assembly and CLR stored procedure.
  • You can further elaborate on the sample and update it so the assembly is saved under the credentials of current user instead of the SQL Server service account. For this purpose take a look on the SqlContext.WindowsIdentity Property on MSDN.


Last Updated: 2013-12-16


next webcast button


next tip button



About the author
MSSQLTips author Pavel Pawlowski Pavel Pawlowski is Lead BI Specialist in Tieto with focus on Microsoft SQL Server and Microsoft BI platform.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, January 08, 2019 - 1:57:27 AM - Jeff Back To Top

Hello,

Help me.

Severity Code Description Project File Line Suppression State

ErrorCS0103The name 'FileMode' does not exist in the current context

Error CS0103 The name 'Path' does not exist in the current context

Error CS0246 The type or namespace name 'FileStream' could not be found (are you missing a using directive or an assembly reference?)

Error CS0246 The type or namespace name 'FileStream' could not be found (are you missing a using directive or an assembly reference?)


Wednesday, July 25, 2018 - 6:57:51 PM - Sergio Back To Top

 Hi I created the CLRSQL folder in c: then i run the sql commands from the assemblyexporter zip and run the last command wich is EXECUTE [AssemblyExporter].[usp_SaveAssembly] 

   @assemblyName = 'MDLUpdateTrigger'

  ,@destinationPath = 'C:\CLRSQL'

GO

But nothing creates the dll. Please help


Wednesday, January 15, 2014 - 7:51:54 AM - Pavel Pawlowski Back To Top

If you are interested only in classes, methods and their signatures, then you can use the Object Browser in Visual Studio. Simply choose View/Object Broser, and then click the three dots next to the Browse field and select your .dll.

If you would like to analyze also the sources, then you will have to refactor it e.g. using Redgate's .NET Reflector or free ILSpy.


Wednesday, January 15, 2014 - 2:08:45 AM - zorro-cool Back To Top

Very interesting article! Thank you! It's interesting for me are there any ways to get any information about the source code of the dll (name and signature of methods e. g.) and to obtain this info in your clr?


Monday, January 06, 2014 - 12:20:40 PM - Pavel Pawlowski Back To Top

@Lyndon, exactly for this purposes it is very useful. You can do a security review of third party code in assemblies for which you do not have sources.


Monday, January 06, 2014 - 10:33:20 AM - Lyndon Back To Top

It might also be useful if you want to see the code of the functions in someone else's CLR assembly. Dump it out to a dll and then open it up in reflector.


Monday, January 06, 2014 - 5:05:45 AM - Graham Bull Back To Top

While it's useful to know that you can do this, you should never actually need  to do this. If you've lost the original DLL, then you can just go off and build a new one. If you haven't got the source code any more, then there's a failure in your processes.


Learn more about SQL Server tools