The Good, the Bad, and the Ugle̅e̅ (need to avoid copyright infringement ?? )

SQL Server 2017 is soon to be officially released (i.e. RTM) and there are some impressive changes, with some being impressively good, and some being impressively bad.

The Good

Some amazingly good changes are: Linux as a platform (see my editorial on SQL Server Central about it: Why I am Excited about SQL Server on Linux), Adaptive Query Processing, Resumable online index rebuild, some new DMVs, some new built-in string functions, etc.

Unfortunately, not all changes fit into this category.

The Bad

SQL Server 2017 happens to come with the first change to SQLCLR since SQL Server 2012: a server-level configuration option named “clr strict security” (which we will cover in this post and the next two). Unfortunately, this is not a happy / fun / positive change; it is a rather annoying, security-related change. By itself it might even be considered amazingly bad, but an even newer feature was introduced that makes this one merely “bad”.

The Ugle̅e̅

In order to alleviate some of the (perceived) hardship and complication of “clr strict security”, a second change was introduced in RC1 (released on July 17th). This new change, “Trusted Assemblies” (which will be covered starting in Part 4), is entirely unnecessary due to existing functionality being able to handle all of the various scenarios. But, the reason why I consider this feature to be more than merely “bad” is because of the security implications and overall clunkiness of it.

The Bad

Without going into too much detail (since a more complete description of the reason for this change will be provided in an up-coming post), the .NET security infrastructure changed with the release of .NET Framework version 4.5. While SQL Server 2016 was somehow not affected, SQL Server 2017 was not so lucky. As noted on the documentation page for Breaking Changes to Database Engine Features in SQL Server 2017 (so far it’s the only breaking change):

CLR uses Code Access Security (CAS) in the .NET Framework, which is no longer supported as a security boundary. Beginning with SQL Server 2017 Database Engine, 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 CLR assemblies as if they were marked UNSAFE. The clr strict security option can be disabled for backward compatibility, but this is not recommended. When clr strict security is disabled, a CLR assembly created with PERMISSION_SET = SAFE may be able to access external system resources, call unmanaged code, and acquire sysadmin privileges. After enabling strict security, any assemblies that are not signed will fail to load. Also, if a database has SAFE or EXTERNAL_ACCESS assemblies, RESTORE or ATTACH DATABASE statements can complete, but the assemblies may fail to load.

To load the assemblies, you must either alter or drop and recreate each assembly so that it is signed with a certificate or asymmetric key that has a corresponding login with the UNSAFE ASSEMBLY permission on the server.

What all of that means is that, assuming clr strict security is “1” (i.e. enabled), and TRUSTWORTHY is “OFF” for the Database in which an Assembly is being created, then in order to create any Assembly you first need to:

1. Sign the Assembly with a strong-name key or a certificate
2. Create an Asymmetric Key or Certificate in master from whatever you signed the Assembly with
3. Create a Login based on that Asymmetric Key or Certificate
4. Grant that Login the UNSAFE ASSEMBLY permission

Is that really so bad? Aren’t many of us (hopefully!) already doing that?

The Background / Context

In order to understand the negative impact of this change, let’s take a look at how things worked prior to this change (i.e. in SQL Server versions 2005, 2008, 2008 R2, 2012, 2014, and 2016):

• For creating Assemblies marked as PERMISSION_SET = SAFE there was no requirement that the Assembly be signed.
• For creating Assemblies marked as EXTERNAL_ACCESS or UNSAFE there were two choices:
  1. Set the Database containing the Assembly to TRUSTWORTHY OFF.
  2. Sign the Assembly, create the Asymmetric Key or Certificate in master, create the Login, and finally grant the Login the EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission.

Given those options, we now need to consider some overall goals / best-practices in order to figure out the implementation as there is some variation as to how (and in which order) we can create these objects:

1. The process needs to be able to use Visual Studio / SQL Server Data Tools (SSDT):

   While you can run the compiler — csc.exe or vbc.exe — manually, I can’t think of a reason to do so. The Build and Publish / Deploy processes can be automated via MSBuild.exe and SqlPackage.exe. There are also a lot of options to configure for the compiler, and Visual Studio manages them quite effectively. Besides, while there will always be advanced users who might opt to handle at least one of these steps manually, we need a system that doesn’t require being an advanced user.

2. The publishing / deployment needs to be fully encapsulated within a T-SQL script (i.e. no external dependencies such as DLLs or snk / cer files):

   Modifying the Database is done via T-SQL scripts, and SQLCLR objects (and their dependencies) should be no different. While Assemblies, Asymmetric Keys, and Certificates can be created from external files (e.g. the compiled DLL), doing so introduces additional points of failure into the process by not only needing to worry about file paths, but also by making sure that the file is on the server, or that the path is a UNC share, and in either case that the service account running SQL Server can access the file. Encapsulation is the approach already being taken by SSDT, though only with regards to the Assembly since SSDT doesn’t handle Asymmetric Keys.

3. The Database containing the Assembly needs to keep TRUSTWORTHY OFF:

   Databases should not be set to TRUSTWORTHY ON unless it is absolutely necessary to do so. The three main reasons for setting TRUSTWORTHY ON are:

   1. Manually loading a .NET Framework library that is not in the list of Supported .NET Framework Libraries (in which case TRUSTWORTHY ON is far from the only concern). ¹
   2. Not wanting to spend the extra few minutes to set up the Asymmetric Key and its associated Login.
   3. Not understanding module singing and how it eliminates the need for TRUSTWORTHY ON, Impersonation, and “Cross-database Ownership-chaining” in nearly all scenarios.

With all of that in mind, we can proceed as follows:

• If our Assembly is SAFE then there is nothing more to do:
  1. The Database can remain set to TRUSTWORTHY OFF
  2. There is no Asymmetric Key or Certificate needed, so SSDT is fine as is
  3. SSDT formats the DLL as a VARBINARY literal so that that script can create the Assembly FROM 0x….
• If our Assembly is EXTERNAL_ACCESS, or if it requires UNSAFE for something found at runtime and not during the verification done at CREATE ASSEMBLY time, then:
  1. We will need an Asymmetric Key or Certificate in master, BUT it can be created after the Assembly since any errors will only show up when attempting to execute the SQLCLR code (i.e. run time). You just need to create the Assembly as SAFE.
  2. The Database can remain set to TRUSTWORTHY OFF.
  3. SSDT still works, though it will need a “PostDeploy” SQL script to: copy the Assembly into master, create the Asymmetric Key from the Assembly, create the Login from the Asymmetric Key, grant the Login the appropriate permission, drop the Assembly (only from master, if that isn’t clear) and finally, execute:

           ALTER ASSEMBLY [{assembly_name}]
           WITH PERMISSION_SET = { EXTERNAL_ACCESS | UNSAFE };
           

  4. Everything is still encapsulated within the SQL script:
     • SSDT formats the DLL as a VARBINARY literal
     • The Assembly created in master can be accomplished by using Dynamic SQL and the value from the following query:

                      DECLARE @Assembly NVARCHAR(MAX);
                      SELECT @Assembly = CONVERT(NVARCHAR(MAX), [content], 1)
                      FROM   sys.assembly_files
                      WHERE  [file_id] = 1
                      AND    [name] = N'{assembly_name}';
                      EXEC (N'USE [master];
                      CREATE ASSEMBLY [tmp] FROM ' + @Assembly);
                      

• If our Assembly requires UNSAFE for something found during the verification done at CREATE ASSEMBLY time, then:

  1. We will need an Asymmetric Key or Certificate in master, AND this time it (and its associated Login) must be created before the Assembly since the CREATE ASSEMBLY statement will fail otherwise.
  2. Certificates have an advantage here over Asymmetric Keys since they can be created from a VARBINARY literal (thereby keeping with the encapsulation / no external files goal). HOWEVER, Visual Studio and SSDT do not use Certificates for signing! OOPS!

     BUT WAIT, all is not lost. We can still accomplish this without giving up any of our goals. We can’t create the UNSAFE Assembly, but we can still create a SAFE Assembly without first having the signature-based Login. And since we can create Asymmetric Keys from Assemblies, we can use a signed, yet empty Assembly as an indirect mechanism for creating an Asymmetric Key from a VARBINARY literal. And then we create the Login, grant it the appropriate permission, and then we can create the UNSAFE Assembly. Woo hoo!

  3. The Database can remain set to TRUSTWORTHY OFF.
  4. SSDT still works. You will need a new Project within the Solution that will be the empty, signed Assembly. Then, the main Project will need a “PreDeploy” SQL script to take the DLL created from the new Project and use it to first create the Asymmetric Key and associated Login.
  5. Everything is still encapsulated within the SQL script.

  I provided a detailed description of this technique, including a working example, in the following article on SQL Server Central: Stairway to SQLCLR Level 7: Development and Security. I also use this technique for the SQL# install script.

The Problem

So, if the technique mentioned directly above solves all of the problems, then what is left? This is where the new restriction comes into play. We now have an additional goal:

4.. Need to keep clr strict security enabled

And due to this new goal, we can no longer use a SAFE Assembly as the means of creating the Asymmetric Key without needing an external file. Remember:

1. All Assemblies need to have the signature-based Login (with the appropriate permission) created before the Assembly can be created.
2. Certificates can be created without an external file, but Visual Studio and SSDT do not sign with Certificates (more on this in Part 3).

The Solutions

In Parts 2 and 3 we will see two different approaches to working within the constraint imposed by the new “clr strict security” server-level configuration option. Each of the approaches uses already existing SQL Server functionality while maintaining all of the stated goals / best-practices:

1. Works with SSDT
2. Complete encapsulation within a SQL script
3. Never enable TRUSTWORTHY
4. Never disable clr strict security

Conclusion

While there might be good reason to impose tighter security restrictions on SQLCLR code (given underlying changes within the .NET Framework), it is sad and disappointing that it was handled in this manner. By disallowing even SAFE Assemblies from being created without a) being signed, and b) having the associated signature-based Login with the appropriate permission, that forces anyone deploying / publishing from hex bytes instead of the DLL (which is hopefully most of us, but at least anyone using Visual Studio / SSDT) to either disable clr strict security or enable TRUSTWORTHY. But we shouldn’t do either of those. Certificates can be created from a VARBINARY literal, but Visual Studio and SSDT don’t sign with Certificates, so that doesn’t help.

My concern is that rather than take the few extra steps of temporarily enabling TRUSTWORTHY, loading the Assemblies, disabling TRUSTWORTHY, and then creating the Asymmetric Key and its associated Login, most people will just make one of those two configuration changes and never set them back to their ideal / recommended / default state. And, since Visual Studio makes it easy enough to change Database-level settings, most people will probably just disable TRUSTWORTHY.

Microsoft, for years, has done little to promote the best-practice of using signatures and the signature-based Login as opposed to enabling TRUSTWORTHY to handle SQL security. And now, looking at this change only (ignoring the “fix” introduced in RC1 that actually makes things worse), they are effectively forcing most people into that bad-practice of enabling TRUSTWORTHY.

FORTUNATELY, I provide actual solutions to this new restriction in the following posts in this series.

Here is how I feel about this change: 😦  😞  😒  👿

Overall, two big 👎     👎.

Please also see:

• “CLR strict security” Server Configuration Option