"clr strict security" - Solution 2

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

By: Solomon Rutzky
On: 2017-08-10
Location: https://pastebin.com/2nw9Kyfe
Related blog post: https://SqlQuantumLeap.com/2017/08/16/sqlclr-vs-sql-server-2017-part-3-clr-strict-security-solution-2/

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 override the default SSDT build process to add a step to sign
             the DLL with a certificate before the DLL is pulled into a deployment script.

             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 (make sure RECOVERY=SIMPLE and TRUSTWORTHY=OFF)...';
    ALTER DATABASE [SQLCLRvsClrStrictSecurity]
        SET RECOVERY SIMPLE,
        TRUSTWORTHY OFF;
END;


PRINT 'Making sure that SQLCLR is enabled...';
IF (EXISTS(
            SELECT *
            FROM   sys.configurations sc
            WHERE  sc.[configuration_id] = 1562 -- "clr enabled"
            AND    sc.[value_in_use] = 0
    ))
BEGIN
    PRINT 'Enabling SQLCLR...';
    EXEC sp_configure N'clr enabled', 1; RECONFIGURE;
END;

PRINT 'Making sure that SQLCLR "strict security" is enabled...';
IF (EXISTS(
            SELECT *
            FROM   sys.configurations sc
            WHERE  sc.[configuration_id] = 518 -- "show advanced options"
            AND    sc.[value_in_use] = 0
    ))
BEGIN
    PRINT ' Showing advanced options...';
    EXEC sp_configure N'show advanced options', 1; RECONFIGURE;
END;


IF (EXISTS(
            SELECT *
            FROM   sys.configurations sc
            WHERE  sc.[configuration_id] = 1587 -- "clr strict security"
            AND    sc.[value_in_use] = 0
    ))
BEGIN
    PRINT ' Enabling CLR strict security...';
    EXEC sp_configure N'clr strict security', 1; RECONFIGURE;
END;





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

    IF (CERT_ID(N'SQL2017-ClrStrictSecurity2-Cert') IS NULL)
    BEGIN
        PRINT ' Creating Certificate...';
        CREATE CERTIFICATE [SQL2017-ClrStrictSecurity2-Cert]
FROM BINARY = 0x\
3082037930820265A00302010202109BCC3B23A3549F9645D0A425322CF7FD300906052B0E03021D\
05003040310B3009060355040613025553311B3019060355040A131253716C5175616E74756D4C65\
61702E636F6D311430120603550403130B53514C434C5220436572743020170D3137303831383033\
313030345A180F32303939313233313034303030305A3040310B3009060355040613025553311B30\
19060355040A131253716C5175616E74756D4C6561702E636F6D311430120603550403130B53514C\
434C52204365727430820122300D06092A864886F70D01010105000382010F003082010A02820101\
00CCEA3F8D614CE7AB2B52E39301A9D9E2A7828473EC10D8748CF0BB32DDF6ED1423E56C0FC36DAA\
995D54BD7194BF3BC71B1931111706DF162ED5188017B0B570DDF9AFFD3E531E266BFADD02220253\
53277BAC965E89DB4645438A973256897E0D2644C24CB3C61AC0011FC7CDDFE06C1234C1C0A60158\
6952FF2033C58F8A519AD1D71B35A6975E1F846A83D1F90D1602A372CC8EB697240E9E38852BBB96\
CB78FE296B57DE401AAFD958EDDF59894E8C0A42B95A77BE43C1B893CE312824B68C931F9893639A\
41BF99E68AD6F2AA2AEC58AA5CC067EF8FF0AD7BE9A1AA44B48F8338BBD324C2D162DA30356FD461\
BF2FD388EDDFDD5990682379EF18A33C4D0203010001A375307330710603551D01046A30688010B7\
C292D45B0F3FDDBF55241F2FD6F2CFA1423040310B3009060355040613025553311B301906035504\
0A131253716C5175616E74756D4C6561702E636F6D311430120603550403130B53514C434C522043\
65727482109BCC3B23A3549F9645D0A425322CF7FD300906052B0E03021D050003820101006AF2D8\
F382D55034DE7715B0F5536669914C6B256344B7343B0B099B32B4759E77B367CA19A3B3FBC7DBCF\
913FB022DD5D4F83EB6792D58B63AF015767F0BC059D24D3E019880A95DEA9E72BD462302806CF9B\
FB1C9A3E3BAC4D612889ACF4839AC0FE7F60625FA33DABA6F0FCD6EBF88401712EF3DA505092CBB5\
ACCDC510258F3003868DC15C0E5374B01E6BF555972E3F99C789F1E14B171CE508D895CDD71AB6FD\
8D9F61962A3E1D89E0E748E3C5505AF21E0DF6D95FEA86E87D5DED7D00A669FC8CAB472A93F993BB\
B21636AEDE56F747E11FCF69F96D4C87732C5BE3780E01C772F3BD0938DD5A440BDCC84680839572\
8AF10C95C6F8E6714DDAFA19B2
    END;

    IF (SUSER_ID(N'SQL2017-ClrStrictSecurity2-Login') IS NULL)
    BEGIN
        PRINT ' Creating permissions Login...';
        CREATE LOGIN [SQL2017-ClrStrictSecurity2-Login]
            FROM CERTIFICATE [SQL2017-ClrStrictSecurity2-Cert];
    END;
END;

-- REVOKE UNSAFE ASSEMBLY FROM [SQL2017-ClrStrictSecurity2-Login];
GRANT UNSAFE ASSEMBLY TO [SQL2017-ClrStrictSecurity2-Login]; -- REQUIRED!!!!


--DROP LOGIN [SQL2017-ClrStrictSecurity2-Login];
--DROP CERTIFICATE [SQL2017-ClrStrictSecurity2-Cert];


-- Verify that "clr strict security" is 1 (i.e. ON), and that TRUSTWORTHY is 0 (i.e. OFF):
SELECT *
FROM   sys.configurations sc
WHERE  sc.[configuration_id] = 1587 -- "clr strict security"

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


GO
USE [SQLCLRvsClrStrictSecurity];
-- DROP FUNCTION [dbo].[String_PadLeft];
-- DROP ASSEMBLY [SQL2017_ClrStrictSecurity2];


IF (ASSEMBLYPROPERTY(N'SQL2017_ClrStrictSecurity2', N'MvID') IS NULL)
BEGIN
    PRINT 'Creating [SQL2017_ClrStrictSecurity2] Assembly...';
    CREATE ASSEMBLY [SQL2017_ClrStrictSecurity2]
        AUTHORIZATION [dbo]
        FROM 0x\
4D5A90000300000004000000FFFF0000B80000000000000040000000000000000000000000000000\
0000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD215468\
69732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A\
2400000000000000504500004C01030082F498590000000000000000E00002210B010B00000A0000\
0008000000000000CE28000000200000004000000000001000200000000200000400000000000000\
06000000000000000080000000020000247B00000300608500001000001000000000100000100000\
00000000100000000000000000000000802800004B00000000400000980400000000000000000000\
00140000D8050000006000000C000000482700001C00000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000200000080000000000000000000000\
082000004800000000000000000000002E74657874000000D408000000200000000A000000020000\
000000000000000000000000200000602E72737263000000980400000040000000060000000C0000\
000000000000000000000000400000402E72656C6F6300000C000000006000000002000000120000\
0000000000000000000000004000004200000000000000000000000000000000B028000000000000\
48000000020005007C200000CC060000010000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000860F00280F00000A\
0F02281000000A03166A6F1100000A6F1200000A731300000A2A1E02281400000A2A000042534A42\
01000100000000000C00000076342E302E33303331390000000005006C00000008020000237E0000\
74020000E402000023537472696E6773000000005805000008000000235553006005000010000000\
2347554944000000700500005C01000023426C6F6200000000000000020000014715000009000000\
00FA2533001600000100000014000000020000000200000003000000140000000C00000001000000\
0200000000000A0001000000000006004E0047000A00760061000A00800061000A00890061000600\
D700C5000600EE00C50006000B01C50006002A01C50006004301C50006005C01C50006007701C500\
06009201C5000600AB01C5000600C401C5000600F401E1013F000802000006003702170206005702\
17020A00AB0290020600D30247000000000001000000000001000100010010002900000005000100\
0100502000000000960092000A0001007220000000008618A1001500040000000100A70000000200\
B30000000300BB002900A10019003100A10019003900A10019004100A10019004900A10019005100\
A10019005900A10019006100A10019006900A10019007100A10019007900A1001E008900A1002400\
9100A10015009900A10015001100C00241002100C00245001900CA024900A100DA024E001100A100\
19000900A10015002000730029002E002B00B5002E00130074002E001B0099002E0023009F002E00\
0B0054002E003300E1002E003B0099002E0053001D012E005B002A012E00630033012E006B003C01\
0480000002000600070000000000000000007502000004000000000000000000000001003E000000\
000004000000000000000000000001005500000000000000003C4D6F64756C653E0053514C323031\
375F436C725374726963745365637572697479322E646C6C0055736572446566696E656446756E63\
74696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E44617461\
0053797374656D2E446174612E53716C54797065730053716C537472696E670053716C4368617273\
0053716C496E74333200537472696E675F5061644C656674002E63746F7200496E70757453747269\
6E67005061644368617200506164416D6F756E740053797374656D2E5265666C656374696F6E0041\
7373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F\
6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574\
6500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475\
637441747472696275746500417373656D626C79436F707972696768744174747269627574650041\
7373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572\
6541747472696275746500417373656D626C7956657273696F6E4174747269627574650041737365\
6D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F7374\
6963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053\
797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C617469\
6F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C\
6974794174747269627574650053514C323031375F436C7253747269637453656375726974793200\
4D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174\
74726962757465006765745F56616C7565006765745F4974656D00537472696E67005061644C6566\
740000000003200000000000F41C3A78FF4C4A49B5F1F9B95DE8B0F70008B77A5C561934E0890A00\
0311091109120D111103200001042001010E0520010111410420010108170100010054020F497344\
657465726D696E6973746963010320000E03200008042001030A0520020E08031F01001A53514C32\
3031375F436C7253747269637453656375726974793200002401001F68747470733A2F2F53716C51\
75616E74756D4C6561702E636F6D2F3230313700000501000000001501001053716C205175616E74\
756D204C65617000002B01002653514C20536572766572203230313720767320434C522073747269\
637420736563757269747900003B010036436F7079726967687420286329203230313720536F6C6F\
6D6F6E2052757A6B792E20416C6C207269676874732072657365727665642E00000C010007332E38\
2E392E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E45\
7863657074696F6E5468726F777301000000000082F4985900000000020000001C01000064270000\
64090000525344535AC21935AE54774C9B9CF9BD052243AB08000000633A5C53514C5175616E7475\
6D4C6561705C50726F6A656374735C426C6F67546F706963735C313830335C53514C323031375F43\
6C725374726963745365637572697479325C53514C323031375F436C725374726963745365637572\
697479325C6F626A5C52656C656173655C53514C323031375F436C72537472696374536563757269\
7479322E706462000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000A82800000000000000000000BE28000000200000000000000000000000000000\
0000000000000000B02800000000000000005F436F72446C6C4D61696E006D73636F7265652E646C\
6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000001001000000018000080000000000000000000000000000001000100000030000080\
00000000000000000000000000000100000000004800000058400000400400000000000000000000\
400434000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000\
BD04EFFE00000100080003000000090008000300000009003F000000000000000400000002000000\
000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00\
000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004\
A0030000010053007400720069006E006700460069006C00650049006E0066006F0000007C030000\
010030003000300030003000340062003000000058002000010043006F006D006D0065006E007400\
73000000680074007400700073003A002F002F00530071006C005100750061006E00740075006D00\
4C006500610070002E0063006F006D002F003200300031003700000044001100010043006F006D00\
700061006E0079004E0061006D00650000000000530071006C0020005100750061006E0074007500\
6D0020004C006500610070000000000060001B000100460069006C00650044006500730063007200\
69007000740069006F006E0000000000530051004C0032003000310037005F0043006C0072005300\
74007200690063007400530065006300750072006900740079003200000000003000080001004600\
69006C006500560065007200730069006F006E000000000033002E0038002E0039002E0030000000\
60001F00010049006E007400650072006E0061006C004E0061006D0065000000530051004C003200\
3000310037005F0043006C0072005300740072006900630074005300650063007500720069007400\
790032002E0064006C006C00000000009400370001004C006500670061006C0043006F0070007900\
72006900670068007400000043006F00700079007200690067006800740020002800630029002000\
3200300031003700200053006F006C006F006D006F006E002000520075007A006B0079002E002000\
41006C006C0020007200690067006800740073002000720065007300650072007600650064002E00\
0000000068001F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00\
65000000530051004C0032003000310037005F0043006C0072005300740072006900630074005300\
650063007500720069007400790032002E0064006C006C0000000000700027000100500072006F00\
64007500630074004E0061006D00650000000000530051004C002000530065007200760065007200\
20003200300031003700200076007300200043004C00520020007300740072006900630074002000\
7300650063007500720069007400790000000000340008000100500072006F006400750063007400\
560065007200730069006F006E00000033002E0038002E0039002E00300000003800080001004100\
7300730065006D0062006C0079002000560065007200730069006F006E00000032002E0036002E00\
37002E00300000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000002000000C000000D03800000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
D805000000020200308205C506092A864886F70D010702A08205B6308205B2020101310B30090605\
2B0E03021A0500304C060A2B060104018237020104A03E303C3017060A2B06010401823702010F30\
09030100A004A20280003021300906052B0E03021A0500041443D232A77578792F67D9469EBEE29E\
B6D88FF59AA082037D3082037930820265A00302010202109BCC3B23A3549F9645D0A425322CF7FD\
300906052B0E03021D05003040310B3009060355040613025553311B3019060355040A131253716C\
5175616E74756D4C6561702E636F6D311430120603550403130B53514C434C522043657274302017\
0D3137303831383033313030345A180F32303939313233313034303030305A3040310B3009060355\
040613025553311B3019060355040A131253716C5175616E74756D4C6561702E636F6D3114301206\
03550403130B53514C434C52204365727430820122300D06092A864886F70D01010105000382010F\
003082010A0282010100CCEA3F8D614CE7AB2B52E39301A9D9E2A7828473EC10D8748CF0BB32DDF6\
ED1423E56C0FC36DAA995D54BD7194BF3BC71B1931111706DF162ED5188017B0B570DDF9AFFD3E53\
1E266BFADD0222025353277BAC965E89DB4645438A973256897E0D2644C24CB3C61AC0011FC7CDDF\
E06C1234C1C0A601586952FF2033C58F8A519AD1D71B35A6975E1F846A83D1F90D1602A372CC8EB6\
97240E9E38852BBB96CB78FE296B57DE401AAFD958EDDF59894E8C0A42B95A77BE43C1B893CE3128\
24B68C931F9893639A41BF99E68AD6F2AA2AEC58AA5CC067EF8FF0AD7BE9A1AA44B48F8338BBD324\
C2D162DA30356FD461BF2FD388EDDFDD5990682379EF18A33C4D0203010001A37530733071060355\
1D01046A30688010B7C292D45B0F3FDDBF55241F2FD6F2CFA1423040310B30090603550406130255\
53311B3019060355040A131253716C5175616E74756D4C6561702E636F6D31143012060355040313\
0B53514C434C52204365727482109BCC3B23A3549F9645D0A425322CF7FD300906052B0E03021D05\
0003820101006AF2D8F382D55034DE7715B0F5536669914C6B256344B7343B0B099B32B4759E77B3\
67CA19A3B3FBC7DBCF913FB022DD5D4F83EB6792D58B63AF015767F0BC059D24D3E019880A95DEA9\
E72BD462302806CF9BFB1C9A3E3BAC4D612889ACF4839AC0FE7F60625FA33DABA6F0FCD6EBF88401\
712EF3DA505092CBB5ACCDC510258F3003868DC15C0E5374B01E6BF555972E3F99C789F1E14B171C\
E508D895CDD71AB6FD8D9F61962A3E1D89E0E748E3C5505AF21E0DF6D95FEA86E87D5DED7D00A669\
FC8CAB472A93F993BBB21636AEDE56F747E11FCF69F96D4C87732C5BE3780E01C772F3BD0938DD5A\
440BDCC846808395728AF10C95C6F8E6714DDAFA19B2318201CF308201CB02010130543040310B30\
09060355040613025553311B3019060355040A131253716C5175616E74756D4C6561702E636F6D31\
1430120603550403130B53514C434C52204365727402109BCC3B23A3549F9645D0A425322CF7FD30\
0906052B0E03021A0500A0523010060A2B06010401823702010C31023000301906092A864886F70D\
010903310C060A2B060104018237020104302306092A864886F70D0109043116041470E1D5141ADB\
71E29F00A5D80E2A58B2BCBA01D4300D06092A864886F70D010101050004820100AF02DE1136A6CE\
693A4C46F9DBB494FD7FE687F0C014A75B5A8DEB0D6B7A0CB967C42A09B743E0124891B3598FADAB\
A6DF6812378F15A0B9C7B8AB076A90D320BAFC220F5438C4204E2A5A770DDD93AB2E4AD324198659\
9C991196FBC115ACFBB5B492B21F2FB6DCB89D4B61E2E19C716693C6A2F9DE12572E259CBFD48238\
003FF893D95D3EC5C025CD9323147A0D4BEAB0F2C430C9F1C0B035FA983FF62E7F840723B9B7DABC\
D580FC3347C1173DFDF56A94EC5498FFB11D0F6816A3E0432E986327CC6A2CC9858CDD79C39E6C8F\
929CCD8DE79B7C0C6EC77556E7EFAF79E2A2B79CE11AE5F4A9EC459E1C290E7935CAB66AD1B957FE\
8D4829365E24549A9F00000000000000
        WITH PERMISSION_SET = SAFE;
END;


IF (OBJECT_ID(N'dbo.String_PadLeft') IS NULL)
BEGIN
    PRINT N'Creating [dbo].[String_PadLeft]...';
    EXEC(N'
CREATE FUNCTION [dbo].[String_PadLeft]
(
  @InputString NVARCHAR(4000),
  @PadChar NVARCHAR(1),
  @PadAmount INT
)
RETURNS NVARCHAR(4000)
WITH RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME [SQL2017_ClrStrictSecurity2].[UserDefinedFunctions].[String_PadLeft];
');
END;
GO


PRINT 'Running three tests...';
SELECT dbo.String_PadLeft(N'3456', N'~', 7); -- ~~~3456
SELECT dbo.String_PadLeft(N'34567', N'+', 3); -- 34567
SELECT dbo.String_PadLeft(NULL, N'f', 5); -- NULL


PRINT 'Removing UNSAFE ASSEMBLY permission...';
EXEC (N'
USE [master];

REVOKE UNSAFE ASSEMBLY FROM [SQL2017-ClrStrictSecurity2-Login];
');


PRINT 'Re-running first test (it should error in SQL Server 2017)...';
SELECT dbo.String_PadLeft(N'3456', N'~', 7); -- ~~~3456

/* RETURNS:

~~~3456
34567
NULL

*/
GO

SELECT ASSEMBLYPROPERTY(N'sql2017_clrstrictsecurity2', 'CLRName') AS [CLRName];
-- sql2017_clrstrictsecurity2, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil