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.

Solution 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.

Solution 2

i know that's not a real solution but you can change security mode:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE;
EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;

This is the easiest solution for those who want to continue their work

Solution 3

I came across this the other day, and it seems it is not as bad as it sounds (apart from the fact that you can no longer just create a SAFE assembly, but need to sign it etc., or use TRUSTWORTHY).

In my tests:

• I created an assembly that had a "SAFE" method as well as an "UNSAFE" (it used Task).
• I created the assembly as SAFE (after having built and signed it etc.)
• I created T-SQL wrapper functions around my two methods.
• When executing the "SAFE" function all worked.
• When executing the "UNSAFE", I received a HostProtectionException.

That to me indicates that there are still some controls over what is executing. I followed that up by:

• Re-creating the assembly with PERMISSION_SET = UNSAFE
• Re-created the functions
• Now when I executed the UNSAFE function all worked as expected.

So I am not so sure that the statement in the documentation of 'clr strict security'is 100% correct.

I wrote a blog-post of my experiences, and you can find it here if you want to test it out yourself: http://www.nielsberglund.com/2017/07/02/sql-server-2017-sqlclr-and-permissions/

Niels

Comments

[Jesús López]

Very nice blog-post, thank you very much. So you think the sentence "may be able to access external system resources, call unmanaged code, and acquire sysadmin privileges" is not correct. Perhaps you need to do something fancy and weird to bypass the security checks

[Niels Berglund]

Hi Jesus! Yeah - I think it's not correct, at least not based on my testing.

[Amirhossein]

its work very well. tanks for good and short answer dear Hossein

[ZedZip]

Hi Niels, thank you for your article, it is great. Is it possible to sign uploaded assembly? I.e. I have an assembly in my db, can I sign it in the place? without downloading to dll, sign and then uploading to the db again?

[Niels Berglund]

Hi @ZedZip, what version of SQL are you on- can you use whitelisting?

[ZedZip]

The right question. I need to deploy the solution to 2012-2019 versions of SQL Server

[jenson-button-event]

then how do i still get: The configuration option 'clr strict security' does not exist, or it may be an advanced option. running that?

[hossein andarkhora]

@jenson-button-event yes you should run this command to let you work with clr on your sql server instance

[jenson-button-event]

The salient point is that this only works for 2017+ database instances