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

(last updated: 2019-03-31 @ 16:30 EST / 2019-03-31 @ 20:30 UTC )

As mentioned in Part 1 of this “SQLCLR vs. SQL Server 2017” series, the new clr strict security server-level configuration option requires that in order to create any Assembly, even a SAFE one, it must be signed (by a Certificate or Strong Name Key), and there must already exist a corresponding Login, based on the same signature, that has the UNSAFE ASSEMBLY permission.

This new requirement prevents the technique described towards the end of Part 1 from working. That technique uses a SAFE Assembly as an indirect means of creating the Asymmetric Key to create the Login from. That worked perfectly prior to SQL Server 2017, but now even SAFE Assemblies require that the signature-based Login be created first, which now puts us in a whole chicken-egg paradox.

Before proceeding to the solution, it should be noted that yes, Microsoft has, as of RC1 (released on 2017-07-23), provided a kinda/sorta “fix” for this that allows for creating an Assembly without having the signature-based Login. HOWEVER, that “fix” is convoluted, less secure than existing functionality, and entirely unnecessary. It should not be used by anyone. Ever! In fact, it should be completely removed. In no uncertain terms: it is not an option! To help clarify, I am being intentionally vague about that new feature here (and in Part 1) so as not to distract from these two solutions (this post and Part 3) that do not promote bad practices; it will be covered starting in Part 4.

Solution Description

Let’s first review the goals / best-practices we are adhering to for any approach to publishing SQLCLR code (the reasoning for these is provided in Part 1):

1. The process needs to be able to use Visual Studio (VS) / SQL Server Data Tools (SSDT)
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)
3. The Database containing the Assembly needs to keep TRUSTWORTHY OFF
4. Need to keep clr strict security enabled

A technique for publishing SQLCLR objects that maintains the first three goals / best-practices is described towards the end of Part 1, and is explained in detail in my SQL Server Central article: Stairway to SQLCLR Level 7: Development and Security. The addition of goal / best-practice #4, starting in SQL Server 2017, initially invalidates that technique. However, with only a few extra one-time / setup steps, that technique can still work.

The trick is to be tricky. (That’s not very helpful, is it?) By tricky I mean that we have various tools to use, and we usually think in terms of using one or the other, but sometimes they can also be used in combination. Let’s look at what we know:

1. Assemblies can be created from a VARBINARY literal.
2. Before an Assembly can be created:
   1. it must be signed (by Strong Name Key or Certificate)
   2. there needs to be a Login created from the same signature
   3. the signature-based Login must be granted the UNSAFE ASSEMBLY permission
3. Asymmetric Keys:
   1. cannot be created from a VARBINARY literal
   2. can be created from an Assembly
4. Certificates can be created from a VARBINARY literal

The only two objects that can be created from a VARBINARY literal are Assemblies and Certificates. We can no longer use an Assembly as the starting point, so we need to figure out a way to use a Certificate. But Visual Studio and SSDT use Asymmetric Keys (via a Strong Name Key), not Certificates, so we still need to use an Asymmetric Key for the main Assemblies. The Asymmetric Key that will be used to create the Login that allows us to create the main Assembly(ies) needs to be created from an Assembly that is signed with the same Strong Name Key that is used to sign the main Assembly(ies). And creating that Assembly requires that it be signed with a Certificate so that the Certificate can be created before anything else. Once that Certificate exists, a Login can be created from it and granted the UNSAFE ASSEMBLY permission. Then, the first Assembly can be created, and from that Assembly we can create the Asymmetric Key and its associated Login. At that point we can drop the first Assembly, the Certificate-based Login, and the Certificate as we won’t need any of those three items again.

So, the tricky trick is that the first Assembly — the one that we create the Asymmetric Key from — is signed by both a Strong Name Key and a Certificate. Does this actually work? You betcha :-). Again, those steps only need to be performed once, and they can all be placed into a “PreDeploy” SQL script. SSDT will continue to use the Strong Name Key / Asymmetric Key, and will know nothing of the Certificate as that part will be handled manually.

To be fair, there is a second, minor trick to this technique: you need to convert / format two binary files — the certificate .cer file and the DLL signed with the certificate — into a string representation so that they can be the VARBINARY literals used to create those objects. Visual Studio and SSDT are not going to handle this part, so I wrote a small command-line utility to do the conversion. I recently posted it to GitHub — Binary Formatter — including a pre-compiled .exe so that nobody needs to mess with compiling it. It is very simple as I wrote it years ago to only be used by myself so that I could automate the build for SQL#. But it seems like others might benefit from this as well, so it is now freely available to all. One nice yet non-standard feature it has is to chop long binary lines into many lines of a specific max length, each line (except the last) being appended with the T-SQL line-continuation character: \

Below are the steps I took to create a working example of this technique. Regarding these steps, please note that:

• they are effectively the same as what I described in Stairway to SQLCLR Level 7: Development and Security, with the main differences being the additional steps 7 – 14, and 20 – 21.
• they assume that one is working with an already existing Solution / Project that simply needs this technique applied so that it can continue deploying to SQL Server 2017 (even if the Assembly is SAFE). For the purposes of having a shareable, working example, I started with a Project containing just the following simple RegEx UDF:

  using System;
  using System.Data.SqlTypes;
  using System.Text.RegularExpressions;
  using Microsoft.SqlServer.Server;
  
  public class UDFs
  {
      [SqlFunction(IsDeterministic = true, IsPrecise = true)]
      public static SqlBoolean RegEx_IsMatch(SqlString Text,
                                             SqlString Expression)
      {
          return Regex.IsMatch(Text.Value, Expression.Value,
              RegexOptions.IgnoreCase, new TimeSpan(0, 0, 30));
      }
  }
  

• this is not a fully-automated solution. The steps outlined below require manually building what will ultimately become a “PreDeploy” SQL script, used by the main / existing Project. A fully-automated approach will be provided in the next article in my Stairway to SQLCLR series on SQL Server Central.
• I used Visual Studio 2015, though nothing should really be any different going back as far as Visual Studio 2012.
• again, BinaryFormatter.exe, used in steps 8 and 9 immediately below, is available for free on GitHub.

Solution Steps

The following one-time setup steps are done in Visual Studio, and are required for the subsequent two sets of steps:

1. Create New Project (a SQL Server Database Project), “SQL2017_KeyAsm”, in Visual Studio Solution
2. Go to “Project” menu | “Properties…” | SQLCLR tab | Signing… button
3. Check the “Sign the assembly” check-box
   1. Select “<New…>” in the “Choose a strong name key file:” drop-down
   2. Enter a filename in the “Key file name:” text field. No extension is needed (I used “SigningKey” for this example)
   3. Do not uncheck the “Protect my key file with a password” checkbox!
   4. Enter in a password.
   5. Click the “OK” button
   6. You should now have a “filename.pfx” file (for me this was SigningKey.pfx) in your main project folder (i.e. $(ProjectDir) ). This file is being used here to sign what will be the SQL2017_KeyAsm.dll assembly, and you will reference it in the main project to sign its assembly.
4. Build the project ( SQL2017_KeyAsm.dll is now signed with the private key in SigningKey.pfx )
5. In a Command Prompt window (create the certificate):

       MAKECERT -r -pe -n "CN=SqlQuantumLeap.com" -e "12/31/2099" ^
       -sv SQL2017-ClrStrictSecurity-Cert.pvk ^
       SQL2017-ClrStrictSecurity-Cert.cer
       

   (password = blah ; ^ is the DOS line-continuation character)

6. In a Command Prompt window (merge .cer and .pvk files into .pfx file):

       PVK2PFX -pvk SQL2017-ClrStrictSecurity-Cert.pvk -pi blah ^
       -spc SQL2017-ClrStrictSecurity-Cert.cer -pfx ^
       SQL2017-ClrStrictSecurity-Cert.pfx
       

7. In a Command Prompt window (sign the empty assembly with the .pfx file):

       SIGNTOOL sign /f SQL2017-ClrStrictSecurity-Cert.pfx /p blah ^
       /v SQL2017_KeyAsm.dll
       

   ( SQL2017_KeyAsm.dll is now also signed with the private key in SQL2017-ClrStrictSecurity-Cert.pfx )

8. In a Command Prompt window (convert public key for VARBINARY literal):

       BinaryFormatter.exe .\SQL2017-ClrStrictSecurity-Cert.cer ^
       .\SQL2017-ClrStrictSecurity-Cert.sql 40
       

   (use SQL2017-ClrStrictSecurity-Cert.sql in Step 1 immediately below)

9. In a Command Prompt window (convert empty assembly for VARBINARY literal):

       BinaryFormatter.exe .\SQL2017_KeyAsm.dll .\SQL2017_KeyAsm.sql 40
       

   (use SQL2017_KeyAsm.sql in Step 4 immediately below)

The following steps should be incorporated into a single SQL script. That script can be run once manually, or can be made into a re-runnable (idempotent) script to be used as a “PreDeploy” script for the main Project. Please note that steps 1 and 4 require the output from steps 8 and 9 above, respectively.

1. CREATE CERTIFICATE [TempCert] FROM BINARY = 0x{contents_of_ClrStrictSecurity-Cert.sql};
2. CREATE LOGIN [TempLogin] FROM CERTIFICATE [TempCert];
3. GRANT UNSAFE ASSEMBLY TO [TempLogin];
4. CREATE ASSEMBLY [SQL2017-ClrStrictSecurity-KeyAsm] FROM 0x{contents_of_SQL2017_KeyAsm.sql};
5. CREATE ASYMMETRIC KEY [SQL2017-ClrStrictSecurity-Key] FROM ASSEMBLY [SQL2017-ClrStrictSecurity-KeyAsm];
6. CREATE LOGIN [SQL2017-ClrStrictSecurity-Login] FROM ASYMMETRIC KEY [SQL2017-ClrStrictSecurity-Key];
7. GRANT UNSAFE ASSEMBLY TO [SQL2017-ClrStrictSecurity-Login]; -- REQUIRED!!!!
8. DROP ASSEMBLY [SQL2017-ClrStrictSecurity-KeyAsm];
9. DROP LOGIN [TempLogin];
10. DROP CERTIFICATE [TempCert];

Finally, the following steps are a one-time setup for the Project (and any new Projects added to this Solution) that will cause Assembly to be signed with the same Strong Name Key that was loaded into SQL Server via the steps detailed above.

1. In main Project, go to Project Properties | SQLCLR tab | Signing… button
2. Check the “Sign the assembly” check-box, “Browse…” in “Choose a strong name key file”, find “SigningKey.pfx” in the “Signing Key” project folder. Using the same .pfx file to sign both projects is what makes the security work within SQL Server. This is the link between your assembly and the Asymmetric Key loaded into [master].
3. (optional) set up a “PreDeploy” SQL script consisting of the T-SQL commands in the previous set of steps

If you want to see the fully working example of this technique, incorporating the RegEx UDF C# code shown above, it is available on PasteBin.

Solution Recap

Here is a general overview of the steps again, from the perspective of SQL Server, just to make sure that everyone understands the workflow:

1. USE [master];
2. Create Certificate from hex bytes
3. Create Login-A from Certificate, and grant it the UNSAFE ASSEMBLY permission
4. Create Assembly-1 that is signed by both the Certificate and the same Strong Name Key used to sign all other Assemblies / DLL (at least in this Solution), but is otherwise empty, from hex bytes
5. Create Asymmetric Key from Assembly-1
6. Create Login-B from Asymmetric Key, and grant it the UNSAFE ASSEMBLY permission
7. Drop Assembly-1
8. Drop Login-A
9. Drop Certificate
10. USE [other_DB];
11. Create Assembly-2, signed with the same Strong Name Key used to sign Assembly-1, from hex bytes
12. Create T-SQL wrapper objects (i.e. CREATE STORED PROCEDURE … WITH EXTERNAL NAME AS …, etc).

Additional thoughts

While this technique does work, it is also quite a few steps. Might there be an easier way? I’m glad you asked as there are two things to mention:

1. In coming up with the example code for this post I discovered another, simpler approach that is several steps shorter than what is described above (including not needing the additional Project / Assembly). That technique is “Solution 2” and will be explained in the next post, Part 3.

2. What would truly make this new restriction a complete non-issue is if an Asymmetric Key could be created from a VARBINARY literal, just like Certificates. If this ability existed, then the entire one-time setup would consist of nothing more than:

   1. Create Asymmetric Key from hex bytes
   2. Create Login from Asymmetric Key
   3. Grant Login UNSAFE ASSEMBLY

   Another benefit of this ability would be that SSDT could finally fully handle signature-based security, thereby removing one of the main reasons that so many people set TRUSTWORTHY ON. What a wonderful world that would be.

   To help make this world a better place, please up-vote the following suggestion of mine: Allow Asymmetric Key to be created from binary hex bytes string just like CREATE CERTIFICATE

   A related suggestion of mine that would be used, at the very least, by SSDT to import existing Databases and/or objects is: Add function to extract Asymmetric Key similar to CERTENCODED for Certificates

Conclusion

As we can see here, even though the new clr strict security server configuration option appears to prevent us from keeping our goals of having a fully encapsulated T-SQL script that can be produced by SSDT, all while not enabling TRUSTWORTHY or disabling clr strict security, we actually can accomplish it by using both a Certificate and an Asymmetric Key.

In the next post we will see how to accomplish this in fewer steps, with only a Certificate.

Please also see:

• “CLR strict security” Server Configuration Option
• CREATE ASYMMETRIC KEY
• CREATE CERTIFICATE