Asymmetric Key not matching Enhanced Strong Name

Question

So I have scoured much of the internet for answers. I have successfully created a template for deploying the CLR Assemblies to multiple (over 100) SQL Server instances and servers using simple strong names and certificates in a programmatic way...without even requiring the use of an IDE like VISUAL STUDIO except for syntax and checking assemblies. :)

The only catch seems to be the public key I am signing it with, since the C# Compiler csc.exe uses a SHA-1 hash to sign assemblies. So the only workarounds are either using Enhanced Strong Naming or MSBuild (not really an option here).

Enhanced Strong Naming is a simple enough process:

• Create the Strong Name Key (snk) file:

  sn.exe -k [RSA_Length] CLR_IdentityKey.snk

• Publish the public key and hash this with SHA-2

  sn.exe -p CLR_IdentityKey.snk CLR_PubKey.snk sha256

• Delay Sign your assembly with the public key (the source of csc.exe dictates the .NetFramework compatibility of your assembly)

  csc.exe /target:library /keyfile:CLR_PubKey.snk /out:CLR_Assembly.dll "YourCSFiles.cs" "YourCSFiles2.cs" "YourCSFiles3.cs" /DelaySign+

• And then Resign the Assembly with the original key with the StrongName tool.

  sn.exe -Ra CLR_Assembly.dll CLR_IdentityKey.snk

The primary reason for wanting to sign my assemblies with the Enhanced Strong Name method is twofold:

1. Avoid exposing the private key on multiple servers for any length of time.
2. Provide the alternate of publishing the hashed public snk with the option of setting an encrypted password when creating ASYMMETRIC KEY.

Note that although I could accomplish this using both a pfx and a simple snk file, the assembly here is a simple HTTP POST action, and the cost of a certificate is prohibitive given its small use in a intranet environment.

Now Checking the signed assembly, I see the following in the manifest:

Public key (hash algorithm: sha256): 002400000c80000014010000060200...

Public key token is b8ee775aa5bfbc5b

So obviously sn.exe has successfully signed the assembly with the SHA-2 method.

Unfortunately, when I attempt to create my Asymmetric key based on this signed assembly in SQL Server, it seems public key is incorrectly associated.

Msg 10327, Level 14, State 1, Line 14

CREATE ASSEMBLY for assembly 'CLR_Assembly' failed because assembly 'CLR_Assembly' is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

Example Code: Please note that there is a difference between a named instance and the default name in both location and security rights. The folder chosen likely may not have the sufficient rights so you may have to add it.

Powershell:

# The version of csc.exe dictates the version of .NetFramework your assembly is created.
$csc_path="C:\Windows\Microsoft.NET\Framework64\v4.0.30319\csc.exe"
$sn_path="C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.2 Tools\sn.exe"
$CLR_path="C:\Program Files\Microsoft SQL Server\MSSQL13.TESTSQL\MSSQL\JOBS\"

$CLR_Assembly_SNK=$CLR_path + "CLR_SNK.dll"
$CLR_Assembly_DelaySign=$CLR_path + "CLR_DelaySign.dll"

$cs_BackComp="C:\Program Files\Microsoft SQL Server\MSSQL13.TESTSQL\MSSQL\JOBS\BackwardsCompatibility.cs "
$cs_MyMethods="C:\Program Files\Microsoft SQL Server\MSSQL13.TESTSQL\MSSQL\JOBS\MyMethods.cs "
$cs_Main="C:\Program Files\Microsoft SQL Server\MSSQL13.TESTSQL\MSSQL\JOBS\CLR_JSON_Program.cs"

$CLR_IdentityKey=$CLR_path + "CLR_IdentityKey.snk"
$CLR_IdentityKey1=$CLR_path + "CLR_IdentityKey1.snk"
$CLR_PubKey=$CLR_path + "CLR_PubKey.snk"

#using SNK method
& $sn_path -k 2048 $CLR_IdentityKey
& $csc_path /target:library /out:$CLR_Assembly_SNK /keyfile:$CLR_IdentityKey $cs_BackComp $cs_MyMethods $cs_Main
& $sn_path -Tp $CLR_Assembly_SNK

# using delaySign+ method version
& $sn_path -k 2048 $CLR_IdentityKey1
& $sn_path -p $CLR_IdentityKey $CLR_PubKey sha256
& $csc_path /target:library /out:$CLR_Assembly_DelaySign /keyfile:$CLR_PubKey $cs_BackComp $cs_MyMethods $cs_Main /DelaySign+
& $sn_path -Ra $CLR_Assembly_DelaySign $CLR_IdentityKey
& $sn_path -Tp $CLR_Assembly_DelaySign

T-SQL Code:

USE MASTER
GO
CREATE ASYMMETRIC KEY CLR_SNK_KEY
FROM EXECUTABLE FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL13.TESTSQL\MSSQL\JOBS\CLR_SNK.dll'
CREATE LOGIN CLR_SNK
 FROM ASYMMETRIC KEY CLR_SNK_KEY
GRANT UNSAFE ASSEMBLY TO CLR_SNK
GO
CREATE ASSEMBLY CLR_SNK_ASSEMBLY
 AUTHORIZATION [dbo] 
 FROM 'C:\Program Files\Microsoft SQL Server\MSSQL13.TESTSQL\MSSQL\JOBS\CLR_SNK.dll'
 WITH PERMISSION_SET = UNSAFE
GO
DROP LOGIN CLR_SNK
DROP ASYMMETRIC KEY CLR_SNK_KEY
DROP ASSEMBLY CLR_SNK_ASSEMBLY
GO
/*DelaySign+ Version*/
 CREATE ASYMMETRIC KEY CLR_Delay_KEY
FROM EXECUTABLE FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL13.TESTSQL\MSSQL\JOBS\CLR_DelaySign.dll'
CREATE LOGIN CLR_Delay
 FROM ASYMMETRIC KEY CLR_Delay_KEY
GRANT UNSAFE ASSEMBLY TO CLR_Delay
GO
-- fails here due to the ASYMMETRIC key not matching the assembly
CREATE ASSEMBLY CLR_Delay_Assembly
 AUTHORIZATION [dbo] 
 FROM 'C:\Program Files\Microsoft SQL Server\MSSQL13.TESTSQL\MSSQL\JOBS\CLR_DelaySign.dll'
 WITH PERMISSION_SET = UNSAFE

My C# assembly uses var postRequest = (HttpWebRequest)WebRequest.Create(uri); which is not a safe method and requires at least EXTERNAL_ACCESS.

However, SQL Server 2017 is dropping support for the CAS security method and is introducing a quick fix sp_configure 'clr strict security` setting. Create future CLRs using UNSAFE. Microsoft (April 19, 2017): CLR String Security

Why is SQL Server unable to find the correct public key when they are supposedly one and the same?

Does anyone know of a working example of getting delayed assemblies in SQL Server?

Answer 1

Why is SQL Server unable to find the correct public key when they are supposedly one and the same?

If you temporarily set your database to TRUSTWORTHY ON so that you can load the Assembly then you should be able to see the issue.

SELECT * FROM sys.asymmetric_keys;
SELECT * FROM sys.assemblies;

After running those you should see that the "thumbprint" column of sys.asymmetric_keys does not match the "publickeytoken" property shown in the "clr_name" column of sys.assemblies. This is most likely due to using SHA-256 when running sn -p, but you have no choice about that as SHA-1 does not work for Enhanced Strong Naming. The "thumbprint" of the Asymmetric Key is SHA-1 and you have no control over that, hence no way to make them match.

Does anyone know of a working example of getting delayed assemblies in SQL Server?

As luck would have it, the documentation for Enhanced Strong Naming mentions using the AssemblySignatureKeyAttribute attribute to allow some legacy scenarios to work, and this certainly could be one. And in fact, using that attribute does indeed allow Enhanced Strong Naming to work.

Steps to get Enhanced Strong Naming working with SQLCLR (tested on SQL Server 2016 SP1):

1. Create the Identity and Signature key pairs:

   sn -k 2048 IdentityKey.snk
   sn -k 2048 SignatureKey.snk
   

2. Extract the public key from both key pairs:

   sn -p IdentityKey.snk IdentityPubKey.snk
   sn -p SignatureKey.snk SignaturePubKey.snk sha256
   

   Note that the Identity key is not using sha256, hence using the default sha1, while the Signature key is using sha256.

3. Generate the publicKey and counterSignature values needed for the AssemblySignatureKey attribute:

   sn -a IdentityPubKey.snk IdentityKey.snk SignaturePubKey.snk
   

4. Copy two values generated in the prior step into one of the source files (i.e. typically AssemblyInfo.cs):

   [assembly:System.Reflection.AssemblySignatureKey(
   "public" +
   "key" +
   "value",
   "counter" +
   "Signature" +
   "value"
   )]
   

5. Compile the Assembly with delayed signing:

   csc /target:library /out:MyAssembly.dll SqlStoredProcedure.cs
         /keyfile:IdentityPubKey.snk /delaySign+
   

6. Re-sign the Assembly:

   sn -Ra MyAssembly.dll SignatureKey.snk
   

7. In SQL Server, create the Asymmetric Key in master from the DLL:

   USE [master];
   
   CREATE ASYMMETRIC KEY [MyAsymKey]
   FROM EXECUTABLE FILE = N'C:\path\to\MyAssembly.dll';
   

8. Create the Login from that Asymmetric Key and grant it either the EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY:

   CREATE LOGIN [MyAsymKeyLogin]
   FROM ASYMMETRIC KEY [MyAsymKey];
   
   GRANT UNSAFE ASSEMBLY TO [MyAsymKeyLogin];
   

   You only need to grant one of them as UNSAFE implies EXTERNAL ACCESS, but as of SQL Server 2017, if you are using the default configuration of clr strict being enabled, then this needs to be UNSAFE ASSEMBLY.

9. Change to the target Database and create the Assembly:

   USE [Test];
   ALTER DATABASE CURRENT SET TRUSTWORTHY OFF; -- Just to be sure!
   
   CREATE ASSEMBLY [MyAssembly]
   FROM N'C:\path\to\MyAssembly.dll'
   WITH PERMISSION_SET = UNSAFE;
   

---

P.S. Your other concerns are mostly non-issues:

1. Not sure how you think that the private key could be exposed, but it is not part of the ASYMMETRIC KEY (since it is being created from an assembly) so there is no potential for that.
2. Setting a password via CREATE ASYMMETRIC KEY is not an option since the private key is not there (since it is being created from an assembly).
3. If you prefer to use a Certificate there is no cost as a self-signed Certificate (via MAKECERT or PowerShell) is fine, especially for this being a small, internal project. A benefit of going with a Certificate is that you can create it from hex bytes instead of a file or Assembly, and since you can also create the Assembly from hex bytes, your install script can be completely portable as it won't have any file system references :-).