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:

  <PropertyGroup>
    <BuildDependsOn>
      BuildOnlySettings;
      BeforeBuild;
      PrepareForBuild;
      PreBuildEvent;
      ResolveReferences;
      ResolveArtifactReferences;
      GenerateSqlTargetFrameworkMoniker;
      ResolveKeySource;
      CoreCompile;
      GenerateSerializationAssemblies;
      SqlBuild;
      GetTargetPath;
      PrepareForRun;
      SqlPrepareForRun;
      IncrementalClean;
      PostBuildEvent;
      AfterBuild;
    </BuildDependsOn>
  </PropertyGroup>

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”,

  <PropertyGroup>
    <SqlBuildDependsOn>
      _SetupSqlBuildInputs;
      _SetupSqlBuildOutputs;
    </SqlBuildDependsOn>
  </PropertyGroup>

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 ^
   SQL2017-ClrStrictSecurity2-Cert.cer

   (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:

   <PropertyGroup>
     <SqlBuildDependsOn>
       BeforeSqlBuild;
       $(SqlBuildDependsOn);
     </SqlBuildDependsOn>
   </PropertyGroup>
   <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 ^
   $(ProjectDir)obj\$(Configuration)\$(TargetName).dll"/>
   </Target>
   

   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 ¹ / 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=0.0.0.0, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

System.IO.FileLoadException:

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.

Conclusion

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.