I executed the same command on SQL Server 2017 CU12 and got the same 15208 error. I then tried the following:
CREATE ASYMMETRIC KEY [MS.NETsnk] FROM EXECUTABLE FILE =
'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll';
and that succeeded! ("snk" in the name is for "Strong Name Key", since technically this isn't a certificate).
From what I can tell, most of the .NET Framework libraries for CLR 2.0 (i.e. .NET Framework versions 2.0, 3.0, and 3.5) are not signed with a certificate. Most, but not all, of the mscor*.dll files, and only one System.*.dll — System.EnterpriseServices.Thunk.dll — are signed with a certificate. The rest are only using a Strong Name Key / are strongly-named.
Starting with CLR 4.0 (i.e. .NET Framework versions 4.*), it appears that all .NET Framework libraries are signed with a certificate.
Hence, the more precise rule is:
For CLR 4.0 (i.e. SQL Server versions 2012 and newer), use CREATE CERTIFICATE
For CLR 2.0 (i.e. SQL Server versions 2005, 2008, and 2008 R2), use CREATE ASYMMETRIC KEY
. But, if doing that results in a 15208 error ("The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it"), then switch to CREATE CERTIFICATE
.
The reason for this group to start with ASYMMETRIC KEY
is the very low probability of ever needing to use CERTIFICATE
. I tried to import System.EnterpriseServices.Thunk.dll in both SQL Server 2019 (which used CLR 4.0) and SQL Server 2005 (which used CLR 2.0) and it failed in both — msg 6507 in SQL Server 2005, and msg 6544 in SQL Server 2019 — complaining of a "malformed assembly" (meaning it's a mixed-mode / not-pure-MSIL assembly). I also tried importing some of the mscor* libraries in SQL Server 2005 and they failed with the same 6507 error (not surprising that the core libraries would have unmanaged code in them). So, it's quite likely that there's not even a possibility of using CREATE CERTIFICATE
when importing .NET Framework libraries for CLR 2.0.
Now, it appears that System.Messaging has several dependencies:
- system.configuration.install
- system.runtime.serialization.formatters.soap
- system.windows.forms
- system.drawing
- accessibility
- system.directoryservices
Those dependencies get imported automatically because the source for CREATE ASSEMBLY
is a file and not a VARBINARY
literal / hex bytes. While doing this automatic import of dependencies, the operation fails on system.windows.forms claiming that it is not authorized for UNSAFE
, even though we have already created the Asymmetric Key and the other dependencies have imported correctly.
Further investigation reveals that the Strong Name Key used for system.windows.forms is different than what was used for the other libraries. The libraries that import successfully have a Public Key Token of b03f5f7f11d50a3a
while the one that fails has a Public Key Token of b77a5c561934e089
. As the O.P. discovered, creating an Asymmetric Key from system.windows.forms does not work. Creating a certificate from the library also doesn't work, but that's easier to explain: the library isn't signed with a certificate ;-). The issue with creating the Asymmetric Key, however, is probably due to how system.windows.forms is signed. It uses ECMA signing, which places a standard, place-holder value in the library instead of the public key, and this place-holder value is not a valid public key. For more info, please see the following:
Now, I don't think we can get the public key file to create an Asymmetric Key from. It might be somewhere in the file system as it shouldn't be a security issue, being a "public" key and all, but as of right now I don't know where it is.
So, for the moment we should still be able to get around this using the same basic technique that allows pre-existing, unsigned assemblies in databases upgraded to SQL Server 2017 (or newer) from a pre-2017 version, to work: sign the assembly in-place. Of course, to get the assembly into SQL Server we will need to temporarily enable TRUSTWORTHY
, but only for a single command. Then, once the assemblies have been imported, we can:
- create a certificate
- sign the assembly with it
- back up the certificate (both public and private keys) to files (can't get the
VARBINARY
of the public key via CERTENCODED as that was introduced in SQL Server 2012)
- remove the private key from the certificate so that it can't be used to sign anything else
- create the same certificate in
[master]
, but only with the public key (so it can't be used for signing)
- create a login from that certificate
- grant that signature-based login the
UNSAFE ASSEMBLY
permission
Try the following approach based on the steps outlined above. The example code below assumes that the steps to create the Asymmetric Key, associated signature-based Login, and GRANT
on System.Messaging have already been completed.
--CREATE DATABASE [Test];
--ALTER DATABASE [Test] SET RECOVERY SIMPLE;
GO
USE [Test];
ALTER DATABASE [Test] SET TRUSTWORTHY ON;
CREATE ASSEMBLY [System.Messaging]
FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
WITH PERMISSION_SET = UNSAFE;
ALTER DATABASE [Test] SET TRUSTWORTHY OFF;
SELECT * FROM sys.assemblies;
-- System.Messaging: publickeytoken=b03f5f7f11d50a3a
-- System.Windows.Forms: publickeytoken=b77a5c561934e089
CREATE CERTIFICATE [WinForms]
ENCRYPTION BY PASSWORD = 'Use Another Password!'
WITH SUBJECT = 'For ECMA-signed .NET Framework libraries';
ADD SIGNATURE
TO ASSEMBLY::[System.Windows.Forms]
BY CERTIFICATE [WinForms] WITH PASSWORD = 'Use Another Password!';
BACKUP CERTIFICATE [WinForms]
TO FILE = 'C:\TEMP\WinFormsCert.crt'
WITH PRIVATE KEY
(
FILE = 'C:\TEMP\WinFormsCert.pvk',
ENCRYPTION BY PASSWORD = 'Backup Password!',
DECRYPTION BY PASSWORD = 'Use Another Password!'
);
ALTER CERTIFICATE [WinForms] REMOVE PRIVATE KEY;
GO
USE [master];
CREATE CERTIFICATE [WinForms] FROM FILE = 'C:\TEMP\WinFormsCert.crt';
CREATE LOGIN [WinForms] FROM CERTIFICATE [WinForms];
GRANT UNSAFE ASSEMBLY TO [WinForms];
This work-around shouldn't be necessary when working with CLR 4.0 (i.e. SQL Server 2012 and newer) since all of those assemblies are signed with a certificate.
For more info on working with SQLCLR in general, please visit: SQLCLR Info