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

Created By: Solomon Rutzky / Sql Quantum Leap ( https://SqlQuantumLeap.com/ )
Created On: 2017-08-18
Updated On: 2020-01-16 ~ Added note about Login for database owner needing the
                         UNSAFE ASSEMBLY permission for CREATE ASSEMBLY to work.

Location:          https://pastebin.com/mwi5BidL
Related blog post: https://SqlQuantumLeap.com/2017/08/28/sqlclr-vs-sql-server-2017-part-4-trusted-assemblies-the-disappointment/

Description: This script shows a technique for PROPERLY handling SQLCLR Assemblies,
             marked as SAFE and not signed, that reside in databases that are upgraded to
             SQL Server 2017 or restored into SQL Server 2017 from a previous version,
             while both keeping "clr strict security" enabled and "TRUSTWORTHY" disabled.
             Prior to SQL Server 2017 it was not necessary to have SAFE Assemblies signed
             and have a corresponding signature-based Login with the UNSAFE ASSEMBLY
             permission. As of SQL Server 2017 it is necessary, but the new "Trusted
             Assemblies" feature is NOT necessary and should not be used. Ever.

             The trick is to simply use module signing, which isn't really a trick at all.

             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;



-- Verify that "clr strict security" is 1 (i.e. ON), and that TRUSTWORTHY is 0 (i.e. OFF):
PRINT CHAR(13) + CHAR(10) + 'Verifying settings...';
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];


IF (OBJECT_ID(N'dbo.Date_Format') IS NULL)
BEGIN
	PRINT CHAR(13) + CHAR(10) + 'SQLCLR UDF [Date_Format] does not exist:';

	PRINT '	TEMPORARILY Altering Database to set TRUSTWORTHY=ON...';
	ALTER DATABASE [SQLCLRvsClrStrictSecurity]
		SET TRUSTWORTHY ON;

	-------------------------------------------------------------------------
	--  PLEASE NOTE:
	--  For the following CREATE ASSEMBLY statement to execute successfully,
	--  the Login that is the database owner (i.e. same SID used by the [dbo]
	--  User) needs to have the UNSAFE ASSEMBLY permission. If you just
	--  created this database then the database owner is your Login. And, if
	--  you are a sysadmin, then you have all permissions and this requirement
	--  has been satisfied. But, if you change the owner of the database to
	--  be a low-privileged login (a "best practice"), then you will need to
	--  execute the following statement in order for CREATE ASSEMBLY to work
	--  when TRUSTWORTHY is ON:
	--  
	--  EXEC (N'USE [master]; GRANT UNSAFE ASSEMBLY TO [{DB_Owner_Login}];');
	-------------------------------------------------------------------------

	IF (ASSEMBLYPROPERTY(N'SQL2017_TrustedAssemblies', N'MvID') IS NULL)
	BEGIN
		PRINT '	Creating [SQL2017_TrustedAssemblies] Assembly...';
		CREATE ASSEMBLY [SQL2017_TrustedAssemblies]
			AUTHORIZATION [dbo]
			FROM 0x\
4D5A90000300000004000000FFFF0000B80000000000000040000000000000000000000000000000\
0000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD215468\
69732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A\
2400000000000000504500004C010300A4AD9F590000000000000000E00002210B010B00000A0000\
0008000000000000DE28000000200000004000000000001000200000000200000400000000000000\
06000000000000000080000000020000000000000300608500001000001000000000100000100000\
000000001000000000000000000000008C2800004F00000000400000080500000000000000000000\
0000000000000000006000000C000000542700001C00000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000200000080000000000000000000000\
082000004800000000000000000000002E74657874000000E408000000200000000A000000020000\
000000000000000000000000200000602E72737263000000080500000040000000060000000C0000\
000000000000000000000000400000402E72656C6F6300000C000000006000000002000000120000\
0000000000000000000000004000004200000000000000000000000000000000C028000000000000\
480000000200050080200000D4060000010000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000000000000133002001C000000\
010000110F00280F00000A0A12000F01281000000A281100000A731200000A2A1E02281300000A2A\
42534A4201000100000000000C00000076342E302E33303331390000000005006C000000FC010000\
237E000068020000C802000023537472696E67730000000030050000080000002355530038050000\
100000002347554944000000480500008C01000023426C6F62000000000000000200000147150200\
0900000000FA2533001600000100000013000000020000000200000002000000130000000C000000\
01000000010000000200000000000A000100000000000600480041000A0070005B000A007A005B00\
0600C400B2000600DB00B2000600F800B20006001701B20006003001B20006004901B20006006401\
B20006007F01B20006009801B2000600B101B2000600E101CE013B00F50100000600240204020600\
440204020A0097027C020600AC024100000000000100000000000100010001001000280000000500\
01000100502000000000960086000A00010078200000000086189200130003000000010098000000\
0200A500210092001700290092001700310092001700390092001700410092001700490092001700\
510092001700590092001700610092001700690092001700710092001C0081009200220089009200\
13009100920013001900B5023F001100B50244009900BF0248001100920017000900920013002000\
730027002E002B00DD002E00130071002E001B00C1002E002300C7002E000B0052002E0033000901\
2E003B00C1002E00530045012E005B0058012E00630061012E006B006A014D0004800000E1070800\
14000100000000000000620200000400000000000000000000000100380000000000040000000000\
00000000000001004F00000000000000003C4D6F64756C653E0053514C323031375F547275737465\
64417373656D626C6965732E646C6C0053514C32303137767353514C434C52006D73636F726C6962\
0053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53\
716C54797065730053716C537472696E670053716C4461746554696D6500446174655F466F726D61\
74002E63746F720044617465546F466F726D617400466F726D6174537472696E670053797374656D\
2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D\
626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E6669677572\
6174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500\
417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967\
687441747472696275746500417373656D626C7954726164656D61726B4174747269627574650041\
7373656D626C7943756C7475726541747472696275746500417373656D626C7956657273696F6E41\
747472696275746500417373656D626C7946696C6556657273696F6E417474726962757465005379\
7374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562\
756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C65725365727669\
63657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74\
696D65436F6D7061746962696C6974794174747269627574650053514C323031375F547275737465\
64417373656D626C696573004D6963726F736F66742E53716C5365727665722E5365727665720053\
716C46756E6374696F6E417474726962757465004461746554696D65006765745F56616C75650054\
6F537472696E670000032000000000007A9C698E8B40FA4D93155AD83B1E444E0008B77A5C561934\
E0890800021109110D110903200001042001010E05200101113D0420010108170100010054020F49\
7344657465726D696E697374696301042000114D0320000E0420010E0E040701114D1E0100195351\
4C323031375F54727573746564417373656D626C69657300004F01004A68747470733A2F2F53716C\
5175616E74756D4C6561702E636F6D2F63617465676F72792F70726F6772616D6D696E672F73716C\
636C722D76732D73716C2D7365727665722D323031372F00000501000000001501001053716C2051\
75616E74756D204C65617000002B01002653514C20536572766572203230313720767320434C5220\
73747269637420736563757269747900003B010036436F7079726967687420286329203230313720\
536F6C6F6D6F6E2052757A6B792E20416C6C207269676874732072657365727665642E0000120100\
0D323031372E382E32302E30303200000801000200000000000801000800000000001E0100010054\
0216577261704E6F6E457863657074696F6E5468726F77730100000000000000A4AD9F5900000000\
020000001C01000070270000700900005253445313CF1DF386A59840A3A3BFB3702BDE7401000000\
633A5C53514C5175616E74756D4C6561705C50726F6A656374735C426C6F67546F706963735C3231\
33305C53514C323031375F54727573746564417373656D626C6965735C53514C323031375F547275\
73746564417373656D626C6965735C6F626A5C52656C656173655C53514C323031375F5472757374\
6564417373656D626C6965732E706462000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000000000000000000000000000B42800000000000000000000CE28000000200000\
0000000000000000000000000000000000000000C0280000000000000000000000005F436F72446C\
6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000001001000000018000080000000000000000000000000000001000100000030000080\
00000000000000000000000000000100000000004800000058400000AC0400000000000000000000\
AC0434000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000\
BD04EFFE000001000800E107020014000800E107020014003F000000000000000400000002000000\
000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00\
000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004\
0C040000010053007400720069006E006700460069006C00650049006E0066006F000000E8030000\
0100300030003000300030003400620030000000B0004B00010043006F006D006D0065006E007400\
73000000680074007400700073003A002F002F00530071006C005100750061006E00740075006D00\
4C006500610070002E0063006F006D002F00630061007400650067006F00720079002F0070007200\
6F006700720061006D006D0069006E0067002F00730071006C0063006C0072002D00760073002D00\
730071006C002D007300650072007600650072002D0032003000310037002F000000000044001100\
010043006F006D00700061006E0079004E0061006D00650000000000530071006C00200051007500\
61006E00740075006D0020004C00650061007000000000005C001A000100460069006C0065004400\
650073006300720069007000740069006F006E0000000000530051004C0032003000310037005F00\
540072007500730074006500640041007300730065006D0062006C0069006500730000003C000E00\
0100460069006C006500560065007200730069006F006E000000000032003000310037002E003800\
2E00320030002E0030003000320000005C001E00010049006E007400650072006E0061006C004E00\
61006D0065000000530051004C0032003000310037005F0054007200750073007400650064004100\
7300730065006D0062006C006900650073002E0064006C006C0000009400370001004C0065006700\
61006C0043006F007000790072006900670068007400000043006F00700079007200690067006800\
7400200028006300290020003200300031003700200053006F006C006F006D006F006E0020005200\
75007A006B0079002E00200041006C006C0020007200690067006800740073002000720065007300\
650072007600650064002E000000000064001E0001004F0072006900670069006E0061006C004600\
69006C0065006E0061006D0065000000530051004C0032003000310037005F005400720075007300\
74006500640041007300730065006D0062006C006900650073002E0064006C006C00000070002700\
0100500072006F0064007500630074004E0061006D00650000000000530051004C00200053006500\
720076006500720020003200300031003700200076007300200043004C0052002000730074007200\
6900630074002000730065006300750072006900740079000000000040000E000100500072006F00\
6400750063007400560065007200730069006F006E00000032003000310037002E0038002E003200\
30002E00300030003200000040000C00010041007300730065006D0062006C007900200056006500\
7200730069006F006E00000032003000310037002E0038002E00320030002E003100000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
0000000000000000002000000C000000E03800000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000000000000000000
			WITH PERMISSION_SET = SAFE;
	END;

	PRINT '	Altering Database to set TRUSTWORTHY back to OFF...';
	ALTER DATABASE [SQLCLRvsClrStrictSecurity]
		SET TRUSTWORTHY OFF;

	PRINT N'	Creating [dbo].[Date_Format]...';
	EXEC(N'
CREATE FUNCTION [dbo].[Date_Format]
(
	@DateToFormat DATETIME,
	@FormatString NVARCHAR(500)
)
RETURNS NVARCHAR(MAX)
WITH	EXECUTE AS CALLER,
		RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME [SQL2017_TrustedAssemblies].[SQL2017vsSQLCLR].[Date_Format];
');

	--PRINT '	Altering Database to set TRUSTWORTHY back to OFF...';
	--ALTER DATABASE [SQLCLRvsClrStrictSecurity]
	--	SET TRUSTWORTHY OFF;
END;
GO

-- TEST A
-- Should error due to:
--   1) "clr strict security" is enabled
--   2) TRUSTWORTHY is disabled
--   3) Assembly is not signed (hence no signature-based Login)
--   4) This is not a "Trusted" Assembly

PRINT CHAR(13) + CHAR(10) + 'Running test A...';
GO
SELECT dbo.Date_Format('2017-08-12 14:15:16.789', N'~~ yyyy ~~ d ~~ MMM ~~ mm ~~') AS [DateFormatOutput];
-- error:
/*
Msg 10314, Level 16, State 11, Server osboxes, Line XXXXX
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65546. The server may be running out of
   resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the
   assembly trust issues. For more information about this error: 
System.IO.FileLoadException: Could not load file or assembly 'sql2017_trustedassemblies, Version=0.0.0.0, Culture=neutral,
   PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
System.IO.FileLoadException: 
   at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly
      locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection,
      Boolean suppressSecurityChecks)
   at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence asse
*/

GO

-- SETUP FOR TEST B (B1 and B2)
-- 1) Create Certificate
-- 2) Sign Assembly
-- 3) Copy Certificate to [master] (Public Key only!)
-- 4) Create Login from Certificate


-- DROP SIGNATURE FROM Assembly::[SQL2017_TrustedAssemblies] BY CERTIFICATE [SQL2017-TrustedAssemblies];
-- DROP CERTIFICATE [SQL2017-TrustedAssemblies-Cert];


IF (SUSER_ID(N'SQL2017-TrustedAssemblies-Login') IS NULL)
BEGIN
	PRINT CHAR(13) + CHAR(10) + 'Permission Login does NOT exist:';

	IF (CERT_ID(N'SQL2017-TrustedAssemblies-Cert') IS NULL)
	BEGIN
		PRINT '	Creating Certificate in [SQLCLRvsClrStrictSecurity]...';
		CREATE CERTIFICATE [SQL2017-TrustedAssemblies-Cert]
			ENCRYPTION BY PASSWORD = 'YaddaYaddaYadda!'
			WITH SUBJECT = 'Sql Quantum Leap',
			EXPIRY_DATE = '2099-12-31';
	END;

	IF (NOT EXISTS(
					SELECT *
					FROM  sys.crypt_properties cp
					INNER JOIN sys.assemblies sa
							ON sa.[assembly_id] = cp.[major_id]
					WHERE sa.[name] = N'SQL2017_TrustedAssemblies'
				))
	BEGIN
		PRINT '	Signing the Assembly...';
		ADD SIGNATURE
			TO Assembly::[SQL2017_TrustedAssemblies]
			BY CERTIFICATE [SQL2017-TrustedAssemblies-Cert]
			WITH PASSWORD = 'YaddaYaddaYadda!';

	END;		

	IF (NOT EXISTS(
					SELECT *
					FROM   [master].[sys].[certificates] crt
					WHERE  crt.[name] = N'SQL2017-TrustedAssemblies-Cert'
				))
	BEGIN
		PRINT '	Copying the Certificate to [master]...';
		DECLARE @PublicKey VARBINARY(MAX),
				@SQL NVARCHAR(MAX);

		SET @PublicKey = CERTENCODED(CERT_ID(N'SQL2017-TrustedAssemblies-Cert'));

		SET @SQL = N'
CREATE CERTIFICATE [SQL2017-TrustedAssemblies-Cert]
	FROM BINARY = ' + CONVERT(NVARCHAR(MAX), @PublicKey, 1) + N';';
		--SELECT @PublicKey AS [@PublicKey]; -- DEBUG
		--PRINT @SQL; -- DEBUG

		EXEC [master].[sys].[sp_executesql] @SQL;
	END;


	PRINT '	Creating permissions Login...';
	EXEC [master].[sys].[sp_executesql] N'
CREATE LOGIN [SQL2017-TrustedAssemblies-Login]
	FROM CERTIFICATE [SQL2017-TrustedAssemblies-Cert];
';
END;
GO


-- TEST B1
-- Should error due to:
--   1) "clr strict security" is enabled
--   2) TRUSTWORTHY is disabled
--   3) Signature-based Login does not have "UNSAFE ASSEMBLY" permission
--   4) This is not a "Trusted" Assembly

PRINT CHAR(13) + CHAR(10) + 'Running test B1...';
GO
SELECT dbo.Date_Format('2017-08-12 14:15:16.789', N'~~ yyyy ~~ d ~~ MMM ~~ mm ~~') AS [DateFormatOutput];
-- error
/*
	{ same as shown above for Test A }
*/

GO

-- SETUP FOR TEST B2
-- 5) Grant Login UNSAFE ASSEMBLY permission

PRINT CHAR(13) + CHAR(10) + 'Granting UNSAFE ASSEMBLY permission...';
-- REVOKE UNSAFE ASSEMBLY FROM [SQL2017-TrustedAssemblies-Login];
EXEC [master].[sys].[sp_executesql] N'
GRANT UNSAFE ASSEMBLY TO [SQL2017-TrustedAssemblies-Login]; -- REQUIRED!!!!
';


PRINT CHAR(13) + CHAR(10) + 'Running test B2...';
GO
SELECT dbo.Date_Format('2017-08-12 14:15:16.789', N'~~ yyyy ~~ d ~~ MMM ~~ mm ~~') AS [DateFormatOutput];

/* RETURNS:

~~ 2017 ~~ 12 ~~ Aug ~~ 15 ~~

*/


GO

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

-----------------------------------------------------------
-- Clean up:

PRINT CHAR(13) + CHAR(10) + 'Cleaning up objects (to be re-runnable)...';

USE [SQLCLRvsClrStrictSecurity];
DROP FUNCTION [dbo].[Date_Format];
DROP ASSEMBLY [SQL2017_TrustedAssemblies];
DROP CERTIFICATE [SQL2017-TrustedAssemblies-Cert];

USE [master];
DROP LOGIN [SQL2017-TrustedAssemblies-Login];
DROP CERTIFICATE [SQL2017-TrustedAssemblies-Cert];

-- DROP DATABASE [SQLCLRvsClrStrictSecurity];