Start from SQL2017, CLR strict security is enabled by default in SQL, and treats SAFE and EXTERNAL_ACCESS assemblies as if they were marked UNSAFE. One can disable CLR stric security option but that’s not recommended. Microsoft recommends assembily be signed by a certificate or asymmetric key with corresponding login that has been granted UNSAFE ASSEMBLY permission in the master database. DBA can aslo add assembliy to a trusted list by using sys.sp_add_trusted_assembly.
To create a CLR assembly under CLR strict security option, following conditions must meet:
-- the user must have CREATE ASSEMBLY permission.
-- One of the following must also be true:
-- Assembly is signed with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Signing assembly is recommended.
-- Database has TRUSTWORTHY set to ON, and database owner has UNSAFE ASSEMBLY permission on the server. This option is not recommended.
To use sys.sp_add_trusted_assembly, we need the SHA2_512 hash value of the assembly. We can install the assembly on a version of SQL prior to 2017 and then script it out with its binary string. Then we can use HASHBYTES function to generate the SHA2_512 hash value.
DECLARE @CLRDescription NVARCHAR(4000) = N'your assembly name, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil';
DECLARE @CLRBinary VARBINARY(MAX) = 0x000000000000; --this is the script out assembly binary string
DECLARE @hash VARBINARY(64);
SET @hash = HASHBYTES('SHA2_512', @CLRBinary);
SELECT @hash;
EXECUTE sys.sp_add_trusted_assembly @hash, @CLRDescription
--SELECT * FROM sys.trusted_assemblies;