CLR

Intro

CLR assemblies are created to execute .NET code inside SQL Server.
All assemblies that are ‘Safe’ or have ‘external access’ as permission set will be treated as unsafe assemblies
in SQL 2017. This, because safe assemblies in theory don’t access external resources, can still happen.
This means that starting from SQL 2017 all assemblies need to be signed with either a certificate or an
assymetric key.

While implementing this is useful, you also need to be able to trust the assemblies and other external resources running on your
instance.

Example

 

The next snippet succeeds on SQL Server 2016, but fails on SQL Server 2017:

 

CREATE ASSEMBLY UnsignedAssembly FROM

 'C:\Users\vertora\Desktop\ \DLLFiles\SafeAssembly.dll' --> UnSigned "safe" Assembly

WITH PERMISSION_SET= SAFE;

 

Error message:

Msg 10343, Level 14, State 1, Line 45

CREATE or ALTER ASSEMBLY for assembly ‘SafeAssembly’ 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.

 

Remarks

You can disable this setting, but that is not advised, since assemblies are never safe, and should only be executed if they are trusted.

Be advised, when upgrading your SQL Server too SQL Server 2017, that your assemblies will stop working if they are not signed.

 

 

Leave a Comment

Your email address will not be published. Required fields are marked *