(last updated: 2018-11-01 @ 00:50 EDT / 2018-11-01 @ 04:50 UTC )

SQL Server 2017 introduced a new security restriction for SQLCLR in the form of a system configuration option named, “CLR strict security”. So far, this series – SQLCLR vs SQL Server 2017 – has mostly focused on the publishing / deployment aspect of working with Assemblies within this new restriction. However, there might be additional implications of what can be done once an Assembly is loaded. The documentation seems to suggest that the PERMISSION_SET values of SAFE and EXTERNAL_ACCESS effectively no longer exist. Let’s look at the exact wording of the documentation and run a few simple tests to see: a) what SQL Server – the true authority – actually allows, and b) if the documentation accurately reflects that functionality.

The Problem

The documentation for both CREATE ASSEMBLY and ALTER ASSEMBLY states (as of 2018-10-30):

⚠ Warning
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 (14.x), 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. …

And farther down on that same page, there is a note stating:

🛈 Important
The PERMISSION_SET option is affected by the clr strict security option, described in the opening warning. When clr strict security is enabled, all assemblies are treated as UNSAFE.

The last sentence in each of those quoted statements certainly does seem to be saying that when “CLR strict security” is enabled, then UNSAFE is effectively the only PERMISSION_SET, or stated another way: then PERMISSION_SET effectively no longer has any effect. However, as usual, things are perhaps not so simple given that the wording is not explicitly stating that. It would be rather easy to add a few words to both statements to indicate that the PERMISSION_SET property is now being ignored. Other documentation makes similar claims (for example: allow updates Server Configuration Option). So, did they fail to explicitly state this, or was this not stated because it is not what is actually happening?

The Tests

Confirm Environment

First, we need to make sure that we are using SQL Server 2017 (14.x) or newer since the “CLR strict security” configuration option does not (typically) apply to earlier versions. Technically, you can enable “CLR strict security” on SQL Server 2012 / 2014 / 2016, but that can only be set by a startup-only Trace Flag, and won’t be testable via sys.configurations, and is really only uesd for testing when preparring to upgrade to SQL Server 2017 or newer. The outcome of these tests will still apply to those earlier versions if “CLR strict security” is enabled, but for what we are trying to determine right now, we can keep things simple and focus on SQL Server 2017 and newer.

SELECT @@VERSION;
/*
Microsoft SQL Server vNext (CTP2.0) - 15.0.1000.34 (X64) 
    Sep 18 2018 02:32:04 
    Copyright (C) 2018 Microsoft Corporation
    Enterprise Evaluation Edition (64-bit)
        on Windows 10 Home 10.0 <X64> (Build 17134: )
*/

As you can see from that output (in the block comment), I am using SQL Server 2019, CTP 2.

Next, we need to make sure that the “CLR strict security” instance-level configuration option is actually enabled, since that is the basis of this new security context that might be affecting the PERMISSION_SET property.

SELECT cfg.[name], cfg.[value_in_use]
FROM   sys.configurations cfg
WHERE  cfg.[configuration_id] = 1587; -- clr strict security
/*
name                   value_in_use
clr strict security    1
*/-- EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
-- EXEC sp_configure 'CLR strict security', 1; RECONFIGURE;

As you can see, my instance does indeed have “CLR strict security” enabled. If it wasn’t enabled, I could execute the two EXEC statements to enable it.

Please note that while “CLR strict security” is an “advanced” option, the “show advanced options” option does not need to be “1” in order to see the row in sys.configurations. However, to see and/or change the value of the “CLR strict security” option via sp_configure, the “show advanced options” option does need to be “1”.

Test Setup

Next, we create the testing database if it does not already exist:

USE [master];
IF (DB_ID(N'PermissionSetTest') IS NULL)
BEGIN
    CREATE DATABASE [PermissionSetTest] WITH TRUSTWORTHY OFF;
    ALTER DATABASE [PermissionSetTest] SET RECOVERY SIMPLE;
END;
GO
USE [PermissionSetTest];
GO

Now, for the first two tests I will use two different functions from the Free version of SQL# (a SQLCLR library that I created). While SQL# does contain many more functions than we need for these tests, the installer already handles enabling “clr integration” if it’s not already enabled, and setting up proper security. The SQL# installer does not disable “CLR strict security”, and it does not set TRUSTWORTHY to ON; it uses module signing ( https://ModuleSigning.info/ ). That’s several steps that I don’t need to re-create for the simple, quick tests that we are doing here.

Install SQL# ( https://SQLsharp.com/free/ ) into the [PermissionSetTest] database (requires editing only the USE line of the install script).

Check Run-time Restrictions

EXTERNAL_ACCESS

We already verified that “CLR strict security” is enabled. Here we will verify that the database property of TRUSTWORTHY is disabled, which is the better / preferred security setting:

SELECT db.[name], db.[is_trustworthy_on]
FROM   sys.databases db
WHERE  db.[database_id] = DB_ID();
/*
name                 is_trustworthy_on
PermissionSetTest    0
*/

And we see that TRUSTWORTHY is disabled. Great.

Next we verify that the Assembly containing the SQLCLR code that we are about to execute is marked as PERMISSION_SET = SAFE. We are verifying this because the documentation seems to be suggesting that when “CLR strict security” is enabled, the Assembly will be “treated as being UNSAFE“, which allows for accessing external resources.

SELECT asm.[permission_set_desc]
FROM   sys.assemblies asm
WHERE  asm.[name] = N'SQL#.DB';
-- SAFE_ACCESS

And yes, the Assembly is marked as being SAFE.

Next we will create a global temporary table to be the destination table for a bulk copy operation. The .NET SqlBulkCopy class requires an external / regular database connection for the destination. Since an external connection is a different session, a local temporary table would not be visible to that destination connection. We could use a permanent table in [tempdb] instead of using a global temporary table, but the temp table will be automatically cleaned up when we close this query tab.

CREATE TABLE ##TempDestination ([Col1] INT);
-- TRUNCATE TABLE ##TempDestination;

Now we run our first test. Please note that the default source connection for DB_BulkCopy is the internal context connection (i.e. the current session / transaction), and the default destination connection is the current instance using a trusted connection.

EXEC SQL#.DB_BulkCopy
  @SourceQuery = N'SELECT [object_id] FROM master.sys.objects;',
  @DestinationTableName = N'##TempDestination';

That returned the following error:

The current PERMISSION_SET on the Assembly does not allow this operation (SqlClientPermission).

    Please execute the following SQL in order to fix this:

        EXEC SQL#.SQLsharp_SetSecurity 2, N’SQL#.DB’;

It looks like we actually do not have the ability to access external reources. So we can execute the command suggested in the error message to see if that helps. And then we will verify that the PERMISSION_SET was changed.

EXEC SQL#.SQLsharp_SetSecurity 2, N'SQL#.DB';
SELECT asm.[permission_set_desc]
FROM   sys.assemblies asm
WHERE  asm.[name] = N'SQL#.DB';
-- EXTERNAL_ACCESS

Now that the PERMISSION_SET is EXTERNAL_ACCESS we will try again:

EXEC SQL#.DB_BulkCopy
  @SourceQuery = N'SELECT [object_id] FROM master.sys.objects;',
  @DestinationTableName = N'##TempDestination';
SELECT * FROM ##TempDestination;
-- 113 rows returned

Ah ha. This time it worked.

This test, by itself, pretty much proves that SAFE Assemblies are not being treated as if they were marked as UNSAFE, at least not in terms of executing code within them. But, we will try a few more tests to hopefully narrow down what “being treated as if marked as UNSAFE” does mean.

UNSAFE

For the second test we will execute code that requires the Assembly be marked as UNSAFE. The function is supposed to play a specified frequency for a specified number of milliseconds. This will work if you are using SQL Server Express LocalDB, which runs in user-mode and not as a background process, but otherwise you likely won’t hear anything. Either way, whether or not you can hear the tone has no impact on the permissions required to execute the code.

SELECT asm.[permission_set_desc]
FROM   sys.assemblies asm
WHERE  asm.[name] = N'SQL#.OS';
-- SAFE_ACCESS
SELECT SQL#.OS_GenerateTone(1000, 1000);

That returned the following error:

System.Security.HostProtectionException: Attempted to perform an

operation that was forbidden by the CLR host.

The protected resources (only available with full trust) were: All

The demanded resources were: UI

Just like the first test, the Assembly being marked as SAFE did not allow the code to execute. And this time the actual .NET error message comes through and even mentions “full trust”, which equates to UNSAFE.

So let’s try the next level of permissions:

EXEC SQL#.SQLsharp_SetSecurity 2, N'SQL#.OS';
SELECT asm.[permission_set_desc]
FROM   sys.assemblies asm
WHERE  asm.[name] = N'SQL#.OS';
-- EXTERNAL_ACCESS
SELECT SQL#.OS_GenerateTone(1000, 1000);

That returned the same error:

System.Security.HostProtectionException: Attempted to perform an

operation that was forbidden by the CLR host.

The protected resources (only available with full trust) were: All

The demanded resources were: UI

Ok. Now we can try setting the Assembly to UNSAFE:

EXEC SQL#.SQLsharp_SetSecurity 3, N'SQL#.OS';
SELECT asm.[permission_set_desc]
FROM   sys.assemblies asm
WHERE  asm.[name] = N'SQL#.OS';
-- UNSAFE_ACCESS
SELECT SQL#.OS_GenerateTone(25000, 3000);
-- Success (well, no tone, but no error either)

And that finally worked. Once again we see that in terms of executing code, each PERMISSION_SET works just as it always had; there is no change in behavior from previous versions.

Check Creation Restrictions

If “being treated as if marked as UNSAFE” doesn’t apply to executing code, maybe it has to do with the verification steps done when executing the CREATE ASSEMBLY and ALTER ASSEMBLY statements? The verification steps can be found in the documentation for CREATE ASSEMBLY as well as on the following page: CLR Integration Programming Model Restrictions.

For this test we will once again verify that “CLR strict security” is enabled. Then we will enable the database property of TRUSTWORTHY. While it is generally a bad idea to enable TRUSTWORTHY, doing so is one of the ways to get Assemblies to load if “CLR strict security” is enabled. The idea here is that we will create an Assembly that has a static class variable that is not marked as “readonly”. This is violation is something that can be caught when executing CREATE ASSEMBLY and ALTER ASSEMBLY. And, since Assemblies marked as UNSAFE are allowed to have non-“readonly” static class variables, if Assemblies truly are “being treated as if marked as UNSAFE“, then creating the Assembly should succeed, even if marked as SAFE.

SELECT cfg.[name], cfg.[value_in_use]
FROM   sys.configurations cfg
WHERE  cfg.[configuration_id] = 1587; -- clr strict security
/*
name                   value_in_use
clr strict security    1
*/ALTER DATABASE [PermissionSetTest] SET TRUSTWORTHY ON;
SELECT db.[name], db.[is_trustworthy_on]
FROM   sys.databases db
WHERE  db.[database_id] = DB_ID();
/*
name                 is_trustworthy_on
PermissionSetTest    1
*/

The output shown above indicates that “CLR strict security” is still enabled, and now the database property of TRUSTWORTHY is enabled.

The .NET C# code for the Assembly is:

using Microsoft.SqlServer.Server;
public class StoredProcedures
{
    private static string CaughtByCreateAssemblyChecks = "Nope";
    [SqlProcedure]
    public static void PrintHello()
    {
        SqlContext.Pipe.Send("Hello");
    }
}

I have compiled that code into a DLL (i.e. an Assembly). We will then store the Assembly’s definition in temp table so that it can be used across multiple queries that are going to be executed individually and not in a batch. I used BinaryFormatter – an open-source tool that I created and host on GitHub – to convert the DLL into a string representation of the hex bytes needed for CREATE ASSEMBLY, broken into lines of 76 digits each. The Assembly definition has been shortened so that it could be posted here. You can get the full script (including all of the T-SQL commands in this post) at PasteBin: “CLR strict security” and PERMISSION_SET.

CREATE TABLE #AssemblyBits ([HexBytes] VARBINARY(MAX) NOT NULL);
INSERT INTO #AssemblyBits ([HexBytes]) VALUES (0x4D5A90000300000004000000FFFF0000B80000000000000040000000000000000000000000...
00000000000000000000000000000000000000000000000000000000000000000000000000000000000000);

Now that the Assembly definition is easily accessible (without having to paste multiple copies of that definition into the script), we can attempt the actual loading of the Assembly:

-- Get Assembly definition from temp table:
GO
DECLARE @Assembly VARBINARY(MAX);
SELECT @Assembly = [HexBytes] FROM #AssemblyBits;
-- Create Assembly from that definition:
CREATE ASSEMBLY [UnsafeStuff]
AUTHORIZATION [dbo]
FROM @Assembly
WITH PERMISSION_SET = SAFE;

That returned the following error:

Msg 6211, Level 16, State 1, Line XXXXX

CREATE ASSEMBLY failed because type ‘StoredProcedures’ in safe assembly ‘UnsafeStuff’ has a static field ‘CaughtByCreateAssemblyChecks’. Attributes of static fields in safe assemblies must be marked readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language.

That didn’t work because of the checks performed on SAFE and EXTERNAL_ACCESS Assemblies. This behavior is also the same as it has been in all previous versions, going all the way back to SQL Server 2005 when SQLCLR was introduced. Perhaps enabling TRUSTWORTHY isn’t enough to allow loading unsigned Assemblies?

For this final test, we will attempt to create the Assembly as UNSAFE:

-- Get Assembly definition from temp table:
GO
DECLARE @Assembly VARBINARY(MAX);
SELECT @Assembly = [HexBytes] FROM #AssemblyBits;
-- Create Assembly from that definition:
CREATE ASSEMBLY [UnsafeStuff]
AUTHORIZATION [dbo]
FROM @Assembly
WITH PERMISSION_SET = UNSAFE;
-- Commands completed successfully.

Ah ha. That actually worked. This means that the checks done on the code in the Assembly, when executing CREATE ASSEMBLY and ALTER ASSEMBLY, are the same as they have been since SQLCLR was introduced; there is no change in behavior, at least not in terms of the checking the contents of Assemblies as they are loaded into SQL Server.

Where Does That Leave Us?

So far we have not seen any changes in behavior from prior versions of SQL Server with respect to the three PERMISSION_SET options. Has anything changed?

Yes. One thing has changed: the security requirements for creating SAFE and EXTERNAL_ACCESS Assemblies are now the same as for creating UNSAFE Assemblies. When “CLR strict security” is enabled, and TRUSTWORTHY is disabled, you can no longer create:

• SAFE Assemblies that are not signed
• EXTERNAL_ACCESS Assemblies that are signed, but the associated signature-based Login has the EXTERNAL ACCESS ASSEMBLY permission instead of the UNSAFE ASSEMBLY permission

And so this is what “being treated as if marked as UNSAFE” actually means.

Conclusion

Given the current wording of the documentation for CREATE ASSEMBLY and ALTER ASSEMBLY, it’s easy to see how people would be quite easily confused about what the PERMISSION_SET property actually does when “CLR strict security” is enabled. It certainly does sound like there is only one effective value when that option is enabled: UNSAFE. But as we have seen, that is clearly not the case.

The reality is that the only change is in the security requirements for creating / altering Assemblies. Going back to the explanation for this change (quoted at the beginning of this post), we can now understand that they are saying: since it is possible to do things in SAFE and EXTERNAL_ACCESS Assemblies that should only be possible in UNSAFE Assemblies ¹, we need to provide the same level of explicit trust to all Assemblies, just in case.

And so, putting all of this info and evidence together, we can restate the message as:

” When the instance-level configuration option “CLR strict security” is enabled, the requirements for loading Assemblies having a PERMISSION_SET of either SAFE or EXTERNAL_ACCESS , into SQL Server (using CREATE ASSEMBLY or ALTER ASSEMBLY) and into memory (by accessing anything inside the Assembly), are the same as they would be for loading an Assembly marked as UNSAFE. ”

I will submit documentation fixes for CREATE ASSEMBLY and ALTER ASSEMBLY so that this behavior is stated clearly.