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

Welcome back, everyone. In the previous post in this series, I explained how to work within the new SQLCLR security restriction in SQL Server 2017 (i.e. that all Assemblies need to be signed and have a corresponding Login that has been granted the UNSAFE ASSEMBLY permission). That approach is 22 steps, but they are all a one-time setup, and it fits nicely within SQL Server Data Tools (SSDT), or can work just as well with custom build / deploy processes.

Even though that technique works well, it is still a bunch of steps which might be a bit challenging for some, especially those who are just getting into SQLCLR. Of course, the more complicated a system is, the less likely people will be to implement it, or be successful in implementing it. So, with Solution 1 being the worst-case scenario, it’s time to see if there is an easier method. Let’s look at what we know:

  1. 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
  2. Asymmetric Keys:
    1. are what Visual Studio and MSBuild use to do code signing
    2. cannot be created from a VARBINARY literal
  3. Certificates:
    1. can be created from a VARBINARY literal
    2. are not what Visual Studio and MSBuild use to do code signing
  4. When building a SQL Server Database Project, the compiled DLL / Assembly is converted into a string form of the hex bytes (similar to what Binary Formatter does, but not as nice as it does not split large Assemblies across multiple lines) and placed into the “Create” and/or incremental publish script
  5. The build process is:
    1. highly customizable / flexible, but
    2. not customizable within Visual Studio (at least not on the level that we need), and
    3. not the easiest to customize, especially when dealing with an already non-standard build process, customized by SSDT as a function of the Project being a “SQL Server Database Project”

The takeaways here are:

  1. Using certificates instead of strong name keys (Asymmetric Keys in SQL Server) would reduce several steps since Certificates (in SQL Server) can be created from a VARBINARY literal
  2. In order to use a certificate instead of a strong name key / Asymmetric Key, we would need to sign the DLL after it has been compiled (obviously) but before it gets placed, in string form, into the “Create” script and/or incremental publish script.

Solution Description

The trick this time is to be sneaky. By sneaky I mean that we will modify the build process to sign the DLL / Assembly before any publishing-related tasks do anything with it. We just need to make a small change.

So, how do we modify the build process? First we take a look at the following documentation: How to: Extend the Visual Studio Build Process. In the top section, “Overriding Predefined Targets”, it shows what to add to the project file (for a “SQL Server Database Project”, this would be the .sqlproj file) to override one of the predefined Targets in the Microsoft.Common.targets file. There are a few predefined Targets that look like they might work for this: “AfterCompile”, “AfterBuild”, and “BeforePublish”. It turns out that “AfterBuild” fires after the SQL scripts have been generated, so that won’t help.

I tested both “AfterCompile” and “BeforePublish” and for some reason neither one seemed to fire. In looking more closely at the build output (my “MSBuild project build output verbosity” is set to “Detailed”), I noticed that Microsoft.Common.targets was not the main targets file. Instead, it was using C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v14.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets. Looking in that file, I found the following:


That list is the main “Build” workflow. Notice how it doesn’t have either “AfterCompile” or “BeforePublish”. That would have been too easy. Fortunately, working around the missing events isn’t much work at all. We just need to look at the next section on that documentation page, “Overriding “DependsOn” Properties”. But, overriding that long list requires copying it into the .sqlproj file so that we can add a custom event just before the “SqlBuild” target. Given that these .targets files can change whenever there is an update to SSDT, it would be less risky to override a smaller, less critical list. Looking through that .targets file more I found “SqlBuildDependsOn”,


which only has two items in it, and we don’t need to inject something between those two items, so we can add a custom step to the beginning of that group and then import the group as it is initially defined. This is far less likely to break in a future SSDT update.

The final piece of this puzzle is what to place into our custom target / event. For that we need an Exec Task. In that task we will run SIGNTOOL.EXE to sign the DLL with the certificate. We will use MSBuild variables so that the paths can be dynamically filled in by MSBuild at run-time.

Solution Steps

The following one-time setup steps are done in a Command Prompt window, and are required for the subsequent two sets of steps. The ^ character on the right side of all but the last line in each step is the DOS continuation character which allows a single command-line to span multiple actual lines.

  1. Create the certificate:
    MAKECERT -r -pe -n "CN=SQLCLR Cert,O=SqlQuantumLeap.com,C=US" ^
    -e "12/31/2099" -sv SQL2017-ClrStrictSecurity2-Cert.pvk ^

    (password = “blah”, but you will want to use a stronger password)

  2. Merge the .cer and .pvk files into a .pfx file for signing:

    PVK2PFX -pvk SQL2017-ClrStrictSecurity2-Cert.pvk -pi blah ^
    -spc SQL2017-ClrStrictSecurity2-Cert.cer ^
    -pfx SQL2017-ClrStrictSecurity2-Cert.pfx
  3. Convert the .cer file (i.e. the public key) to be used as a VABINARY literal:
    BinaryFormatter.exe SQL2017-ClrStrictSecurity2-Cert.cer ^
    SQL2017-ClrStrictSecurity2-Cert.sql 40

    BinaryFormatter.exe is a small command-line utility that I wrote to convert binary files into a hex bytes string. It is available on GitHub.

The following steps, executed in master, 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 Project. Please note that step 1 requires the output from step 3 above.

  1. CREATE CERTIFICATE [SQL2017-ClrStrictSecurity2-Cert]
    FROM BINARY = 0x{contents_of_ClrStrictSecurity2-Cert.sql};
  2. CREATE LOGIN [SQL2017-ClrStrictSecurity2-Login]
    FROM CERTIFICATE [SQL2017-ClrStrictSecurity2-Cert];
  3. GRANT UNSAFE ASSEMBLY TO [SQL2017-ClrStrictSecurity2-Login];

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 certificate that was loaded into SQL Server via the steps detailed above.

  1. Update .sqlproj file (in Project directory / $(ProjectDir)) by placing the following at the end, just before the closing Project tag:
    <Target Name="BeforeSqlBuild">
      <Exec Command="&quot;C:\Program Files (x86)\Windows Kits\8.1\bin\x64\signtool.EXE&quot; ^
    sign /v /p blah ^
    /f $(SolutionDir)SQL\SQL2017-ClrStrictSecurity2-Cert.pfx ^

    The “Command” definition above assumes that you put the .pfx file in the main project directory, which is where the .pfx is placed by default when adding a Strong Name Key via Visual Studio (done in the “Signing…” tab or button in Project Properties).
    While the DLL is in both obj and bin folders, we need to sign the one in the obj folder as that is where it is initially created, and that is the one that SSDT will use to create the SQL script(s).

    Please note that the path to signtool.exe might be different on your system. Please verify the path, or you might not even need the full path. If you open a Command Prompt, go to “C:\“, and run “signtool“, and it works, then you don’t need to specify the path, but can if you want to.

  2. (optional) set up a “PreDeploy” SQL script consisting of the T-SQL commands in the previous set of steps

Please note that:

  • Just like Solution 1:
    • This solution works well with SSDT, yet works just as well without it (i.e. custom deployments)
    • Binary Formatter is required.
    • Certificate needs to use SHA1 hash (which is the default behavior) as that is what SQL Server looks for
    • Only the certificate’s public key is imported into SQL Server; the private key is not imported as there is no need for it since we are not signing anything with it inside of SQL Server.
  • Unlike Solution 1, this solution:
    • does not use a Strong Name Key 1 / Asymmetric Key (i.e. does not use native Visual Studio signing); uses only a certificate.
    • does not require an extra Project / Assembly to hold just the Strong Name Key / Asymmetric Key
    • relies upon a minor override to the default behavior / workflow of MSBuild / SSDT
  • What is shown here is not a fully-automated solution. The steps outlined above 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.
  • This technique can be used for EXTERNAL_ACCESS and UNSAFE Assemblies in SQL Server versions 2012, 2014, and 2016 as an easier alternative to the technique I described in Stairway to SQLCLR Level 7: Development and Security

Demo Script

If you want to see the fully working example of this technique, it is available on PasteBin.

The demo SQLCLR code consists of only the following simple C# code:

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class UserDefinedFunctions
    [SqlFunction(IsDeterministic = true)]
    public static SqlString String_PadLeft(SqlString InputString,
        SqlChars PadChar, SqlInt32 PadAmount)
        return new SqlString(InputString.Value.PadLeft(
                                 PadAmount.Value, PadChar[0]

The demo script follows the 7 required steps to load the Assembly and then executes a few queries to show that it works. Then, it removes the UNSAFE ASSEMBLY permission from the signature-based Login and executes a query using the SQLCLR UDF. This time, the following error is returned:

Msg 10314, Level 16, State 11, Server osboxes, Line 18
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65537. The server may be running out of resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly ‘sql2017_clrstrictsecurity2, Version=, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
at: System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence ass

Getting this error after removing the permission proves that the signing did work, and that the certificate did link the Assembly to the signature-based Login.

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 from Certificate, and grant it the UNSAFE ASSEMBLY permission
  4. USE [other_DB];
  5. Create Assembly, signed with the same Certificate used to create the Login
  6. Create T-SQL wrapper objects (i.e. CREATE STORED PROCEDURE … WITH EXTERNAL NAME AS …, etc).

Additional thoughts

This solution is easier than Solution 1:

  1. 8 steps instead of 22!
  2. No extra Project

However, a very small amount of risk was added by overriding the default MSBuild workflow for SSDT. This risk can be eliminated if Microsoft provides a pre-defined Target for the appropriate event. Please upvote my suggestion to have this happen: Add MSBuild predefined Targets for “BeforeSqlBuild” and “BeforePublish” to SSDT SQL Server Data Projects.

ALSO: Even though we did not sign the assembly with a Strong Name Key, it is still probably a good idea to do that.


As we can see here, there is a second, and much simpler, technique for publishing SQLCLR code that:

  1. works with Visual Studio / SSDT
  2. can be fully encapsulated in a SQL script
  3. never enables TRUSTWORTHY
  4. never disables clr strict security

In the next post we will start exploring the inexcusable travesty that is Trusted Assemblies.

  1. There are some minor effects of not signing the Assembly with a strong name key. When looking at the Assembly properties in Object Explorer, “Strong Name” will show “False”. Also, when running the following query:

    SELECT ASSEMBLYPROPERTY(N'sql2017_clrstrictsecurity2',

    publickeytoken will be “null” (which is expected), and version will always be “”, even if a version number has been provided (which is a bug: “version” incorrectly showing as in sys.assemblies and ASSEMBLYPROPERTY(name, ‘SimpleName’) for unsigned SQLCLR Assemblies 

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

  1. Very good solution! I took out the path to signtool.exe, because it’s in my %PATH% anyway, and it works perfectly.
    There is a practical problem though – DBAs won’t want to trust a developers unsafe code, so they’re unlikely to want to grant unsafe assembly permissions to it.
    Or have I missed something?.. is there such a thing as a “grant safe assembly” permission?

    1. Thanks, Rich! And thanks for mentioning the PATH issue. I updated the post to include a more current path as well as a note about verifying it, and if it is even needed.

      Granting the UNSAFE ASSEMBLY permission doesn’t mean that the code will be UNSAFE, just that it is allowed for an Assembly to be marked as such, or as EXTERNAL_ACCESS. The permission is just a requirement of the new “CLR strict security” setting, but does not imply that something other than using RegEx in a SAFE Assembly will be done.

      No, there is no permission for SAFE Assemblies. Prior to SQL Server 2017, SAFE Assemblies did not require being signed or having a login with either UNSAFE ASSEMBLY or EXTERNAL ACCESS ASSEMBLY permission. But now even the EXTERNAL ACCESS ASSEMBLY permission is not good enough.

      If a DBA is concerned, they should either not run any CREATE ASSEMBLY or ALTER ASSEMBLY statements that set the PERMISSION_SET to UNSAFE, or they can make sure that whoever is executing the CREATE / ALTER statements for Assemblies has not been granted UNSAFE ASSEMBLY, though that is hard to restrict from a sysadmin account if that is being used for automated deployments.

  2. I just got MSBuild to work with this method too, so my Jenkins build works with the certificate-signed assembly – thanks again for the solution.
    I hadn’t appreciated that the user doing the deployment would also need UNSAFE ASSEMBLY perms in order to create an unsafe assembly, even though the certificate-login at run-time has those permissions. So my worries about convincing a DBA to import the certificate, create the user and grant it unsafe assembly perms are (hopefully) unfounded.


    ps. If only MS would put RegEx into SQL server, a lot of us could stop bothering with CLR in the first place!

    1. Excellent! Glad you got it working in MSBuild, and you are quite welcome.

      Regarding the “p.s.”: I don’t think built-in RegEx is really in everyone’s best interest. It will be a mediocre implementation that will just tie into .NET (just like FORMAT, TRY_PARSE, and maybe even CONCAT, etc). There is no way it would be as feature-complete as what is already available in SQL#, and any performance gain would be marginal at best. The only benefit is that it would be available in Azure SQL Database which currently does not support SQLCLR (well, not anymore; it did for about 18 months ;). But noone really needs to “bother” with SQLCLR since installing SQL# requires no knowledge of it 😺 .

  3. I’d be quite happy with a simple wrapper around the .net regex functions, straight out of the box, no 3rd party assemblies, no certificates to maintain across environments, and running on Azure. I’m pretty sure it would be in pretty much everyone’s best interests – with the possible exception of someone selling a CLR library of course :) Sorry about that – I hadn’t noticed the link to SQL# before.
    All the best for SQL# and for this blog. It’s been a great help.


    1. Valid points. Thanks for that feedback. For what it’s worth, the RegEx functions in SQL# are almost all in the Free version, so this isn’t so much about selling a product as it is liking to have all options available, such as the RegExOptions to tailor how it behaves, etc. But yes, I do fully understand that quite enough people never use those options and wouldn’t miss them.

      Regardless, glad you found this stuff helpful.

  4. Thank you for providing this solution, I’m thinking about how to implement it in my project. One thing I wondered about is that it seems like both the .pfx and the associated password are included in the source for the project – does that mean having access to the source represents a vector for the SQL instance (since it is trusting code signed by that pfx)?

    1. Hi Eric. Excellent question. I suppose yes, if someone did have access to both, then they could sign whatever they wanted. But, if there are cases where this is a concern, I believe there are ways to mitigate the risk by either A) adding the certificate to a local certificate store in which case there is no PFX file or password (and there are various options here), and B) doing the signing on a dedicated build server (this would require a move involved deployment process, but is doable). I am currently playing around with option A and will write up something soon showing how to configure / use it to provide security such that nobody would be able to transport that certificate anywhere else without the password, and the password wouldn’t be stored locally (or anywhere in the project).

  5. It seems this solution is already outdated, or at least not easy to follow at this point. MAKECERT and PVK2PFX don’t seem to be present on my machine, nor did a little googling reveal downloads available from MS.

    I really appreciate your content, but the truth is MS is creating a giant security problem by making all of their tooling work together so incredibly poorly. Visual Studio won’t sign the assembly in a way that makes it semi-useable with their own database server. CERTMGR won’t import the PFX. I created a certificate via PowerShell “New-SelfSignedCertificate”, exported it once with private key to a PFX and once with only public key to a .CER, then imported the .CER to trusted roots on local machine. But signtool says the PFX doesn’t “meet all the given requirements” (though I had specified none whatsoever).

    At the bloody conferences it’s always just to tick the checkbox. But it never bloody works in real life. I am FED UP with Microsoft and their utter inability to deliver a decent user experience for anyone trying to USE moderately advanced but utterly necessary features in their products!

    1. Hello, and thanks for providing this feedback. Sorry that I didn’t provide more detail on getting those additional tools. I guess I had them installed for a long time via Visual Studio (without knowing that they came from that install) and just assumed that they came with Windows. Sorry about that. All 3 tools (including SignTool) are available in the Windows SDK, available at: https://developer.microsoft.com/en-us/windows/downloads/windows-10-sdk/ . On the screen where it asks you to select which items to install or download, you only need to select the following:

      1. “Windows SDK Signing Tools for Desktop Apps”
      2. “Windows SDK for UWP Managed Apps”
      3. “Windows SDK for Desktop C++ x86 Apps” (which automatically selects “Windows SDK for UWP C++ Apps”).

      If you do the download, you can even get away with installing only a subset of that subset by installing only the following MSIs:

      1. “Windows SDK for Windows Store Apps Tools-x86_en-us”
      2. “Windows SDK Signing Tools-x86_en-us”
      3. “Windows SDK Desktop Tools x86-x86_en-us”.

      I am working on a new post that focuses on the certificate / signing utilities and commands and will include this info and more.

      Yes, the lack of an end-to-end solution for proper security is quite frustrating. The only way to make it work via a checkbox is to enable TRUSTWORTHY, and that’s just not acceptable. Of course, most of the problems related to deploying SQLCLR assemblies with proper security would be solved by simply allowing asymmetric keys to be created inline using a VARBINARY literal, so please vote for the following suggestion: Allow Asymmetric Key to be created from binary hex bytes string just like CREATE CERTIFICATE.

    1. Hi Tim. You are quite welcome. And yes, Microsoft took an already unnecessarily complicated process, and for a dubious reason (and without much forethought or even reaching out to anyone who understands SQLCLR), added yet another layer of complexity. Truly mind-boggling.

      Glad you like SQL# 😺. Your library also looks quite interesting, and good job on automating the build process!

  6. i’ve got the workflow completed finally. It creates two scripts one for .net 3.5 (2012 and prior), and 4.5. Both signed. v.1.0.5 release has an install.bat that installs the proper version for your server. Would never have gotten there though if not for this post.

    1. Hello again. Awesome! I’m glad you were able to complete that successfully, and I’m glad to have been able to help :). Just FYI: CLR version 2.0 (for Framework versions 2.0, 3.0, and 3.5) is SQL Server 2005, 2008, and 2008 R2. Starting with SQL Server 2012, SQLCLR is linked to CLR version 4.0 (for Framework versions 4.x). And, due to backwards compatibility, code will still work if in an environment with a higher Framework version than it was compiled for. Meaning, if you aren’t using any Framework 4.5 specific features, you could get away with a single build targeting version 3.5, though it might not work in SQL Server 2005, if that’s a concern. Please see the chart in the following article of mine: Stairway to SQLCLR Level 5: Development (Using .NET within SQL Server). Take care, Solomon…

      1. Thanks I will flip my script so that the cutover is 2012. I had forgotten when that was. I am using 4.5 specific features. To keep a bad regex from killing a server, I used the timeout functionality. That way the library can be safe for all 2012 versions and higher. Runway regex makes the thread pool manager go crazy as it tries to quantum punish the regex thread. :)

  7. I’m floundering with this clr strict issue, but it isn’t one of our assemblies. It is during the upgrade-database process for Master Data Services in an azure sql server. Are we expected to somehow sign their own assembly? For us the error references Microsoft.MasterDataServices.DataQuality. Is that even possible?

    1. Hi Thom. Technically speaking, anyone can sign any assembly since assemblies can have multiple signatures. The main question, however, is: are you on Azure SQL DB or Azure SQL Managed Instance? That’s a very important distinction because Azure SQL DB does not allow for SQLCLR (Azure SQL Managed Instance does). Now, since this is a Microsoft-provided assembly, they might possibly make an exception and allow it, but you would need to contact them about that. If this is Azure SQL Managed Instance, then I think we can get it to work.

Leave a Reply