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?
Best Answer
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 theUNSAFE ASSEMBLY
permission.Due to
SAFE
Assemblies now needing to have the signature-based Login in place before being loaded viaCREATE ASSEMBLY
, it is no longer possible to have an empty, signed Assembly that gets loaded into[master]
viaCREATE 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):CREATE ASSEMBLY ... FROM 0x...;
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:
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:
The
GRANT UNSAFE ASSEMBLY
is there due to thetSQLt.InstallExternalAccessKey
Stored Procedure only grantingEXTERNAL 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:
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