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 ^

    (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 ^
  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};
  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


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:

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

  1. I don’t think TSQL has a line continuation character \ as you say towards the end of the solution description…was that a mistype or am I mistaken?

  2. So I can’t seem to get past create cert from binary: Incorrect syntax near hash. Is there a way to do it straight from the file or am I right in assuming it was intended that I copied it out of the .sql file (provided by BinaryFormatter) and pasted it into a query window.

    Using the latest Sql 2017 install from MS.

    Side query has any of this changed in the last several months. When I was testing I was, unknowingly, using sql 2016 and this was WAY WAY WAY easier. Then I went to set up a local “from scratch” run, so pulled the latest Sql install and ran into all of this and it’s quite frustrating. Kind of makes me want to downgrade the instance I’m going to use.

    1. Hello Zexks. I am not sure what you mean by “near hash” in that error message. Yes, your assumption is correct and that you copy and paste from the file into the SQL script. Are you following the demo script as a guide? If you have the FROM BINARY = 0x\ on one line and paste the BinaryFormatter output on the next line, then you need to have that final backslash \ after the 0x to continue the line, else it will error. And if you have that final \, then you cannot have anything after it outside of a newline. So either 0x\ and a newline, then paste, else 0x and no newline (or any whitespace) and paste. If that doesn’t help, then I will need more info on exactly what you are doing and the exact error message. Please let me know either way.

      Also, no, no has changed in the last few months. The way things are now seems to be the way that they will be. But no need to downgrade as once you get this to work the first time, it is very easy to do again on other projects.

      1. I am getting Incorrect syntax near 0x4D5A9000003….. restOfHash. Tried adding the \ after 0x and putting the BinaryFormatter file contents on the next line and no good. I’m currently trying Neil Burglunds steps from his blog (since it doesn’t involve the hash). I don’t know if I’m still going this way though. While you say it’s only a one time thing, it appears as though it’s something that will have to be done for every recompile of the assembly. So if someone makes a change and we have to redeploy it we’ll have to go back over this each time. So I’m going to have to document each and every step with particulars to this project for others to be able to follow, and this just seems like an extremely convoluted process to have to document. Especially considering it’s take you what 3 or 4 blogs to cover most of it. I was also planning on using the certificate created from all of this to do encryption which would break fail if we made any changes. I really don’t want to turn trustworthy on, but this is a single back end instance, with only 2 tables and the stored procs in the assembly that are going to access it (no other outward facing interface). But I would like to try and get it “proper” if I can.

      2. Well I’ve tried it every way I can think of.
        0x\ (newline) binaryFormatter.exeOutput – Gives me ‘Incorrect syntax near (first char of binary)’

        0xbinaryFormatter.exeOutput – Give me ‘Incorrect syntax near (first 518 chars of binary)

        Same result just trying to create assembly from similar setup. Have tried creating a login and using Authorization for create assembly, this gives the Assembly not signed turn on trustworthy or sp_add_trustworthy_assembly.

        Have tried ‘Signing’ the dll in visual studio using the project properties Signing tab. Using the ms PVKConvert extract .cer and .pvk files from pfx. Create cert from .cer file. Then trying the various ways to create the assembly, both with and without special login created from cert (then user from login to try the ‘Authorization’ parameter).

        I can’t get past the first step so I’m not sure how it all lines up with your steps so far. This is just a simple C# Library project. Do I have to go through the whole setup of moving everything to a SQL Server DB Project then using all of the cmd line utilities to make the cert, then convert it, then signtool it then binaryformatter to get it all to work?

      3. Hi again. Sorry for the delay in response, but for some reason this comment and your previous one were flagged as spam and I wasn’t notified that anything had even come in to look at.

        From the information provided, I am not sure where the problem is exactly. I do know that I use this method on all my projects and it always works. I think it would help for me to see your actual SQL script, if possible. You can contact me via the “Contact” page. For now, I can say that you should end up with a statement similar to the following (no spaces before or after each \):

        CREATE CERTIFICATE [SQL2017-ClrStrictSecurity-Cert]
        FROM BINARY = 0x\
        ... {several similar lines removed}...


        • When you sign within Visual Studio, it uses a Strong Name Key (.snk file if not password protected), not a Certificate. They are two separate things. So I’m not sure that it will work to extract anything out of that .pfx file.
        • Yes, this setup, whether you pick Solution 1 or Solution 2, is a one-time setup, at most per Project / Assembly, or you can re-use across many Projects, even Solutions. Once the Asymmetric Key or Certificate has been created in [master] and the Login created from it and granted UNSAFE ASSEMBLY, you never need to do that again no matter how many times you recompile. I have been using the same Asymmetric Key for SQL# for something like 8 years now, and have been adding Projects / Assemblies to that Solution throughout that time. The two solutions that I documented here produce a script that is all-inclusive so that it can be deployed on a new system that never had the Cert or Key at any time so that one does not need to look around for an extra script if a new server is being deployed to. But those statements only run the first time.
        • Yes, it took me several posts here to cover everything, but to be fair, that includes a full explanation of all of the various factors and nuances at play here, plus two fully working solutions that integrate into Visual Studio / SSDT, which is only convoluted due to Microsoft not providing any means to handle this stuff (i.e. proper signature-based security) since SQLCLR was introduced in SQL Server 2005.
        • You can try the trusted assemblies mess if you like, but it will still be convoluted as there is no built-in support for it either. So, you still need to adapt the VS build process (nothing gained there) and then you still have the drawbacks that I have documented in various posts here.
        • The AUTHORIZATION user of the Assembly does not matter, at least not for the purposes of getting it loaded. It mainly allows for separation of AppDomains.

        Please, send me a note via the Contact page so that we can find a way for me to see exactly what you are doing as I am sure it is just one little thing that is off that is not obvious.

  3. So I’m fairly late to this, but we are in the process of upgrading SQL 2014 to 2019 and are running into this security issue. However, our issue involves an Azure pipeline that is deploying a SQL DACPAC to an on-prem server using a sqlpackage command. That sqlpackage call generates the “clr strict security” error. Does the fix involve somehow signing the actual dacpac file rather than the database DLL? I’ve tried your solution and signed the DLL, but the error persists. Any thoughts on this scenario?

    Many thanks for any insight you might have.

    1. Hello Joel. You know, I’m not sure if a DACPAC will work with this because it’s doing something different than publishing a SQL project. I don’t think it can have a pre-deploy script. How are you creating the DACPAC file? You might need to execute the first half of my proposed release script as a one-time (or each time if you need to as it is idempotent / re-runnable) pre-release script. Meaning, you just need to get the asymmetric key loaded into [master], create the login, and grant the UNSAFE ASSEMBLY permission once, and then as long as you sign the assembly (or assemblies) with the same key every time, they will always load as the permission was set up that first time. Remember, the login being created by the asymmetric key is the public key of that strong-name-key key/value pair (that you sign the assemblies with). So, as long as that login exists (and has that permission), then any assembly signed with the key that was used to also create that login will be allowed to load without enabling TRUSTWORTHY. Does that all make sense?

Leave a Reply