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/
Hi Wayne. Please don’t use the “Trusted Assemblies” feature. It is an entirely inferior and unnecessary “feature” that only exists because Microsoft needed something to provide some level of security for the few remaining assemblies that were allowed to exist once they pulled SQLCLR support from Azure SQL Database back in April of 2016. And it only exists in non-Azure SQL Server because existing DBs with unsigned assemblies that were either upgraded to 2017 or restored into 2017 were getting the “not trusted” error, and nobody at Microsoft knew that assemblies that were already loaded into SQL Server could be signed without needing to export, sign, and re-import. It was merely a knowledge oversight, and they had the “trusted assemblies” code that had been added for Azure so they un-hid it. It is something that never should have existed, and does a poor job of solving a problem that was solved back in SQL Server 2005. I cover all of this here:
//sqlquantumleap.wordpress.com/2017/08/07/sqlclr-vs-sql-server-2017-part-1-clr-strict-security/
Creating a certificate is really not that hard, and shouldn’t be discouraged since it is the optimal method of providing security for assemblies. And this hasn’t changed since SQLCLR was introduced in SQL Server 2005. In fact, Part 4 of my “SQLCLR vs SQL Server 2017” series is about signing existing unsigned assemblies, and it has example code showing how to do it.
Also, with regards to splitting strings:
a) this is built into SQL Server 2016 and newer via STRING_SPLIT, but that doesn’t return a Row / Element ID, so that is not usable for some folks / scenarios,
b) the SQLCLR library that I created, SQL# ( //SQLsharp.com/ ), comes with a few string splitters. Starting in version 4.0, I added String_SplitInts which is for splitting a list of BIGINT / INT / SMALLINT / TINYINT values. It does the conversion to BIGINT in the .NET code so you get a result set of BIGINT back instead of strings. This is very fast for both CPU and elapsed times. It even includes an option for returning a single row of NULL if the input is NULL (some folks seem to like that). And when the input is known to never be over 4k characters, the “4k” version is even faster. I am mentioning SQL# because this is in the Free version, and the security is handled properly (using signatures) so no extra effort needs to be done to get it installed. It just works :).
Take care, Solomon..
Reply