Is it possible to rebuild or retrieve a VB CLR dll file which you've imported into SQL some time ago? I've gotten to the point where I was able to find the contents of this DLL by doing:
SELECT * FROM sys.assembly_files
but I don't how if it's possible to rebuild the dll with the contents found there.
Any help would be very appreciated :)
There is a very good example here. The following code comes from the link provided.
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, 'D:\SqlServerProject1.dll', 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
I should imagine .NET Reflector will be your friend in reverse-engineering the DLL once extracted, but I have no idea if it's encrypted in any way.
I finally figured it out. I've added a new assembly which handles the creation of the DLL:
CREATE ASSEMBLY [SqlServerProject1]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01040058B9EB4C0000000000000000E00002210B01080000140000000E000000000000FE31000000200000004000000000400000200000000200000400000000000000040000000000000000A000000004000000000000020040850000100000100000000010000010000000000000100000000000000000000000A83100005300000000600000A008000000000000000000000000000000000000008000000C000000004000001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000004120000002000000014000000040000000000000000000000000000200000602E73646174610000AE000000004000000002000000180000000000000000000000000000400000C02E72737263000000A008000000600000000A0000001A0000000000000000000000000000400000402E72656C6F6300000C000000008000000002000000240000000000000000000000000000400000420000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000E031000000000000480000000200050014240000940D00000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133009009E010000020000110072010000707221000070280100000A280200000A0C02280200000A28030000068C050000010B07168C0500000116280300000A394201000008147223000070168D01000001141414280400000A147231000070198D010000010D0916723F000070A200091720C90000008C05000001A200091807280200000AA20009130411041414198D090000011305110516169C110517169C110518179C110517280500000A26110518902C0A1104189A280200000A0B0814724F000070168D0100000114141417280500000A2608147259000070168D0100000114141417280500000A2608178D010000011304110416723F000070A200110414280600000A147267000070178D010000010D091602280200000AA20009130611061414178D090000011305110516179C110517280500000A26110516902C0B1106169A280200000A10000814727F000070168D0100000114141417280500000A2608178D010000011306110616723F000070A200110614280600000A280200000A0A2B070072210000700A000814728D000070168D0100000114141417280500000A26140C062A0000133002001E000000030000110002280700000A163304160A2B0E280800000A026F0900000A0A2B00062A00001B300100560000000400001100022D04160A2B4C0002280200000A280A00000A0ADE3DDE3A280B00000A000002280200000A280C00000A0A280D00000ADE21DE16280B00000A00150A280D00000ADE10280D00000ADE0000280D00000ADE0000062A0000011C000000002000153500160D000001000009001019003A0D000001133001000E000000050000110002730E00000A0B070A2B00062A00002602281000000A00002A00001B3003002601000006000011007299000070027201010070281100000A1304141305731200000A0D7205010070731300000A0B076F1400000A00731500000A0A06076F1600000A00110407731700000A0C080972350100706F1800000A26096F1900000A72350100706F1A00000A6F1B00000A6F1C00000A17408300000000096F1900000A72350100706F1A00000A6F1B00000A6F1D00000A13092B4011096F1E00000A741400000113061106724B0100706F1F00000A280200000A282000000A2D181106724B0100706F1F00000A740100001B28040000061305000011096F2100000A2DB700DE181109751E0000012C0D1109751E0000016F2200000A0000DC0011052C290318732300000A13081108732400000A1307110711056F2500000A6F2600000A0011076F2700000A0000002A000001100000020073006ADD00180000000142534A4201000100000000000C00000076322E302E35303732370000000005006C00000004040000237E000070040000E805000023537472696E677300000000580A00005C01000023555300B40B0000100000002347554944000000C40B0000D001000023426C6F620000000000000002000001471502080900000000FA253300160000010000002F000000030000000600000006000000350000000F000000060000000100000001000000030000000000D10501000000000006005B0054000600920088000A00E90013000600220102010600400154000A006D0146010A00970146010600A60154000600B30154000A00D10113000600E901DD010A000B0246010600170254000600500231020A00710246010E00AB0295020E00B60295020E00C40295020E00D30289020E00DB0289020600E30288000600F002880006000E03FB0206001A0354000E004F033C030E00620389020E00810389020E00940389020A00D30313000600F103540006000504880006000E04880006003C0429040E007404590406008A0429048F009E0400000600AD0402010600CD0402010600EB0431020600F904310206000D05540006003505230506005005230506006B05230506008405230506009D0523050600BA0523050000000001000000000001000100000100002900310005000100010001000000430031000500010005005020000000001600620013000100FC21000000001600780018000200282200000000160078001D000300A8220000000016009F0022000400C422000000000618B50029000500D022000000001600BB002D000500000001007100000001007D0000000100840000000100AF0000000100D10000000200DF001900F5003600210031011300310077013C003900AB0143003900BB0154003900C40166005100D90118005900F2017D005900FE0182005100D9011D00610021028B00710058021D0061005F0291001100B50095007900B50029000900B5002900C1002103A7009900B50029008900B500AE008900280329008100B500290081002D03B3009100B500B900C9005D03C00099007603C700D1008B03CC00D900A603D200E100AF03D700E100B903DB00B900C703E000A1008B03E400E900DF03E900B900E803F100F100FD032900B100B500F500A900B500FC00110015040301A9001D049500A900230429000901B50029001101B50029001901B5001E012901B50025013101B50029003901B500AE004101B5002A014901B5002A015101B500AE005901B500AE006101B500AE006901B500AE007101B500AE007901B500AE002E0073018A012E00AB01B0012E0053012F012E005B0138012E00630141012E006B0160012E007B0190012E0083018A012E008B0196012E009301B0012E009B01C7012E00A3018A0143007B00A200A0004301A200C0004B01A20033006F00870087009B000801EE0004800000010000008B0F2A500000000000003100000002000000000000000000000001000A00000000000800000000000000000000000A0013000000000002000000000000000000000001008902000000000000003C4D6F64756C653E006D73636F726C6962004D6963726F736F66742E56697375616C4261736963004D6F64756C65310053716C53657276657250726F6A656374310053746F72656450726F636564757265730053797374656D004F626A6563740042696E617279546F537472696E670042696E617279004C656E42004F626A537472004F626A0053797374656D2E494F004D656D6F727953747265616D00436F6E7665727442797465546F4D53007042797465002E63746F720073737053617665417373656D626C79546F4469736B0070417373656D626C796E616D65007046696C656E616D6500496E746572616374696F6E004372656174654F626A6563740053797374656D2E52756E74696D652E436F6D70696C657253657276696365730052756E74696D6548656C70657273004765744F626A65637456616C756500496E743332004D6963726F736F66742E56697375616C42617369632E436F6D70696C65725365727669636573004F70657261746F727300436F6E646974696F6E616C436F6D706172654F626A65637447726561746572004E65774C61746542696E64696E670054797065004C61746547657400426F6F6C65616E004C61746543616C6C004C617465496E64657847657400537472696E6773004C656E0053797374656D2E5465787400456E636F64696E67006765745F556E69636F64650047657442797465436F756E740050726F6A6563744461746100457863657074696F6E0053657450726F6A6563744572726F720053797374656D2E52756E74696D652E496E7465726F705365727669636573004D61727368616C0053697A654F6600436C65617250726F6A6563744572726F72005374616E646172644D6F64756C654174747269627574650053797374656D2E446174610053797374656D2E446174612E53716C436C69656E740053716C436F6D6D616E640053716C436F6E6E656374696F6E0053716C446174614164617074657200446174615365740044617461526F770042696E6172795772697465720046696C6553747265616D0053797374656D2E436F6C6C656374696F6E730049456E756D657261746F7200537472696E6700436F6E636174004F70656E007365745F436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E00446244617461416461707465720046696C6C00446174615461626C65436F6C6C656374696F6E006765745F5461626C657300446174615461626C65006765745F4974656D0044617461526F77436F6C6C656374696F6E006765745F526F7773006765745F436F756E7400476574456E756D657261746F72006765745F43757272656E7400496E666F726D6174696F6E00497344424E756C6C004D6F76654E6578740049446973706F7361626C6500446973706F73650046696C654D6F64650053747265616D00546F417272617900577269746500436C6F73650053797374656D2E446961676E6F73746963730044656275676765724E6F6E55736572436F6465417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650044656275676761626C6541747472696275746500446562756767696E674D6F64657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004775696441747472696275746500436F6D56697369626C6541747472696275746500434C53436F6D706C69616E744174747269627574650053797374656D2E5265666C656374696F6E00417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053716C53657276657250726F6A656374312E646C6C0000001F410044004F00440042002E005200650063006F00720064007300650074000001000D4600690065006C0064007300000D41007000700065006E006400000F6D00420069006E0061007200790000094F00700065006E00000D4100640064004E0065007700001741007000700065006E0064004300680075006E006B00000D550070006400610074006500000B43006C006F00730065000067530045004C00450043005400200063006F006E00740065006E0074002000460052004F004D0020007300790073002E0061007300730065006D0062006C0079005F00660069006C006500730020005700480045005200450020006E0061006D0065003D00270001032700012F63006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D007400720075006500001541007300730043006F006E00740065006E007400000F63006F006E00740065006E00740000001B20D4F1C0E69C4AB183C6C5549364A90008B77A5C561934E08908B03F5F7F11D50A3A0400011C1C040001080E040001081C06000112091D0503200001050002010E0E0206080500021C0E0E060003021C1C021000071C1C12210E1D1C1D0E1D12211D021100081C1C12210E1D1C1D0E1D12211D02020800031C1C1D1C1D0E0D07071C1C1C1D1C1D1C1D021D1C040000122D042001080E0307010805000101123503000001052001011D050607021209120904010000000600030E0E0E0E042001010E052001011245062002010E124506200208124D0E0420001269052001126D0E042000127103200008042000125D0320001C0420011C0E040001021C021D0503200002062002010E117D062001011280810420001D0515070A124112451249124D0E1209125112551259125D06200101118091042001010804200101020801000301000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773012901002461396161383031662D613536322D346238612D623866662D313830636563366535353536000005010000000005010001000019010014436F7079726967687420C2A9204850203230313000001601001153716C53657276657250726F6A656374310000070100024850000000D03100000000000000000000EE310000002000000000000000000000000000000000000000000000E031000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000058B9EB4C0000000002000000920000001C4000001C18000052534453E57CA4FDD7683A44B6C2DD735FDFE93C01000000633A5C75736572735C6B6F656E6D645C646F63756D656E74735C76697375616C2073747564696F20323031305C50726F6A656374735C53716C53657276657250726F6A656374315C53716C53657276657250726F6A656374315C6F626A5C44656275675C53716C53657276657250726F6A656374312E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000030003000000280000800E000000480000801000000060000080000000000000000000000000000002000200000078000080030000009000008000000000000000000000000000000100007F0000A80000800000000000000000000000000000010001000000C00000800000000000000000000000000000010000000000D80000000000000000000000000000000000010000000000E80000000000000000000000000000000000010000000000F800000000000000000000000000000000000100000000000801000068640000E802000000000000000000005067000028010000000000000000000078680000220000000000000000000000186100004C03000000000000000000004C0334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001002A508B0F000001002A508B0F3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC020000010053007400720069006E006700460069006C00650049006E0066006F00000088020000010030003000300030003000340062003000000028000300010043006F006D00700061006E0079004E0061006D0065000000000048005000000000004C0012000100460069006C0065004400650073006300720069007000740069006F006E0000000000530071006C00530065007200760065007200500072006F006A006500630074003100000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003900370039002E0032003000350032003200000000004C001600010049006E007400650072006E0061006C004E0061006D0065000000530071006C00530065007200760065007200500072006F006A0065006300740031002E0064006C006C0000004C00140001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A9002000480050002000320030003100300000005400160001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C00530065007200760065007200500072006F006A0065006300740031002E0064006C006C000000440012000100500072006F0064007500630074004E0061006D00650000000000530071006C00530065007200760065007200500072006F006A006500630074003100000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003900370039002E00320030003500320032000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003900370039002E00320030003500320032000000000000000000280000002000000040000000010004000000000080020000000000000000000000000000000000000000000000008000008000000080800080000000800080008080000080808000C0C0C0000000FF0000FF000000FFFF00FF000000FF00FF00FFFF0000FFFFFF0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007777777777777777777777777777700444444444444444444444444444447004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF4700488888888888888888888888888847004444444444444444444444444444470044C4C4C4C4C4C4C4C4C4ECECE49747004CCCCCCCCCCCCCCCCCCCCCCCCCCC40000444444444444444444444444444000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFC00000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000003C0000007FFFFFFFFFFFFFFFFFFFFFFFF2800000010000000200000000100040000000000C0000000000000000000000000000000000000000000000000008000008000000080800080000000800080008080000080808000C0C0C0000000FF0000FF000000FFFF00FF000000FF00FF00FFFF0000FFFFFF000000000000000000077777777777777744444444444444474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF84748888888888888474CCCCCCCCCCCCC47C4444444444444C000000000000000000000000000000000FFFF000080000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000FFFF0000FFFF00000000010002002020100001000400E80200000200101010000100040028010000030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000003200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = UNSAFE;
After that I add my procedure to consume it:
CREATE PROCEDURE [dbo].[sspSaveAssemblyToDisk]
@pAssemblyname NVARCHAR (4000), @pFilename NVARCHAR (4000)
AS EXTERNAL NAME [SqlServerProject1].[SqlServerProject1.StoredProcedures].[sspSaveAssemblyToDisk]
And then I run my procedure and give it the path to save my file and the name of the assembly:
exec sspSaveAssemblyToDisk 'MyAssemblyName','C:\Assemblies\MyAssembly.dll'
Make sure you have writing rights to the location you're saving your file, and there you go!
I ended up doing this.
I got the create script for the CLR assembly for which I want to generate the dll.
CREATE ASSEMBLY [asmClr]
AUTHORIZATION [dbo]
FROM [some_hex_value] //hex value removed for brevity
WITH PERMISSION_SET = UNSAFE;
I converted the hex value (some_hex_value) to bytes using C# after removing the starting "0x" string. There are LINQ examples on SO too for converting hex to bytes.
byte[] StringToByteArray(string hex)
{
var bytes = new byte[hex.Length / 2]; // 2 hex chars = 1 byte
for (int i = 0; i < bytes.Length; i++)
{
string bytehex = hex.Substring(2*i, 2);
byte b = Byte.Parse(bytehex, NumberStyles.HexNumber);
bytes[i] = b;
}
return bytes;
}
I wrote the bytes to the file system.
File.WriteAllBytes(@"D:\clr.dll", bytes);
I used ILSpy to look into the dll source.
