"clr strict security" - Solution 1

/*
 --------- TEST FOR SQLCLR on SQL Server 2017 (Windows and Linux) ---------

By: Solomon Rutzky
On: 2017-08-04
Location: https://pastebin.com/vY9GgH8F
Related blog post: https://SqlQuantumLeap.com/2017/08/09/sqlclr-vs-sql-server-2017-part-2-clr-strict-security-solution-1/

Description: This script shows a technique for deploying SQLCLR Assemblies to SQLCLR 2017
             while both keeping "clr strict security" enabled and "TRUSTWORTHY" disabled,
             AND while being fulled contained within a SQL script, AND working with SSDT.

             The trick is to first create a separate Project / Assembly that is empty but
             is also signed. That will be used to create the Asymmetric Key in [master].

             The second part of the trick is to also sign that empty Assembly with a
             Certificate. This will allow for the empty Assembly to be created.

             For a detailed, step-by-step explanation, please see the relatd blog post
             noted above.
*/



USE [master];
SET NOCOUNT ON;
GO

IF (DB_ID(N'SQLCLRvsClrStrictSecurity') IS NULL)
BEGIN
    PRINT 'Creating Database...';
    CREATE DATABASE [SQLCLRvsClrStrictSecurity]
        COLLATE Latin1_General_100_CI_AS;
END;

IF (DB_ID(N'SQLCLRvsClrStrictSecurity') IS NOT NULL)
BEGIN
    PRINT 'Altering Database...';
    ALTER DATABASE [SQLCLRvsClrStrictSecurity]
        SET RECOVERY SIMPLE,
        TRUSTWORTHY OFF;
END;


PRINT 'Making sure that SQLCLR is enabled...';
EXEC sp_configure N'clr enabled', 1; RECONFIGURE;

PRINT 'Making sure that SQLCLR "strict security" is enabled...';
EXEC sp_configure N'show advanced options', 1; RECONFIGURE;
EXEC sp_configure N'clr strict security', 1; RECONFIGURE;




IF (SUSER_ID(N'SQL2017-ClrStrictSecurity-Login') IS NULL)
BEGIN
    PRINT 'Permission Login does NOT exist:';

    IF (CERT_ID(N'SQL2017-ClrStrictSecurity-Cert') IS NULL)
    BEGIN
        PRINT 'Creating Certificate...';
        CREATE CERTIFICATE [SQL2017-ClrStrictSecurity-Cert]
FROM BINARY = 0x\
30820310308201FCA0030201020210170A6F5B690D95A0402670F83588A54F300906052B0E03021D\
0500301D311B30190603550403131253716C5175616E74756D4C6561702E636F6D3020170D313730\
3830353037313133335A180F32303939313233313034303030305A301D311B301906035504031312\
53716C5175616E74756D4C6561702E636F6D30820122300D06092A864886F70D0101010500038201\
0F003082010A0282010100C3F1C860C555A61F510AD500B185FA9308E3B3D5A64EBA56B2002329EE\
2F3D9B7A8FD803E21668285EC2C18D4018F211329145C201B827782FB086A6688B08EFCD80D915F3\
A8BEA4550206F1232A0086D154632BC8FDE09F5B24EBA25598F2F5DA255093D1C03E8D67F2F305FE\
AFF09EEFC7F1FAD2D6A06D65EF2AA9168802A254B77E62EFD117EF466C3293E1DB6FB8E51BE618AA\
6D7B1C58D05712962C17A123DD554EFF6496E9A1FB8C24C706B6AEC7BAFE606B46E31DD5CDDE23A0\
71DF30D181BEC9416F1B52AF5FF9AEF15A93FF4D79427AF76EF1BC85B3581A7C55FC87446173F2A0\
6DBD96BD59BBD74BF7C8997F8CF0AC8A8FE44AA477E2450681AD670203010001A3523050304E0603\
551D01044730458010A817C71654DDEDACE2F9E5DDE3786D5FA11F301D311B301906035504031312\
53716C5175616E74756D4C6561702E636F6D8210170A6F5B690D95A0402670F83588A54F30090605\
2B0E03021D050003820101001734E296A9978F17108A96354A40D664E46FD32E76227B9378AEE1BF\
6189FB4ED1F638A96775EE2613D3FFCBDECAFD8B265BBDDD3841C541F4DC842C8807A0A81469990C\
EBEA093ABCD5A1FD0ABDE391B3F068B8D42F7A1F1E467B39A610D4C60CDFFEE138CA6610935FD7F5\
26CF3195ED2940FF79FA9D97103C9859526C749471046C9C325CE00100371E9EABF520EC70ADDD1C\
D298DCC8E2F2C6851B2627FBFAB60B868EC57C3E702C0E58F5741E3EFEA997345D395D70E2249E2D\
324698853B3BAD0DE28E81B74C386C6A16F41F1F2B2E078D22A90D56669C7F3BD532D363C1BA54FD\
DF30825A25A27ED010FF5353D10586EF4622CA67E736E4C05162D80C
    END;

    IF (SUSER_ID(N'SQL2017-ClrStrictSecurity-TempLogin') IS NULL)
    BEGIN
        PRINT 'Temporary Login does NOT exist:';
        CREATE LOGIN [SQL2017-ClrStrictSecurity-TempLogin]
            FROM CERTIFICATE [SQL2017-ClrStrictSecurity-Cert];
    END;

    -- REVOKE UNSAFE ASSEMBLY FROM [SQL2017-ClrStrictSecurity-TempLogin];
    GRANT UNSAFE ASSEMBLY TO [SQL2017-ClrStrictSecurity-TempLogin]; -- REQUIRED!!!!

    IF (ASSEMBLYPROPERTY(N'SQL2017-ClrStrictSecurity-KeyAsm', N'MvID') IS NULL)
    BEGIN
        PRINT ' Creating Assembly that holds the Asymmetric Key...';
        CREATE ASSEMBLY [SQL2017-ClrStrictSecurity-KeyAsm]
            FROM 0x\
4D5A90000300000004000000FFFF0000B80000000000000040000000000000000000000000000000\
0000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD215468\
69732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A\
2400000000000000504500004C010300505085590000000000000000E00002210B010B0000080000\
0008000000000000CE27000000200000004000000000001000200000000200000400000000000000\
06000000000000000080000000020000E09600000300608500001000001000000000100000100000\
00000000100000000000000000000000782700005300000000400000200400000000000000000000\
0012000048050000006000000C000000402600001C00000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000200000080000000000000000000000\
082000004800000000000000000000002E74657874000000D4070000002000000008000000020000\
000000000000000000000000200000602E72737263000000200400000040000000060000000A0000\
000000000000000000000000400000402E72656C6F6300000C000000006000000002000000100000\
0000000000000000000000004000004200000000000000000000000000000000B027000000000000\
4800000002000500D020000070050000090000000000000000000000000000005020000080000000\
0000000000000000000000000000000000000000000000000000000000000000176AC7AEEFDF17CB\
AA56763437384545BB93D9435026E4FA74E1F58627354F933210279E295E86B3AD6611A60FF51FAF\
65B432F08C086A3EC0B77A61DEBB67A2A41B362B38A678FA9F69C4A94C8FEDB42C707F2D175B11C9\
0FD4FD85DF816F1B6C5D3358D3457FD4795CE95513D6DB5DCA51A110E2A640258C27CE6AC161D451\
42534A4201000100000000000C00000076342E302E33303331390000000005006C0000005C010000\
237E0000C8010000EC01000023537472696E677300000000B40300000800000023555300BC030000\
100000002347554944000000CC030000A401000023426C6F62000000000000000200000107140000\
0900000000FA253300160000010000000E000000010000000D0000000B0000000100000001000000\
00000A000100000000000600380026000600550026000600720026000600910026000600AA002600\
0600C30026000600DE0026000600F900260006001201260006002B01260006005B0148012F006F01\
000006009E017E010600BE017E01000000000100000000000100010009004F000A0011004F000A00\
19004F000A0021004F000A0029004F000A0031004F000A0039004F000A0041004F000A0049004F00\
0A0051004F000A0059004F000F0069004F00150071004F001A002E000B00C0002E001300DB002E00\
1B00F9002E002300FF002E002B0015012E00330029012E003B00F9002E00530066012E005B007301\
2E0063007C012E006B008501048000000100000000000000010000001E00DC010000040000000000\
00000000000001001D00000000000000003C4D6F64756C653E0053514C323031375F4B657941736D\
2E646C6C006D73636F726C69620053797374656D2E5265666C656374696F6E00417373656D626C79\
5469746C65417474726962757465002E63746F7200417373656D626C794465736372697074696F6E\
41747472696275746500417373656D626C79436F6E66696775726174696F6E417474726962757465\
00417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F647563\
7441747472696275746500417373656D626C79436F70797269676874417474726962757465004173\
73656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C74757265\
41747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D\
626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F737469\
63730044656275676761626C6541747472696275746500446562756767696E674D6F646573005379\
7374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F\
6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69\
74794174747269627574650053514C323031375F4B657941736D0000000320000000000050D5633F\
80DD834B8AEEF46CEAC66A850008B77A5C561934E089042001010E05200101113104200101080320\
000180A00024000004800000940000000602000000240000525341310004000001000100DFB9D86A\
F437605A8736C7A36E5EF9DE45A64F56C3673CC0A37DACEACD64F830A74C06147998309BF902665A\
51C7C81843D4C95466B4D59B9E790245A0C05B498753CD075F2C6B1EB40E0E478E73DC3F12929C8B\
529B9805D10AE58A8945B99AB5BBE885AAB2732892D5F063AE6B7B3F4E0EF71E9FC484E87FD062DA\
56DFE1D01A01001553514C2032303137204B657920417373656D626C7900001D0100184A75737420\
746865207374726F6E67206E616D65206B657900000501000000001501001053716C205175616E74\
756D204C65617000001301000E53514C323031375F4B657941736D00003C010037436F7079726967\
687420286329203230313720536F6C6F6D6F6E205275747A6B792E20416C6C207269676874732072\
657365727665642E00000C010007312E302E302E3000000801000200000000000801000800000000\
001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000050508559\
00000000020000001C0100005C2600005C080000525344532C6CED9E5E15FF43B030186868E50A40\
01000000633A5C53514C5175616E74756D4C6561705C50726F6A656374735C426C6F67546F706963\
735C3731375C53514C323031375F436C7253747269637453656375726974795C53514C323031375F\
4B657941736D5C6F626A5C52656C656173655C53514C323031375F4B657941736D2E706462000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000A02700000000000000000000BE270000\
002000000000000000000000000000000000000000000000B0270000000000000000000000000000\
00005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000100100000001800008000000000000000000000000000000100\
010000003000008000000000000000000000000000000100000000004800000058400000C8030000\
0000000000000000C80334000000560053005F00560045005200530049004F004E005F0049004E00\
46004F0000000000BD04EFFE00000100000001000000000000000100000000003F00000000000000\
0400000002000000000000000000000000000000440000000100560061007200460069006C006500\
49006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00\
000000000000B00428030000010053007400720069006E006700460069006C00650049006E006600\
6F0000000403000001003000300030003000300034006200300000004C001900010043006F006D00\
6D0065006E007400730000004A00750073007400200074006800650020007300740072006F006E00\
670020006E0061006D00650020006B00650079000000000044001100010043006F006D0070006100\
6E0079004E0061006D00650000000000530071006C0020005100750061006E00740075006D002000\
4C0065006100700000000000540016000100460069006C0065004400650073006300720069007000\
740069006F006E0000000000530051004C002000320030003100370020004B006500790020004100\
7300730065006D0062006C0079000000300008000100460069006C00650056006500720073006900\
6F006E000000000031002E0030002E0030002E003000000048001300010049006E00740065007200\
6E0061006C004E0061006D0065000000530051004C0032003000310037005F004B00650079004100\
73006D002E0064006C006C00000000009400380001004C006500670061006C0043006F0070007900\
72006900670068007400000043006F00700079007200690067006800740020002800630029002000\
3200300031003700200053006F006C006F006D006F006E0020005200750074007A006B0079002E00\
200041006C006C002000720069006700680074007300200072006500730065007200760065006400\
2E0000005000130001004F0072006900670069006E0061006C00460069006C0065006E0061006D00\
65000000530051004C0032003000310037005F004B0065007900410073006D002E0064006C006C00\
0000000040000F000100500072006F0064007500630074004E0061006D0065000000000053005100\
4C0032003000310037005F004B0065007900410073006D0000000000340008000100500072006F00\
6400750063007400560065007200730069006F006E00000031002E0030002E0030002E0030000000\
38000800010041007300730065006D0062006C0079002000560065007200730069006F006E000000\
31002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000002000000C000000D0370000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000048050000000202003082053906092A864886F70D010702A082052A3082052602\
0101310B300906052B0E03021A0500304C060A2B060104018237020104A03E303C3017060A2B0601\
0401823702010F3009030100A004A20280003021300906052B0E03021A0500041401DA5AEF4DBC69\
8EE215F956129DD2D3F3A631D8A082031430820310308201FCA0030201020210170A6F5B690D95A0\
402670F83588A54F300906052B0E03021D0500301D311B30190603550403131253716C5175616E74\
756D4C6561702E636F6D3020170D3137303830353037313133335A180F3230393931323331303430\
3030305A301D311B30190603550403131253716C5175616E74756D4C6561702E636F6D3082012230\
0D06092A864886F70D01010105000382010F003082010A0282010100C3F1C860C555A61F510AD500\
B185FA9308E3B3D5A64EBA56B2002329EE2F3D9B7A8FD803E21668285EC2C18D4018F211329145C2\
01B827782FB086A6688B08EFCD80D915F3A8BEA4550206F1232A0086D154632BC8FDE09F5B24EBA2\
5598F2F5DA255093D1C03E8D67F2F305FEAFF09EEFC7F1FAD2D6A06D65EF2AA9168802A254B77E62\
EFD117EF466C3293E1DB6FB8E51BE618AA6D7B1C58D05712962C17A123DD554EFF6496E9A1FB8C24\
C706B6AEC7BAFE606B46E31DD5CDDE23A071DF30D181BEC9416F1B52AF5FF9AEF15A93FF4D79427A\
F76EF1BC85B3581A7C55FC87446173F2A06DBD96BD59BBD74BF7C8997F8CF0AC8A8FE44AA477E245\
0681AD670203010001A3523050304E0603551D01044730458010A817C71654DDEDACE2F9E5DDE378\
6D5FA11F301D311B30190603550403131253716C5175616E74756D4C6561702E636F6D8210170A6F\
5B690D95A0402670F83588A54F300906052B0E03021D050003820101001734E296A9978F17108A96\
354A40D664E46FD32E76227B9378AEE1BF6189FB4ED1F638A96775EE2613D3FFCBDECAFD8B265BBD\
DD3841C541F4DC842C8807A0A81469990CEBEA093ABCD5A1FD0ABDE391B3F068B8D42F7A1F1E467B\
39A610D4C60CDFFEE138CA6610935FD7F526CF3195ED2940FF79FA9D97103C9859526C749471046C\
9C325CE00100371E9EABF520EC70ADDD1CD298DCC8E2F2C6851B2627FBFAB60B868EC57C3E702C0E\
58F5741E3EFEA997345D395D70E2249E2D324698853B3BAD0DE28E81B74C386C6A16F41F1F2B2E07\
8D22A90D56669C7F3BD532D363C1BA54FDDF30825A25A27ED010FF5353D10586EF4622CA67E736E4\
C05162D80C318201AC308201A80201013031301D311B30190603550403131253716C5175616E7475\
6D4C6561702E636F6D0210170A6F5B690D95A0402670F83588A54F300906052B0E03021A0500A052\
3010060A2B06010401823702010C31023000301906092A864886F70D010903310C060A2B06010401\
8237020104302306092A864886F70D01090431160414F8CDBD63BEF919FD6752DC3A30F6D7CFEAF6\
E8D1300D06092A864886F70D0101010500048201002471AB9C11EDE18DDB9CD1E4E8A0A036556B0B\
D3CF60F09E84D94495AE7DA3672B18AE022C8ECA44D5BE17B4D3520F3B3F9AB7B42867B0C3FBF63B\
92E9B1D6BDD02F284CE43FC9FAC277D947E42E8F8A63CC99FA76698F9F378A6DA7F39D6A68526C76\
E6FF0115DAB5910D8858A5FF6186D9D58055AED9D3A7F571D8D313784EC269477F47B030E5988A7C\
D9967D81168BB8E77A1E76AF32C0112E123BFB317213D58DB5956454D7EF9FF4CD6EE8E865AC6847\
EED8E034791FE9E2640ABBB4FFE221DBC273BEFF39DD80AB91BDECC129082F7E866E3868D534DFB4\
3C4F78112818353BED62B893822F15DA2454200B53EBFA10AFD28B58F714E220B73F30DE35000000
WITH PERMISSION_SET = SAFE;
    END;

    IF (ASYMKEY_ID(N'SQL2017-ClrStrictSecurity-Key') IS NULL)
    BEGIN
        PRINT ' Creating Asymmetric Key...';
        CREATE ASYMMETRIC KEY [SQL2017-ClrStrictSecurity-Key]
            FROM ASSEMBLY [SQL2017-ClrStrictSecurity-KeyAsm];
    END;

    IF (SUSER_ID(N'SQL2017-ClrStrictSecurity-Login') IS NULL)
    BEGIN
        PRINT ' Creating permissions Login...';
        CREATE LOGIN [SQL2017-ClrStrictSecurity-Login]
            FROM ASYMMETRIC KEY [SQL2017-ClrStrictSecurity-Key];
    END;

    GRANT UNSAFE ASSEMBLY TO [SQL2017-ClrStrictSecurity-Login]; -- REQUIRED!!!!


    IF (ASSEMBLYPROPERTY(N'SQL2017-ClrStrictSecurity-KeyAsm', N'MvID') IS NOT NULL)
    BEGIN
        PRINT ' Dropping Assembly that holds the Asymmetric Key...';
        DROP ASSEMBLY [SQL2017-ClrStrictSecurity-KeyAsm];
    END;

    IF (SUSER_ID(N'SQL2017-ClrStrictSecurity-TempLogin') IS NOT NULL)
    BEGIN
        PRINT ' Dropping temporary Login...';
        DROP LOGIN [SQL2017-ClrStrictSecurity-TempLogin];
    END;

    IF (CERT_ID(N'SQL2017-ClrStrictSecurity-Cert') IS NOT NULL)
    BEGIN
        PRINT ' Dropping Certificate...';
        DROP CERTIFICATE [SQL2017-ClrStrictSecurity-Cert];
    END;
END;

--DROP LOGIN [SQL2017-ClrStrictSecurity-Login];
--DROP ASYMMETRIC KEY [SQL2017-ClrStrictSecurity-Key];


-- Verify that "clr strict security" is 1 (i.e. ON), and that TRUSTWORTHY is 0 (i.e. OFF):
EXEC sp_configure 'clr strict security';

SELECT [name], [is_trustworthy_on], [collation_name]
FROM sys.databases WHERE [database_id] = DB_ID(N'SQLCLRvsClrStrictSecurity');


GO
USE [SQLCLRvsClrStrictSecurity];
-- DROP PROCEDURE [dbo].[RegEx_IsMatch];
-- DROP ASSEMBLY [SQL2017_ClrStrictSecurity];


IF (ASSEMBLYPROPERTY(N'SQL2017_ClrStrictSecurity', N'MvID') IS NULL)
BEGIN
    PRINT 'Creating [SQL2017_ClrStrictSecurity] Assembly...''dbo.RegEx_IsMatch') IS NULL)
BEGIN
    PRINT N'Creating [dbo].[RegEx_IsMatch]...';
    EXEC(N'
CREATE FUNCTION [dbo].[RegEx_IsMatch]
(
  @Text NVARCHAR (MAX),
  @Expression NVARCHAR (MAX)
)
RETURNS BIT
WITH RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME [SQL2017_ClrStrictSecurity].[UDFs].[RegEx_IsMatch];
');
END;
GO


SELECT dbo.RegEx_IsMatch(N'3456', N'\d{4}'); -- 1
SELECT dbo.RegEx_IsMatch(N'34567', N'\d{4}'); -- 1
SELECT dbo.RegEx_IsMatch(N'345', N'\d{4}'); -- 0
SELECT dbo.RegEx_IsMatch(NULL, N'f'); -- NULL

/* RETURNS:

1
1
0
NULL

*/