Hello people,
All right with you ?
In this post I will demonstrate how to export a CLR assembly in SQL Server as a DLL and reverse engineer it to C # source code. The first time I needed to use this feature was when there was an assembly already created and compiled in the database and I needed to change the source code of a procedure, but the version that was in the database didn't look the same as it was in Team Foundation Server (TFS) ).
How to export assembly from a bank CLR to DLL
The first step in performing this activity is to export the database assembly to DLL. To do this, you will need to identify the assembly name and run one of the scripts below.
Identifying the assembly name
Exporting assembly to DLL with OLE Automation
One way to export the Assembly to DLL is by using OLE Automation. To do this, use the code below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
-- Habilitando o OLE Automation (Se já está habilitando, não precisa executar) 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 = A.content FROM sys.assembly_files A WITH(NOLOCK) JOIN sys.assemblies B WITH(NOLOCK) ON A.assembly_id = B.assembly_id WHERE A.file_id = 1 AND B.name = 'tSQLtCLR' -- nome do assembly 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:\Teste\myassembly.dll', 2 -- Nome do seu assembly EXEC sp_OAMethod @ObjectToken, 'Close' EXEC sp_OADestroy @ObjectToken -- Desativando o OLE Automation (Execute apenas se não estava ativado antes) EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE WITH OVERRIDE GO EXECUTE SP_CONFIGURE 'show advanced options', 0 RECONFIGURE WITH OVERRIDE GO |
Exporting assembly to DLL with CLR
Another way to export the Assembly to DLL is by using the CLR itself.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using System.IO; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void stpAssembly_Backup(SqlString Ds_Servidor, SqlString Ds_Database, SqlString Ds_Assembly, SqlString Ds_Diretorio_Destino) { var sql = "SELECT AF.content FROM " + Ds_Database.Value + @".[sys].assembly_files AF JOIN " + Ds_Database.Value + ".[sys].assemblies A ON AF.assembly_id = A.assembly_id where AF.file_id = 1 AND A.name = @assemblyname"; using (var conn = new SqlConnection(Servidor.Localhost.Replace("LOCALHOST", Ds_Servidor.Value))) { conn.Open(); using (var cmd = new SqlCommand(sql, conn)) { var param = new SqlParameter("@assemblyname", SqlDbType.VarChar) { Value = Ds_Assembly.Value }; cmd.Parameters.Add(param); using (var dr = cmd.ExecuteReader()) { dr.Read(); var bytes = dr.GetSqlBytes(0); var nomeArquivo = Ds_Diretorio_Destino.Value + "/" + Ds_Assembly.Value + ((!Ds_Assembly.Value.ToLower().Contains(".dll")) ? ".dll" : ""); using (var bytestream = new FileStream(nomeArquivo, FileMode.CreateNew)) { bytestream.Write(bytes.Value, 0, (int) bytes.Length); } } } } } } |
Example of use:
|
1 2 3 4 5 |
EXEC CLR.dbo.stpAssembly_Backup @Ds_Servidor = N'vm-dba', -- nvarchar(max) @Ds_Database = N'BI', -- nvarchar(max) @Ds_Assembly = N'tSQLtCLR', -- nvarchar(max) @Ds_Diretorio_Destino = N'C:\Teste\' -- nvarchar(max) |
Result:
Reverse Engineering DLL to Source Code
Once you have successfully exported the assembly from your CLR library to a DLL file, you can reserve engineering the source code. To do this there are several tools, but I suggest the tool JetBrains dotPeek.
Once installed, you can open the dotPeek tool, click on the menu and select the option “Open…”
Now that your CLR will appear in the list, just click on the button highlighted in the image below, or right-click on the library and select the option “Export to Project” or access the File menu> “Export to Project”.
Select where you want to save the source code and you can leave the default options checked.
Ready. DotPeek has reverse engineered your DLL and regenerated the original source code as shown below:
It is worth mentioning that some formatting, spacing and comments are not recovered through reserve engineering, since this is lost when the code is compiled. In addition, this technique only applies to DLLs that have not been obfuscated (source code encryption).
That's it, guys.
I hope you enjoyed this post.
Hug!
SQL Server database as export save export a CLR assembly as DLL and reverse engineer to source code C # csharp disassembly reverse engineering JetBrains dotPeek
SQL Server database as export save export a CLR assembly as DLL and reverse engineer to source code C # csharp disassembly reverse engineering JetBrains dotPeek
