question

Daniel Ross avatar image
Daniel Ross asked

getting the source code from a CLR assembly

Hi Peeps, Is there a way of getting the source code from a CLR assembly? We have a database created by another department that has a CLR assembly used in a SP, and we need to change the CLR because it is referencing a column that doesn't exist anymore and we need to update it. Can we update the CLR without the source code, or is there a way to get the source code from the assembly.
sql-server-2008clrred-gate
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
I haven't done with a SQL CLR assembly, but [.NET Reflector][1] from Red-Gate works very well to reverse engineer .NET assemblies. [1]: http://reflector.red-gate.com/download.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I didn't even think of using reflector, thanks. I used the script from this link to save the dll to the hard drive. http://serverfault.com/questions/139703/extracting-a-sqlclr-assembly
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered
I'll add an answer in here... 1 - The script to extract the assembly from the database at http://serverfault.com/questions/139703/extracting-a-sqlclr-assembly is not correct. `SELECT @IMG_PATH = content FROM sys.assembly_files WHERE assembly_id = 65536` will return an arbitrary file from the assembly, and there is no requirement that the assembly only has one file. If you look at the [MSDN reference for sys.assembly_files][1] when you will see that you want to limit it `WHERE file_id = 1` to obtain the assembly DLL. 2 - .NET reflector does not always decompile things correctly. I have had instances in the past where I have been decompiling something in order to recompile it, and the recompiled version has been subtly different. An example of this was when trying to decompile and recompile a third party T-SQL script parsing component - it looked like it worked, but then fell over on some of the less obvious code paths - so definitely tread with caution. [1]: http://msdn.microsoft.com/en-us/library/ms175085.aspx
8 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Great point that seems to hold true for many decompilers
4 Likes 4 ·
+1, very true...
0 Likes 0 ·
@Matt Whitfield I would like to ask about another possibility. Usually, the reflectors are designed to use instances/static methods of the **System.Reflection** types to reflect into the source types querying for methods/properties with different combination of the flags (thus the name - reflector). The other alternative is to use the **ildasm** tool provided by .NET Framework. This tool gives a complete copy of the source assembly in **MSIL** language unless the latter was compiled with ngen (it makes no assumptions/changes whatsoever). This is why they say to never include any sensitive info into the binary :) While MSIL is not trivial to understand, it still shows the source in plain text, so if all that is needed to be changed is limited to removing the column from the embedded query then it appears that it could be suffifient to edit the MSIL by hand and then compile it back to DLL. This way, the logic of the code will certainly remain the same. This way it would be possible to implement the change even if the source was treated by the Obfuscator which usually makes the refectors scratch their heads. Do I make any sense or I simply need more coffee?
0 Likes 0 ·
@Oleg - you definitely make sense - but editing MSIL by hand is certainly not for the feint hearted - it's not a route I'd take unless it was an absolute emergency... :)
0 Likes 0 ·
@Oleg - also, congrats on the 10k sir! :)
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.