Unable to create the tSQLtCLR assembly in SQL Server 2017

Question

I recently installed SQL Server 2017 Express and localdb (general availablity). While attempting to install the tSQLt framework I've discovered a new security feature implemented in SQL Server 2017: the "clr strict security" option. This new security feature seems to prevent the creation of the tSQLtCLR assembly.

The SQL error message states:

CREATE or ALTER ASSEMBLY for assembly 'tSQLtCLR' with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly.

I've read Microsoft's technical documentation related to the sp_add_trusted_assembly procedure, but it seems to assume that you were able to successfully create the assembly. How would one code the tSQLtCLR assembly to be listed as "trusted" if you can't get it created in the first place?

Answer 1

SQL Server 2017 introduces a new server-level configuration option named "CLR strict security", and it is enabled by default. This option requires that ALL Assemblies, even SAFE ones, be signed with a certificate or strong name key, and that the Certificate or Asymmetric Key used to do that signing is loaded into [master], and has a Login created from it, and that Login has been granted the UNSAFE ASSEMBLY permission.

Due to SAFE Assemblies now needing to have the signature-based Login in place before being loaded via CREATE ASSEMBLY, it is no longer possible to have an empty, signed Assembly that gets loaded into [master] via CREATE ASSEMBLY ... FROM 0x... WITH PERMISSION_SET = SAFE;.

Now, there are only two ways to create objects usable to set up SQLCLR security from a VARBINARY literal or variable (i.e. not from an external file):

1. CREATE ASSEMBLY ... FROM 0x...;
2. CREATE CERTIFICATE ... FROM BINARY = 0x...;

Option #1 is no longer an option, at least not by itself. Option 2 is fine, but was never preferred due Certificates not being fully integrated into the Visual Studio / MSBuild build process.

Fortunately, there are two ways to fix this as discussed in the following two blog posts of mine:

1. SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1 — more steps than Part 3, Solution 2 (below), but a good fit for existing projects as it requires almost no changes to the existing solution or even deployment process (and in fact, this is effectively the route that I went for my SQL# project as all it did was add 3 simple steps to the beginning of the installation script)
2. SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2

HOWEVER,

that just answers the question of "why" you are in the situation that you are currently in. To fix that situation, assuming that you likely aren't going to update the tSQLt build process to include a Certificate, then you can do a simple one-time fix of:

ALTER DATABASE [master] SET TRUSTWORTHY ON;
EXEC tSQLt.InstallExternalAccessKey;
EXEC master.sys.sp_executesql N'GRANT UNSAFE ASSEMBLY TO [tSQLtExternalAccessKey];';
ALTER DATABASE [master] SET TRUSTWORTHY OFF;

The GRANT UNSAFE ASSEMBLY is there due to the tSQLt.InstallExternalAccessKey Stored Procedure only granting EXTERNAL ACCESS ASSEMBLY to the Login, which used to be fine, but now is not enough.

Of course, you won't be able to load the tSQLt Assemblies until those 4 steps are done, so if the process is to load everything first and that is failing, then you will need to do:

EXEC sp_configure 'clr strict security', 0; RECONFIGURE;
-- Install tSQLt ...
EXEC tSQLt.InstallExternalAccessKey;
EXEC master.sys.sp_executesql N'GRANT UNSAFE ASSEMBLY TO [tSQLtExternalAccessKey];';
EXEC sp_configure 'clr strict security', 1; RECONFIGURE;

I created an issue in the tSQLt GitHub repository with the steps require to incorporate the ideal fix into the source files: https://github.com/tSQLt-org/tSQLt/issues/25

PLEASE NOTE

that none of these possible solutions includes using the new "Trusted Assemblies" feature. That feature should never, ever be used by anyone for any reason (outside of sheer curiosity and testing). The reasons for avoiding it are detailed in several blog posts (currently 3 and more on the way) starting with:

SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment

Answer 2

The tSQLt assembly is signed already. For now, you can create the assembly in master, create a certificate from it, drop the assembly again and then take the necessary steps with that certificate.

I'm working on getting the required step to install tSQLt on 2017 automated.

Answer 3

This worked for me:

declare
     @hash binary(64)
    ,@description nvarchar(4000)

select
      @hash = HASHBYTES('SHA2_512', af.content)
    , @description = a.clr_name
FROM sys.assemblies a
JOIN sys.assembly_files af
    ON a.assembly_id = af.assembly_id
WHERE a.is_user_defined = 1
    and a.name = 'tSQLtCLR'

EXEC sys.sp_add_trusted_assembly
     @hash
    ,@description