CLR Strict Security on SQL Server 2017

Question

MSDN on this article says:

CLR uses Code Access Security (CAS) in the .NET Framework, which is no longer supported as a security boundary. A CLR assembly created with PERMISSION_SET = SAFE may be able to access external system resources, call unmanaged code, and acquire sysadmin privileges. Beginning with SQL Server 2017, an sp_configure option called clr strict security is introduced to enhance the security of CLR assemblies. clr strict security is enabled by default, and treats SAFE and EXTERNAL_ACCESS assemblies as if they were marked UNSAFE. The clr strict security option can be disabled for backward compatibility, but this is not recommended. Microsoft recommends that all assemblies be signed by a certificate or asymmetric key with a corresponding login that has been granted UNSAFE ASSEMBLY permission in the master database.

How can a CLR assembly created with PERMISSION_SET = SAFE may be able to access external system resources, call unmanaged code, and acquire sysadmin privileges?

Why CAS is no longer supported as a security boundary?

As I understand CLR assemblies can no longer be safe, which is very unfortunate.

Answer 1

How can a CLR assembly created with PERMISSION_SET = SAFE may be able to access external system resources, call unmanaged code, and acquire sysadmin privileges?

This is due to security changes made in the .NET Framework, starting in version 4.5 (I believe).

MSDN documentation for Code Access Security Basics states:

The .NET Framework provides a mechanism for the enforcement of varying levels of trust on different code running in the same application called Code Access Security (CAS). Code Access Security in .NET Framework should not be used as a mechanism for enforcing security boundaries based on code origination or other identity aspects. We are updating our guidance to reflect that Code Access Security and Security-Transparent Code will not be supported as a security boundary with partially trusted code, especially code of unknown origin. We advise against loading and executing code of unknown origins without putting alternative security measures in place.

And then points to the page for Security Changes in the .NET Framework which states:

The most important change to security in the .NET Framework 4.5 is in strong naming.

Which then points to the documentation for Enhanced Strong Naming which states:

Strong name keys consist of a signature key and an identity key. The assembly is signed with the signature key and is identified by the identity key. Prior to the .NET Framework 4.5, these two keys were identical. Starting with the .NET Framework 4.5, the identity key remains the same as in earlier .NET Framework versions, but the signature key is enhanced with a stronger hash algorithm. In addition, the signature key is signed with the identity key to create a counter-signature.

ALSO, the documentation for Secure Coding Guidelines states:

Code Access Security and Security-Transparent Code will not be supported as a security boundary with partially trusted code. We advise against loading and executing code of unknown origins without putting alternative security measures in place...

So, the security model for .NET changed years ago, but SQL Server (until SQL Server 2017) has been allowed to continue using the old security model. It seems that, starting with SQL Server 2017, the decision was made to no longer support the old security model.

I suspect that allowing the old security model was:

• preventing SQL Server (at least the CLR-related functionality / components) from being based on the newer .NET Framework versions, and

• responsible for the abrupt removal of SQLCLR as a supported feature from Azure SQL Database (support had been added in late 2014 with the launch of v12, but then removed entirely as of April 15th, 2016).

---

So, yes, this kinda sucks. What it means (at least for the moment) is that one needs to first create a Certificate or Asymmetric Key (that has been used to sign any Assemblies to be loaded) into [master] to then create a Login from and then grant UNSAFE ASSEMBLY to that Login. This is the same sequence of events that one needs to do when loading EXTERNAL_ACCESS and UNSAFE Assemblies, but now, unfortunately, needs to be done for even SAFE Assemblies.

There is currently no mechanism to handle this in a completely portable fashion (i.e. not rely on external files) and cannot be handled by Visual Studio / SSDT without manual intervention. This was kinda already the case, but at least it was possible to create a set up to handle this in a completely portable fashion (i.e. entirely contained within a .sql script): please see Stairway to SQLCLR Level 7: Development and Security for details (this is an article that I wrote).

It is possible to create a Certificate from hex bytes (i.e. FROM BINARY = 0x...) but that does not work with Visual Studio (which relies on MSBuild) / SSDT since using the Certificate requires using signtool and MSBuild uses sn.

In order for this to be made workable such that the Visual Studio / MSBuild / SSDT publishing process works (which in turn would mean that anyone would be able to create a completely self-contained .sql script capable of creating the Asymmetric Key without relying on an external file), the CREATE ASYMMETRIC KEY command needs to be enhanced to allow for being created from a binary string. I have made this suggestion on Microsoft Connect – Allow Asymmetric Key to be created from binary hex bytes string just like CREATE CERTIFICATE – so please support it :-).

Alternatively (for the moment, until MS hopefully creates a better method, such as my Asymmetric Key suggestions), you can try either of the two techniques I describe in the following blog posts (both work fully with SSDT):

• SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1
• SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2

As a last resort, you can consider the following approach:

1. TEMPORARILY set the [master] Database to TRUSTWORTHY ON

   For the next step (i.e. CREATE ASSEMBLY) to execute successfully, the Login that is the database owner (i.e. same SID used by the [dbo] User of [master]) needs to have the UNSAFE ASSEMBLY permission. If [master] is owned by sa or any other sysadmin, then it has all permissions and this requirement has been satisfied. But, if [master] is owned by a low-privileged login (a "best practice"), then you will need to execute the following statement in order for the CREATE ASSEMBLY to work when TRUSTWORTHY is ON:

   EXEC (N'USE [master]; GRANT UNSAFE ASSEMBLY TO [{DB_Owner_Login}];');
   

2. Create the Assembly in [master]
3. Create the Asymmetric Key from the Assembly
4. Drop the Assembly
5. set the [master] Database to TRUSTWORTHY OFF
6. Create the Login from the Asymmetric Key
7. Grant UNSAFE ASSEMBLY to that Login (this replaces the need for the DB where the Assembly is loaded to be set to TRUSTWORTHY ON and for its owner Login to have the UNSAFE ASSEMBLY permission).

Please note that I did not include the new "Trusted Assembly" feature as an option here. The reason it was not mentioned is due to it having many more flaws than benefits, not to mention it being entirely unnecessary in the first place given that existing functionality already handled the situation "Trusted Assemblies" was meant to address. For full details on that and a demo of the proper way to handle existing, unsigned Assemblies, please see: SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment.