Locking down CLR
I recently ran into SQL 2017’s new CLR Strict Security. I was working with a client where we were optimizing some queries. One of the queries had a dynamically coded IN clause. In this case, I like to use a string splitting routine to split the IN clause into different values, and to put them into a temporary table to later join to. My string splitter of choice is here. This is one of the most efficient string splitters that I have run into, but it has one limitation: it won’t handle MAX data types. And at this client, the strings being created in the IN clause would exceed 8000 characters, requiring a MAX data type.
In this circumstance, I reverted to using a CLR string splitter. In the same article, you can download all the sample code, including a very efficient CLR string splitter. The direct link is here.
When we were installing this CLR string splitter on SQL Server 2017, we ran into a problem. SQL 2017 has beefed up the CLR security. This was preventing us from being able to install the CLR assembly.
SQL 2017 CLR Strict Security
Why did Microsoft make these changes? These changes make CLR, and thus SQL Server, more secure. In their “CLR strict security” article, they explain why:
CLR uses Code Access Security (CAS) in the .NET Framework, which is no longer supported as a security boundary. A CLR assembly created with
PERMISSION_SET = SAFEmay be able to access external system resources, call unmanaged code, and acquire sysadmin privileges. Beginning with SQL Server 2017 (14.x), ansp_configureoption calledclr strict securityis introduced to enhance the security of CLR assemblies.clr strict securityis enabled by default, and treatsSAFEandEXTERNAL_ACCESSassemblies as if they were markedUNSAFE. Theclr strict securityoption can be disabled for backward compatibility, but this is not recommended. Microsoft recommends that all assemblies be signed by a certificate or asymmetric key with a corresponding login that has been grantedUNSAFE ASSEMBLYpermission in the master database. SQL Server administrators can also add assemblies to a list of assemblies, which the Database Engine should trust. For more information, see sys.sp_add_trusted_assembly.
The article goes on to explain the permissions needed to create a CLR assembly starting in SQL 2017:
The following permissions required to create a CLR assembly when
CLR strict securityis enabled:
-
The user must have the
CREATE ASSEMBLYpermission -
And one of the following conditions must also be true:
-
The assembly is signed with a certificate or asymmetric key that has a corresponding login with the
UNSAFE ASSEMBLYpermission on the server. Signing the assembly is recommended. -
The database has the
TRUSTWORTHYproperty set toON, and the database is owned by a login that has theUNSAFE ASSEMBLYpermission on the server. This option is not recommended.
-
Now, creating a signed certificate might be easy enough to do. But there are a lot of steps in actually doing this. You can read all about this here.
Is there another way?
That’s a lot of steps to go through just to create an assembly on your SQL Server instance. Let’s not even start thinking about having dozens of assemblies. Thankfully, Microsoft provided another way to do this within the CLR strict security framework. You can mark an assembly as trusted, using the sys.sp_add_trusted_assembly system stored procedure. This procedure adds the assembly to the trusted assemblies in sys.trusted_assemblies. This procedure has one required and one optional parameter. The required parameter is the SHA2_512 hash value of the assembly to add to the list of trusted assemblies for the server. The optional parameter is a user-defined description of the assembly.
Perhaps the easiest way to get the hash value is to install this assembly on a lower version of SQL Server, then script it out. Once we have that, we can use the HASHBYTES function to create the SHA2_512 hash value. Now that we have everything that we need, we can go ahead and call sys.sp_add_trusted_assembly. The following code performs all the needed actions, using the CLR splitter from the above article:
|
1 2 3 4 5 6 7 8 9 |
DECLARE @clrDescription nvarchar(4000) = N'split, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil'; DECLARE @clrBin varbinary(max) = 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030024E9B34D0000000000000000E00002210B010800000E00000006000000000000AE2D0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000582D000053000000004000009003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B40D000000200000000E000000020000000000000000000000000000200000602E7273726300000090030000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000902D0000000000004800000002000500E0210000780B00000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000A2026F1300000A2D0E026F1400000A0373040000062B0C168D140000011673040000068C030000022A000000133002001A0000000100001102A5040000020A031200280800000654041200280A000006512A1E02281500000A2AC202037D0100000402047D0300000402027B010000048E697D02000004027C05000004FE150400000202167D040000042A0013300500C200000002000011027B04000004027B020000043302162A027B040000040A2B56027B010000040693027B030000043342027C0500000425280800000617582809000006027C05000004027B01000004027B0400000406027B0400000459731700000A280B000006020617587D04000004172A0617580A06027B0200000432A1027C0500000425280800000617582809000006027C05000004027B01000004027B04000004027B02000004027B0400000459731700000A280B00000602027B020000047D04000004172A32027B050000048C040000022A1A731800000A7A1E027B060000042A2202037D060000042A1E027B070000042A2202037D070000042A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000CC030000237E0000380400008404000023537472696E677300000000BC0800000800000023555300C4080000100000002347554944000000D4080000A402000023426C6F6200000000000000020000015717A2030902000000FA253300160000010000001900000004000000070000000B00000009000000010000001900000012000000020000000200000003000000050000000300000001000000020000000200000000000A00010000000000060052004B00060059004B000600760063000A00A3008E000A0029020E0206007102520206009B0289020600B20289020600CF0289020600EE02890206000703890206002003890206003B03890206005603890206006F0352020600830389020600BC039C030600DC039C030A00FA030E02060024044B0006002904520206003F04520206004A044B00060051044B00060069049C03000000000100000000000100010001001000140000000500010001000B011000290000000900010004000B01100039000000090006000800210053012E0021005901320021006001350001006A0132000100700138000100CA0132000100E40152005020000000009600AC000A0001007C20000000009600B20012000300A220000000008618BA001B000600AA20000000008318BA001F000600DC2000000000E101C00026000800AA2100000000E109F1002A000800B72100000000E10128011B000800BE210000000083089E0140000800C621000000008308AB0144000800CF21000000008308B80149000900D721000000008308C1014D000900000001000802000002003B02000001004502020002004902020003007E02000001000802000002003B0200000100830200000100830203000D001900E800260019001C012A0019004D011B002900BA001B003100BA001B003900BA004D004100BA004D004900BA004D005100BA004D005900BA004D006100BA004D006900BA004D007100BA004D007900BA0070008100BA004D008900BA0044009100BA001B009900BA001B0021000F04260021001A040D020900BA001B00A900BA001702B900BA001D02C100BA001B00C900BA001B00200093007500240023005D002E0033002E022E00430045022E008B0084022E004B004B022E0053002E022E00730045022E003B0045022E0083007B022E005B005A022E0063004502C100CB002902E100CB0029020001CB0029022001CB0029024001CB0029026001CB002902120225020300010004000200000077013C000000FA01550000000302590002000600030001000900050002000800050002000A00070001000B00070003000A00030003000C00050003000E00070004800000010000002310FA9B000000000000AC00000002000000000000000000000001004200000000000200000000000000000000000100820000000000030002000400020000000000003C4D6F64756C653E0053706C69742E646C6C0055736572446566696E656446756E6374696F6E730053706C6974456E756D657261746F720053706C6974526F77006D73636F726C69620053797374656D004F626A6563740056616C7565547970650053797374656D2E436F6C6C656374696F6E730049456E756D657261746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053706C69740046696C6C526F77002E63746F720053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E4D6F76654E657874004D6F76654E6578740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E6765745F43757272656E74006765745F43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E526573657400526573657400696E707574006C656E6774680064656C696D69746572007374617274007265636F72640053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E43757272656E74006765745F53657175656E6365007365745F53657175656E6365006765745F4974656D007365745F4974656D003C53657175656E63653E6B5F5F4261636B696E674669656C64003C4974656D3E6B5F5F4261636B696E674669656C640053657175656E6365004974656D00496E707574004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650044656C696D69746572006F626A0073657175656E63650053797374656D2E52756E74696D652E496E7465726F705365727669636573004F7574417474726962757465006974656D0076616C75650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C46756E6374696F6E417474726962757465006765745F49734E756C6C006765745F56616C75650043686172005374727563744C61796F7574417474726962757465004C61796F75744B696E6400537472696E67004E6F74496D706C656D656E746564457863657074696F6E00436F6D70696C657247656E657261746564417474726962757465000003200000000000901C02C60145A34EACF1D06C744C88640008B77A5C561934E089070002120D121103080003011C1008100E03200001062002011D0303032000020320001C03061D03020608020603030611100328001C0320000804200101080320000E042001010E02060E032800080328000E12010001005408074D617853697A65FFFFFFFF04200101028196010006005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E1146696C6C526F774D6574686F644E616D650746696C6C526F77540E0F5461626C65446566696E6974696F6E2173657175656E636520494E542C206974656D204E564152434841522834303030290420001D030407011110052001011159072003011D0308080307010804010000001601001153716C53657276657250726F6A6563743100000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313100000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100802D000000000000000000009E2D0000002000000000000000000000000000000000000000000000902D000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000380300000000000000000000380334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100FA9B231000000100FA9B23103F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00498020000010053007400720069006E006700460069006C00650049006E0066006F00000074020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F006600740000004C0012000100460069006C0065004400650073006300720069007000740069006F006E0000000000530071006C00530065007200760065007200500072006F006A006500630074003100000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003100330031002E00330039003900330030000000000034000A00010049006E007400650072006E0061006C004E0061006D0065000000530070006C00690074002E0064006C006C0000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F006600740020003200300031003100000000003C000A0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530070006C00690074002E0064006C006C000000440012000100500072006F0064007500630074004E0061006D00650000000000530071006C00530065007200760065007200500072006F006A006500630074003100000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003100330031002E00330039003900330030000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003100330031002E00330039003900330030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000B03D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000; DECLARE @hash varbinary(64); SET @hash = HASHBYTES('SHA2_512', @clrBin); SELECT @hash; EXECUTE sys.sp_add_trusted_assembly @hash, @clrDescription SELECT * FROM sys.trusted_assemblies; |
Now, you can go ahead and create the assembly, add the necessary functions, and start using it.
References:
A couple of reference links that I came across while figuring all of this out:
//nielsberglund.com/post/2017-07-23-sql-server-2017-sqlclr-white-listing-assemblies/
//www.nielsberglund.com/2017/07/02/sql-server-2017-sqlclr-and-permissions/
