none
Error from CLR assembly in Azure SQL Database Managed Instance RRS feed

  • Question

  • I used the Migration Service to move an on-premise SQL Server 2008 R2 database to Managed Instance. Everything went smoothly. But I have a stored procedure that uses functions from a CLR assembly (RegEx - enormously useful in SQL Server!). I receive the following error when I run the sp, even if I run it in SSMS under my sysadmin account.

    Msg 10314, Level 16, State 11, Procedure dbo.globalSearch, Line 22 [Batch Start Line 2]
    An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
    System.IO.FileLoadException: Could not load file or assembly 'regexsqlclr, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
    System.IO.FileLoadException:
       at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
       at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
       at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)
       at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
       at System.Reflection.Assembly.Load(String assemblyString)

    I have asked Azure support on Twitter for help and they suggested I try the options in this link. I tried option one, with no success. Option two is not available to me in Managed Instance. The original assembly was created from a file, which is also not possible in MI. But it appears to exist in the database as a binary object anyway because when I use SSMS to script it out, this is what I get.

    USE [Utility]
    GO

    /****** Object:  SqlAssembly [RegexFunction]    Script Date: 2/27/2019 4:44:11 PM ******/
    DROP ASSEMBLY [RegexFunction]
    GO

    /****** Object:  SqlAssembly [RegexFunction]    Script Date: 2/27/2019 4:44:11 PM ******/
    CREATE ASSEMBLY [RegexFunction]
    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103008B1BF65A0000000000000000E00022200B0150000028000000060000000000002647000000200000006000000000001000200000000200000400000000000000040000000000000000A000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000D44600004F000000006000008803000000000000000000000000000000000000008000000C0000009C4500001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000002C270000002000000028000000020000000000000000000000000000200000602E72737263000000880300000060000000040000002A0000000000000000000000000000400000402E72656C6F6300000C0000000080000000020000002E0000000000000000000000000000400000420000000000000000000000000000000008470000000000004800000002000500C82500001C1F00000100000000000000E4440000B8000000000000000000000000000000000000000000000000000000000000000000000000000000000000001E02281700000A2A1E02281800000A2AA6731900000A8001000004731A00000A8002000004731B00000A8003000004731C00000A80040000042A2E7E010000046F1D00000A2A2E7E020000046F1E00000A2A2E7E030000046F1F00000A2A2E7E040000046F2000000A2AC67E0500000414282100000A2C1E7201000070D005000002282200000A6F2300000A732400000A80050000047E050000042A1A7E060000042A1E0280060000042A56730C000006282500000A740600000280070000042A1E02282600000A2A1A7E070000042A1A280D0000062A1E02282700000A2A001330040016010000000000000F00282800000A178C25000001168C25000001282900000A0F01282800000A188C25000001168C25000001282900000A282A00000A0F02282800000A1A8C25000001168C25000001282900000A282A00000A0F03282800000A1E8C25000001168C25000001282900000A282A00000A0F04282800000A1F108C25000001168C25000001282900000A282A00000A0F05282800000A1F208C25000001168C25000001282900000A282A00000A0F06282800000A1F408C25000001168C25000001282900000A282A00000A0F07282800000A20000100008C25000001168C25000001282900000A282A00000A0F08282800000A20000200008C25000001168C25000001282900000A282A00000A282B00000A282C00000A2A0000133003004F000000010000110F01282D00000A2D090F00282D00000A2C0D7E2E00000A282F00000A0A2B2E1201FE152500000104283000000A0B0F01283100000A0F00283100000A07283200000A6F3300000A282F00000A0A062A001330030045000000020000110F01282D00000A2D090F00282D00000A2C087E3400000A0A2B291201FE152500000104283000000A0B0F01283100000A0F00283100000A07283500000A283600000A0A062A000000133003004B000000030000110F01282D00000A2D090F00282D00000A2C0916282C00000A0A2B2E1201FE152500000104283000000A0B0F01283100000A0F00283100000A07283200000A6F3700000A282C00000A0A062A00133001002A000000040000110F00282D00000A2C0D7E2E00000A282F00000A0A2B120F00283100000A283800000A282F00000A0A062A0000133003003C000000050000110F01282D00000A2D090F00282D00000A2C04140A2B241201FE152500000104283000000A0B0F01283100000A0F00283100000A07283900000A0A062A4A0302283A00000A733B00000A81240000012A00133005003E000000040000110F00282D00000A2D090F01282D00000A2C087E3C00000A0A2B2212000F00283100000A0F01283100000A0F02283100000A19283D00000A283B00000A062A0000133005004D000000010000110F01282D00000A2D090F00282D00000A2C087E3C00000A0A2B311201FE152500000105283000000A0B12000F01283100000A0F00283100000A0F02283100000A07283D00000A283B00000A062A000000133003003C000000050000110F01282D00000A2D090F00282D00000A2C04140A2B241201FE152500000104283000000A0B0F01283100000A0F00283100000A07283E00000A0A062A133002003B000000060000110274290000010A03066F3300000A733B00000A812400000104066F3700000A733F00000A812200000105066F4000000A733F00000A81220000012A360203284100000A284200000A2A1E02284300000A2A2ED009000002282200000A2A1E02284400000A2A000000133001001400000007000011028C0500001B2D08280100002B0A2B02020A062A2203FE150500001B2A000000133002002800000008000011027B4600000A6F4700000A0A068C0800001B2D12280200002B0A027B4600000A066F4800000A062A4A02282700000A02734900000A7D4600000A2A0042534A4201000100000000000C00000076322E302E35303732370000000005006C000000F8090000237E0000640A0000D409000023537472696E6773000000003814000030000000235553006814000010000000234755494400000078140000A40A000023426C6F6200000000000000020000015715A209090F000000FA01330016000001000000350000000A00000008000000230000002A00000049000000480000000800000005000000090000000A000000080000000100000004000000010000000200000003000000020000000000E7040100000000000600A703860706001404860706008202C7060F00F00700000600C302A80506008A03A8050600FB03A8050600C703A8050600E003A80506000A03A8050600AF02400706002B02400706003D03A8050A004E025C060A00FB01D1040A009602D1040E00C20116070E008C06DA0606002503C7060E00DA025F070E00F20287000600CA0802050E00780616070E005A0387000600850102050E000100A7040A003902200506006502C70606001002860706004206A6070600D20587050A00D201720512007503950612002B000C08120015050C0812005F040C080A00A20855081200B90095061200B30095060600310174080A008404550806003D0102050600B109A8050A00DD0172050E009C0587000E00BE085F070E0049085F0706006B0402050A00980955080A00BA0155080A00BA0555080600AF0886070600B00602050000000034000000000001000100000000004D059E09450001000100000000008A069E0949000100020000011000D1089E0959000100030000010000C607B707590005000800000110002E089E09810007000B0000010000C0099E09590008000E000100000087083D00590008000F000501000008070000590008001B0005010000100000005900080022003100070680013100DE0588013100F205900131002006980111000905A0011100A201A40111000C01A8012100440951015020000000000618BA06060001005820000000000618BA06060001006020000000001118C006AC0101008A200000000013087D06B001010096200000000013083D05B5010100A2200000000013087406BA010100AE20000000001308F806BF010100BA200000000013083E06C4010100EC200000000013088A01C9010100F3200000000013089601CE010100FB20000000001118C006AC0102001121000000000618BA060600020019210000000016080A09D401020020210000000013082108D40102002721000000000618BA060600020030210000000016005B05D901020054220000000016007F04FA010B00B022000000001600720409020E0004230000000016008D09180211005C230000000016007601270214009423000000001600FF0830021500DC230000000011005D093F021800F023000000001600C80048021A003C240000000016006A0957021D009824000000001600FF0730022100E0240000000011004E0969022400272500000000C60242082D012800352500000000C6022501E10029003D2500000000830082017A022900492500000000C6026904AB00290054250000000011005F007F02290074250000000001004B0087022A002721000000000618BA0606002B008025000000000308FC004A002B00B425000000000618BA0606002B0000000100550400000100EA01000002006C0100000300B201000004009D0000000500610100000600D50000000700DB0800000800330900000900160900000100CA05000002003E0900000300A70800000100CA05000002003E0900000300A70800000100CA05000002003E0900000300A708000001003E0900000100CA05000002003E0900000300A708000001003E09020002008A04000001003E0900000200CA0500000300270900000100CA05000002003E0900000300270900000400A70800000100CA05000002003E0900000300A708000001003E09020002008A04020003008209020004009B0400000100DC05000001001C01000001001C010900BA0601001100BA0606001900BA060A002900BA0610003100BA0610003900BA0610004100BA0610004900BA0610005100BA0610005900BA0615006100BA0610006900BA0610007100BA061A008100BA0620009900BA060600A100BA060600A900BA060600C100BA062600D900BA061000E100BA060600E900BA0606000901BA0606008900BA0606009100BA0606000C00BA0606001400BA0606001C00BA0606002400BA0606000C00FC004A001400FC004A001C00FC004A002400FC004A00B10039084F00C9004F015500C900AD095D00F100BA0663006101A6006B000101BA060600B100BA06060019013204740069015B0478007101C8087F007901520685001101E7088A002101F70474008101BA099A002101E7089D001101F308A40021013204AB0089018404AF0091013204AB001901F501C30089017704C8001901E708D10091017809E10089017B01EB0089010409F9007901690403012101BA0610002101FD0408018901CD000D018901040817011101BA06010091019004E100A10146042801B10042082D01B1002501E100B1006904AB00A901ED003A013400440951013C0032044A003C003C0468013C00BA06060029009B004A0A2E000B00B7022E001300C0022E001B00DF022E002300E8022E002B00F9022E003300F9022E003B00E8022E004300FF022E004B00F9022E005300F9022E005B0017032E006300410340007B004E0340007300530343006B00340943007300530349009B005B0A63006B00340963007300530369009B006F0A80007B004E03830083004E0383008B004E0383006B00340989009B007C0AA0007B004E03A30083004E03A3006B004D09A300A3004E03A300AB004E03A3008B004E03A9007300DF02C0007B004E03C300AB004E03C3006B008E09C3007300DF02C9007300DF02E0007B004E03E30083004E03E3008B004E03E300A3004E03E300AB004E0309019B00900A23017300530323019300E80943017300530343015300F9020002B3005C032002B3005C034002B3005C036002B3005C038002B3005C03A002B3008103E002B300FC040003B30058062003B300B50760037300530360037B004E0380037300530380037B004E03A00373005303A0037B004E03C00373005303C0037B004E03E0037B004E0300047B004E0320047B004E0320047300530340047B004E0360047B004E036004730053039100BA00D800E500F000220132014501040001000500050006000700070008000A00090000008C068F0200004F05940200007806990200000A079E0200004206A3020000AA01A80200000E09AD0200003008AD0200000001B202020004000300020005000500020006000700020007000900020008000B00020009000D0001000A000D0002000D000F0002000E0011000200220013002E0035003C00430037014A01590160010480000001000000000000000000000000003D0000000200000000000000000000006E017E00000000000200000000000000000000006E0102050000000008000000000000000000000077018700000000000200000000000000000000006E017200000000000000000001000000D0070000090004000A0004000000100014004900000010003F00490000000000410049008B0040018B0063010000000000436F6E7465787456616C7565603100546872656164536166654F626A65637450726F766964657260310053716C496E743332003C4D6F64756C653E00526567657853514C434C52005400446973706F73655F5F496E7374616E63655F5F004372656174655F5F496E7374616E63655F5F0053797374656D2E44617461006D73636F726C6962004D6963726F736F66742E56697375616C426173696300436F6D70696C65640053796E6368726F6E697A65640053797374656D446174614163636573734B696E640052656745785265706C6163650049676E6F72655061747465726E5768697465737061636500437265617465496E7374616E6365006765745F476574496E7374616E63650064656661756C74496E7374616E636500696E7374616E63650047657448617368436F64650049456E756D657261626C650052756E74696D655479706548616E646C65004765745479706546726F6D48616E646C650053696E676C654C696E65004D756C74694C696E650052656745784573636170650047657454797065006765745F43756C74757265007365745F43756C74757265007265736F7572636543756C74757265004578706C6963697443617074757265004170706C69636174696F6E42617365004170706C69636174696F6E53657474696E6773426173650049676E6F7265436173650046616C736500456469746F7242726F777361626C65537461746500436F6D70696C657247656E65726174656441747472696275746500477569644174747269627574650048656C704B6579776F72644174747269627574650047656E657261746564436F64654174747269627574650044656275676765724E6F6E55736572436F64654174747269627574650044656275676761626C6541747472696275746500456469746F7242726F777361626C6541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C795469746C65417474726962757465005374616E646172644D6F64756C6541747472696275746500486964654D6F64756C654E616D6541747472696275746500417373656D626C7954726164656D61726B41747472696275746500446562756767657248696464656E41747472696275746500417373656D626C7946696C6556657273696F6E417474726962757465004D7947726F7570436F6C6C656374696F6E4174747269627574650053716C46756E6374696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E7341747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C79436F6D70616E794174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C7565007365745F56616C7565004765744F626A65637456616C75650076616C7565004949660053716C537472696E6700546F537472696E6700526567457849734D617463680052656745784D61746368006D61746368006765745F4C656E677468006D617463684C656E677468004D6963726F736F66742E56697375616C42617369632E4D7953657276696365732E496E7465726E616C0053797374656D2E436F6D706F6E656E744D6F64656C00526567657853514C434C522E646C6C006765745F49734E756C6C0053797374656D007265736F757263654D616E0053716C426F6F6C65616E0053797374656D2E436F6D706F6E656E744D6F64656C2E44657369676E006765745F4170706C69636174696F6E004D794170706C69636174696F6E0052656745784F7074696F6E456E756D65726174696F6E0053797374656D2E436F6E66696775726174696F6E0053797374656D2E476C6F62616C697A6174696F6E00496E746572616374696F6E0053797374656D2E5265666C656374696F6E004D61746368436F6C6C656374696F6E007061747465726E0043756C74757265496E666F006D5F4170704F626A65637450726F7669646572006D5F557365724F626A65637450726F7669646572006D5F436F6D70757465724F626A65637450726F7669646572006D5F4D7957656253657276696365734F626A65637450726F7669646572006765745F5265736F757263654D616E6167657200546F496E74656765720053797374656D2E436F6465446F6D2E436F6D70696C6572006765745F55736572006765745F436F6D7075746572004D79436F6D7075746572004D6963726F736F66742E53716C5365727665722E53657276657200416374697661746F72002E63746F72002E6363746F720053797374656D2E446961676E6F7374696373004D6963726F736F66742E56697375616C42617369632E44657669636573006765745F5765625365727669636573004D795765625365727669636573004D6963726F736F66742E56697375616C42617369632E4170706C69636174696F6E53657276696365730053797374656D2E52756E74696D652E496E7465726F705365727669636573004D6963726F736F66742E56697375616C42617369632E436F6D70696C657253657276696365730053797374656D2E52756E74696D652E436F6D70696C657253657276696365730053797374656D2E5265736F757263657300526567657853514C434C522E4D792E5265736F757263657300526567657853514C434C522E5265736F75726365732E7265736F757263657300446562756767696E674D6F6465730052656745784D6174636865730053797374656D2E446174612E53716C5479706573006765745F53657474696E6773004D7953657474696E6773005265666572656E6365457175616C7300436F6E76657273696F6E730053797374656D2E546578742E526567756C617245787072657373696F6E730053797374656D2E436F6C6C656374696F6E7300526567756C617245787072657373696F6E46756E6374696F6E730052656765784F7074696F6E730052756E74696D6548656C70657273004F70657261746F7273004F724F626A656374004D7950726F6A656374005269676874546F4C656674006F705F496D706C69636974006F705F4578706C6963697400526567457853706C6974006765745F44656661756C740043756C74757265496E76617269616E74007265706C6163656D656E740045434D4153637269707400696E707574006D5F436F6E74657874004E6578744D617463686564526F77004E65787453706C6974526F770052656745785265706C61636578006765745F496E646578006D61746368496E646578005265674578496E64657800526567657800526567657853514C434C522E4D79006765745F417373656D626C7900456D707479004D7953657474696E677350726F70657274790000002B52006500670065007800530051004C0043004C0052002E005200650073006F007500720063006500730000000000EA0C0AE91F17A54699B3CBC6AE7560C200042001010803200001052001011111042001010E0420010102052002010E0E05200101113D072004010E0E0E0E0615122801120C061512280112080615122801125D061512280112240420001300050002021C1C07000112651180A90520001280AD072002010E1280AD0800011280B11280B1032000020600031C021C1C0500021C1C1C040001081C0600011180890808070211809111809502060E0600011180910E060001081180890320000E0A00031280A50E0E11809508070211808D118095040611808D080003020E0E11809506000111808D02080702118089118095032000080507011180910400010E0E0807021280A11180950900031D0E0E0E1180950400010E1C04061180910900040E0E0E0E1180950A00031280CD0E0E1180950507011280A50400011C1C042001021C0407011E00021E00051001001E00040A011E00040701130006151228011300070615126901130006151269011300021300040A01130005200101130008B77A5C561934E08908B03F5F7F11D50A3A070615122801120C0706151228011208070615122801125D0706151228011224030612790306127D0306121803000001040000120C0400001208040000125D04000012240400001279040000127D05000101127D040000121820000911808911808D11808D11808D11808D11808D11808D11808D11808D11808D0E00031180911180911180911180890E000311808D1180911180911180890E00031180891180911180911180890800011180911180910E00031280A1118091118091118089080002011C101180910E0003118091118091118091118091110004118091118091118091118091118089100004011C1011809110118089101180890420001265071001011E001E000730010101101E00040800120C0408001208040800125D04080012240408001279040800127D040800121804280013000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000200000000001001000B526567657853514C434C52000005010000000017010012436F7079726967687420C2A920203230313800002901002461383363666565652D346338612D343739372D393361332D36353530306435326431613100000C010007312E302E302E3000000401000000080100010000000000240100020054020F497344657465726D696E697374696301540209497350726563697365018179010006005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E044E616D650A526567457853706C697454557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D4461746141636365737300000000540E1146696C6C526F774D6574686F644E616D650C4E65787453706C6974526F77815A010005005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E044E616D650C52656745785265706C61636554557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D4461746141636365737300000000815B010005005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E044E616D650D52656745785265706C6163657854557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D4461746141636365737300000000817D010006005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E044E616D650C52656745784D61746368657354557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D4461746141636365737300000000540E1146696C6C526F774D6574686F644E616D650E4E6578744D617463686564526F771801000A4D7954656D706C6174650831312E302E302E3000004001003353797374656D2E5265736F75726365732E546F6F6C732E5374726F6E676C7954797065645265736F757263654275696C64657207342E302E302E3000005901004B4D6963726F736F66742E56697375616C53747564696F2E456469746F72732E53657474696E677344657369676E65722E53657474696E677353696E676C6546696C6547656E657261746F720831312E302E302E3000006101003453797374656D2E5765622E53657276696365732E50726F746F636F6C732E536F617048747470436C69656E7450726F746F636F6C124372656174655F5F496E7374616E63655F5F13446973706F73655F5F496E7374616E63655F5F0000001001000B4D792E436F6D707574657200001301000E4D792E4170706C69636174696F6E00000C0100074D792E5573657200001301000E4D792E576562536572766963657300001001000B4D792E53657474696E67730000000000B4000000CECAEFBE01000000910000006C53797374656D2E5265736F75726365732E5265736F757263655265616465722C206D73636F726C69622C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038392353797374656D2E5265736F75726365732E52756E74696D655265736F7572636553657402000000000000000000000050414450414450B4000000000000008B1BF65A00000000020000001C010000B8450000B827000052534453A95DB969E74E964EBD17469C265F150101000000453A5C56422E4E45545C526567657853514C434C525C526567657853514C434C525C6F626A5C52656C656173655C526567657853514C434C522E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FC46000000000000000000001647000000200000000000000000000000000000000000000000000008470000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000586000002C03000000000000000000002C0334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B0048C020000010053007400720069006E006700460069006C00650049006E0066006F0000006802000001003000300030003000300034006200300000001A000100010043006F006D006D0065006E007400730000000000000022000100010043006F006D00700061006E0079004E0061006D006500000000000000000040000C000100460069006C0065004400650073006300720069007000740069006F006E000000000052006500670065007800530051004C0043004C0052000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000040001000010049006E007400650072006E0061006C004E0061006D006500000052006500670065007800530051004C0043004C0052002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100380000002A00010001004C006500670061006C00540072006100640065006D00610072006B00730000000000000000004800100001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000052006500670065007800530051004C0043004C0052002E0064006C006C00000038000C000100500072006F0064007500630074004E0061006D0065000000000052006500670065007800530051004C0043004C0052000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004000000C000000283700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
    WITH PERMISSION_SET = SAFE
    GO

    Thursday, February 28, 2019 5:27 PM

All replies

  • There are some restrictions with CLR in Managed Instance, so lets communicate those:

    A Managed Instance can't access file shares and Windows folders, so the following constraints apply:

    So, not sure if the above restrictions are preventing your stored procedure from running correctly. You do have CLR functionality but there are some limitations.

    Regards,

    Mike


    Thursday, February 28, 2019 10:56 PM
    Moderator
  • Hello,

    Please recompile the source code with "Any CPU" option instead of choosing x64 or x86.


    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Friday, March 1, 2019 1:00 PM
  • Thank you for the reply Mike. I did not create the assembly in the MI database, it was created in an on-prem database which was migrated to MI with the Migration Service. It appears that the only problem is that somewhere in the system tables it is still seen as loading from a file, even though the varbinary representation is also loaded into the database, which I verified by scripting out the assembly as CREATE using SSMS.  I also verified this by looking at the sys.assembly_files system view in the content column. Note that this view also shows the name as the full path and filename of the original assembly dll. Is there any way to update the system tables to make SQL Server use the varbinary rather than looking for a file that does not exist and frankly, is not needed?

    I tried doing DROP and CREATE using the varbinary, but for some reason it changes the syntax of the EXTERNAL NAME clause so that I cannot create the CLR functions any more. I keep getting an error that reads:

    Msg 6505, Level 16, State 2, Procedure RegexIndex, Line 2
    Could not find Type 'RegexSQLCLR.RegularExpressionFunctions' in assembly 'RegexFunctions'.

    even though that is the exact syntax used in the original creation of the functions, and also exists when I script them out as CREATE using SSMS.

    Friday, March 1, 2019 10:54 PM
  • Thanks for the reply Alberto. The code was already compiled with the Any CPU option. But I will try to compile it again. At this point I am getting desperate!
    Friday, March 1, 2019 10:56 PM
  • Hi cb-Ron,

    Do you have an Azure Support Plan? if you do, it would be best to engage them to have this addressed. If you do not have an Azure Support Plan, I can have one created for you if you send your Subscription ID + link to this thread, to AzCommunity at Microsoft.com. Instructions will be returned to have the support requested created. 

    Thanks,

    Mike

    Friday, March 1, 2019 11:08 PM
    Moderator
  • I used the Migration Service to move an on-premise SQL Server 2008 R2 database to Managed Instance. Everything went smoothly. But I have a stored procedure that uses functions from a CLR assembly (RegEx - enormously useful in SQL Server!). I receive the following error when I run the sp, even if I run it in SSMS under my sysadmin account.

    Msg 10314, Level 16, State 11, Procedure dbo.globalSearch, Line 22 [Batch Start Line 2]
    An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the assembly trust issues.

    Hi there. While understandably annoying and frustrating, this is also, fortunately, a simple error to fix (one that certainly does not require paying for Azure support, especially when they should have known by the error number itself what the problem was!).

    The problem is this:

    1. You have an unsigned assembly. The assembly was most likely not signed due to not needing to be signed as there was no requirement for SAFE assemblies to be signed until SQL Server 2017 introduced "CLR strict security", and you have been running on 2008 R2.
    2. Now that you are moving to Azure SQL Database Managed Instance, all assemblies, even SAFE assemblies, need to be signed. Normally you would have gotten the following error attempting to execute CREATE ASSEMBLY:
      Msg 10343, Level 14, State 1, Line 2
      CREATE or ALTER ASSEMBLY for assembly 'RegexSQLCLR' with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly.

      However, you didn't run into that (at least not at first) since the assembly was already loaded due to it existing in a database that was being restored into a newer version of SQL Server. That is why you instead got error 10314.

    Fixing this is easy:

    1. IF the assembly does NOT exist in the database anymore:
      1. set the database to TRUSTWORTHY ON (this is temporary!)
      2. load the assembly
      3. set the database back to TRUSTWORTHY OFF
    2. create a certificate (protected with a password, not the database master key) in the database that contains the assembly
    3. signed the assembly with that certificate
    4. copy the certificate (public key only) to the [master] database
    5. create a login from the certificate
    6. grant the login the UNSAFE ASSEMBLY permission

    I have a demo script going step-by-step through this process on PasteBin:  Avoiding "Trusted Assemblies" - Demo

    And a related blog post explaining it in detail: "SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment"

    Of course, another option that is also quite easy is to simply install SQL# (Free version is fine for this), a library of SQLCLR functions and procedures that I created. There are more RegEx functions than what is contained in your RegExFunction assembly (and yes, I created the assembly on my system and saw all 8 functions plus a helper function for the RegEx options). All of the SQL# assemblies are signed, and all install from the SQL script.

    Take care,
    Solomon...

    _____________________________________________________________
    SQL# - https://SQLsharp.com/ ( SQLCLR library of over 340 Functions and Procedures )
    Sql Quantum Lift - https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap - https://SqlQuantumLeap.com/ ( blog )

    _____________________________________________________________



    Sunday, June 30, 2019 8:56 PM
  • I tried doing DROP and CREATE using the varbinary, but for some reason it changes the syntax of the EXTERNAL NAME clause so that I cannot create the CLR functions any more. I keep getting an error that reads:

    Msg 6505, Level 16, State 2, Procedure RegexIndex, Line 2
    Could not find Type 'RegexSQLCLR.RegularExpressionFunctions' in assembly 'RegexFunctions'.

    even though that is the exact syntax used in the original creation of the functions, and also exists when I script them out as CREATE using SSMS.

    This might be due to the internal name of the assembly being different than what was specified in your CREATE ASSEMBLY statement. You have CREATE ASSEMBLY [RegexFunction] while the name of the assembly is truly RegexSQLCLR. The EXTERNAL NAME clause should reference:

    [AssemblyName].[[optionalNameSpaceName.]ClassName].[MethodName]

    AssemblyName = what you have in the CREATE ASSEMBLY statement, or in the [name] column in sys.assemblies .

    The following worked for me after creating your assembly in SQL Server 2017 as "RegexFunction" (no "s" at the end, just like you shown in the main question)

    GO
    CREATE FUNCTION [dbo].[RegexIndex] (@pattern NVARCHAR(4000), @input NVARCHAR(4000), @Options INT)
    RETURNS INT
    AS EXTERNAL NAME [RegexFunction].[RegexSQLCLR.RegularExpressionFunctions].[RegExIndex];
    GO


    Take care,
    Solomon...

    _____________________________________________________________
    SQL# - https://SQLsharp.com/ ( SQLCLR library of over 340 Functions and Procedures )
    Sql Quantum Lift - https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap - https://SqlQuantumLeap.com/ ( blog )

    _____________________________________________________________

    Sunday, June 30, 2019 10:19 PM