/*
 --------- 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...';
	CREATE ASSEMBLY [SQL2017_ClrStrictSecurity]
		AUTHORIZATION [dbo]
		FROM 0x\
4D5A90000300000004000000FFFF0000B80000000000000040000000000000000000000000000000\
0000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD215468\
69732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A\
2400000000000000504500004C01030041F485590000000000000000E00002210B010B00000C0000\
0008000000000000FE29000000200000004000000000001000200000000200000400000000000000\
06000000000000000080000000020000EDFC00000300608500001000001000000000100000100000\
00000000100000000000000000000000B02900004B00000000400000380400000000000000000000\
0000000000000000006000000C000000782800001C00000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000200000080000000000000000000000\
082000004800000000000000000000002E74657874000000040A000000200000000C000000020000\
000000000000000000000000200000602E72737263000000380400000040000000060000000E0000\
000000000000000000000000400000402E72656C6F6300000C000000006000000002000000140000\
0000000000000000000000004000004200000000000000000000000000000000E029000000000000\
4800000002000500FC2000007C070000090000000000000000000000000000005020000080000000\
000000000000000000000000000000000000000000000000000000000000000091780EA01A63A0D0\
0E190A0E39C02686F7BBFCB3D5CAD5378CC74A2FE6979E80312AB5CB936C9418A4CAA677BF10B8F0\
9E1ADDFC31CB6B51F24256AFCF714EF7C90812ACBCCEE166107189F3C06954C400900E973785ABCC\
34C60FC4412479F9EA535BE5F67BFD37822F8EBC082A5627EDD97C73152BF14B9BC963215B5F35BB\
8E0F00280F00000A0F01280F00000A1716161F1E731000000A281100000A281200000A2A1E022813\
00000A2A42534A4201000100000000000C00000076342E302E33303331390000000005006C000000\
18020000237E000084020000F402000023537472696E677300000000780500000800000023555300\
8005000010000000234755494400000090050000EC01000023426C6F620000000000000002000001\
471500000900000000FA253300160000010000001500000002000000020000000200000013000000\
0C000000010000000300000000000A0001000000000006003D0036000A00650050000A0070005000\
0600B0009E000600C7009E000600E4009E00060003019E0006001C019E00060035019E0006005001\
9E0006006B019E00060084019E0006009D019E000600CD01BA013B00E101000006001002F0010600\
3002F0010A00830268020600A20236000E00CA02AB020E00D002AB02000000000100000000000100\
01000100100028000000050001000100D0200000000096007A000A000100F4200000000086188800\
13000300000001008E00000002009300210088001700290088001700310088001700390088001700\
41008800170049008800170051008800170059008800170061008800170069008800170071008800\
1C0081008800220089008800130091008800130019009802EE0099008800F200A100DD02F9001100\
E502030109008800130020007300C9002E002B0044012E00130028012E001B0028012E0023002E01\
2E000B0009012E00330070012E003B0028012E005300AC012E005B00B9012E006300C2012E006B00\
CB010480000001000000000000000100000027004E02000004000000000000000000000001002D00\
00000000040000000000000000000000010044000000000004000000000000000000000001003600\
0000000000000000003C4D6F64756C653E0053514C323031375F436C725374726963745365637572\
6974792E646C6C0055444673006D73636F726C69620053797374656D004F626A6563740053797374\
656D2E446174610053797374656D2E446174612E53716C54797065730053716C426F6F6C65616E00\
53716C537472696E670052656745785F49734D61746368002E63746F720054657874004578707265\
7373696F6E0053797374656D2E5265666C656374696F6E00417373656D626C795469746C65417474\
72696275746500417373656D626C794465736372697074696F6E4174747269627574650041737365\
6D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D7061\
6E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373\
656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D6172\
6B41747472696275746500417373656D626C7943756C747572654174747269627574650041737365\
6D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E\
4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541\
747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E43\
6F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174\
747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005351\
4C323031375F436C725374726963745365637572697479004D6963726F736F66742E53716C536572\
7665722E5365727665720053716C46756E6374696F6E417474726962757465006765745F56616C75\
650054696D655370616E0053797374656D2E546578742E526567756C617245787072657373696F6E\
730052656765780052656765784F7074696F6E730049734D61746368006F705F496D706C69636974\
000000000003200000000000D258441C60AF8441936C5A07D95675150008B77A5C561934E0890800\
021109110D110D03200001042001010E05200101113D042001010880A00024000004800000940000\
000602000000240000525341310004000001000100DFB9D86AF437605A8736C7A36E5EF9DE45A64F\
56C3673CC0A37DACEACD64F830A74C06147998309BF902665A51C7C81843D4C95466B4D59B9E7902\
45A0C05B498753CD075F2C6B1EB40E0E478E73DC3F12929C8B529B9805D10AE58A8945B99AB5BBE8\
85AAB2732892D5F063AE6B7B3F4E0EF71E9FC484E87FD062DA56DFE1D0240100020054020F497344\
657465726D696E697374696301540209497350726563697365010320000E06200301080808090004\
020E0E1155114D0500011109021E01001953514C323031375F436C72537472696374536563757269\
747900000501000000001501001053716C205175616E74756D204C65617000002B01002653514C20\
536572766572203230313720767320434C522073747269637420736563757269747900003B010036\
436F7079726967687420286329203230313720536F6C6F6D6F6E2052757A6B792E20416C6C207269\
676874732072657365727665642E00000C010007312E302E302E3000000801000200000000000801\
000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000\
0000000041F4855900000000020000001C01000094280000940A000052534453A19A1595E9DDED45\
BEA5CF59FEDC604003000000633A5C53514C5175616E74756D4C6561705C50726F6A656374735C42\
6C6F67546F706963735C3731375C53514C323031375F436C7253747269637453656375726974795C\
53514C323031375F436C7253747269637453656375726974795C6F626A5C52656C656173655C5351\
4C323031375F436C7253747269637453656375726974792E70646200000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000D829000000000000\
00000000EE290000002000000000000000000000000000000000000000000000E029000000000000\
00005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000100\
10000000180000800000000000000000000000000000010001000000300000800000000000000000\
0000000000000100000000004800000058400000DC0300000000000000000000DC03340000005600\
53005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100\
000001000000000000000100000000003F0000000000000004000000020000000000000000000000\
00000000440000000100560061007200460069006C00650049006E0066006F000000000024000400\
00005400720061006E0073006C006100740069006F006E00000000000000B0043C03000001005300\
7400720069006E006700460069006C00650049006E0066006F000000180300000100300030003000\
30003000340062003000000044001100010043006F006D00700061006E0079004E0061006D006500\
00000000530071006C0020005100750061006E00740075006D0020004C0065006100700000000000\
5C001A000100460069006C0065004400650073006300720069007000740069006F006E0000000000\
530051004C0032003000310037005F0043006C007200530074007200690063007400530065006300\
750072006900740079000000300008000100460069006C006500560065007200730069006F006E00\
0000000031002E0030002E0030002E00300000005C001E00010049006E007400650072006E006100\
6C004E0061006D0065000000530051004C0032003000310037005F0043006C007200530074007200\
690063007400530065006300750072006900740079002E0064006C006C0000009400370001004C00\
6500670061006C0043006F007000790072006900670068007400000043006F007000790072006900\
670068007400200028006300290020003200300031003700200053006F006C006F006D006F006E00\
2000520075007A006B0079002E00200041006C006C00200072006900670068007400730020007200\
65007300650072007600650064002E000000000064001E0001004F0072006900670069006E006100\
6C00460069006C0065006E0061006D0065000000530051004C0032003000310037005F0043006C00\
7200530074007200690063007400530065006300750072006900740079002E0064006C006C000000\
700027000100500072006F0064007500630074004E0061006D00650000000000530051004C002000\
53006500720076006500720020003200300031003700200076007300200043004C00520020007300\
74007200690063007400200073006500630075007200690074007900000000003400080001005000\
72006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E00\
3000000038000800010041007300730065006D0062006C0079002000560065007200730069006F00\
6E00000031002E0030002E0030002E00300000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
002000000C000000003A000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000
		WITH PERMISSION_SET = SAFE;
END;


IF (OBJECT_ID(N'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

*/