Sql-server – Assembly deployment with permission UNSAFE or EXTERNAL_ACCESS using asymmetric key

deploymentSecuritysql serversql-clrsql-server-2012

When we are trying to deploy an assembly with PERMISSION_SET=UNSAFE or EXTERNAL_ACCESS, we are getting following error message by SQL Server:

 CREATE ASSEMBLY for assembly 'System.ServiceModel.Internals' failed because 
 assembly 'System.ServiceModel.Internals' 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.**

We are not setting database trustworthy=ON, instead we are creating asymmetric key using ALGORITHM = RSA_2048. Following is the sample code:

USE master; 
GO  

IF NOT EXISTS (SELECT 1 FROM sys.asymmetric_keys WHERE [name]='SampleClrKey')
BEGIN
      CREATE ASYMMETRIC KEY [SampleClrKey] WITH ALGORITHM = RSA_2048
      CREATE LOGIN [SampleClrLogin] FROM ASYMMETRIC KEY [SampleClrKey];  
      GRANT UNSAFE ASSEMBLY TO [SampleClrLogin];
 END
GO

But this error is not getting resolved. I even provided sysadmin access to SampleClrLogin login for my database and master database, but it did not work. Can you guys please provide some insight to solve this issue.

Best Answer

The problem is that you are creating a random new Asymmetric Key in SQL Server, which does nothing to associate the Login to the Assembly, which is what the Asymmetric Key (or Certificate) is used for.

You need to first sign the Assembly prior to loading it into SQL Server. Whether it is signed with a strong name key (which equates to an Asymmetric Key within SQL Server) or a Certificate, you then create that same Asymmetric Key or Certificate (only the public key is required) in master and then create the signature-based Login from that.

For examples on how to accomplish this (and in a way that can be automated via Visual Studio / SSDT), please see the series I am writing on working with SQLCLR (on SQL Server Central): Stairway to SQLCLR (Levels 7 and 8 in particular).

If you are working with SQL Server 2017 or newer, then there is an additional complication to account for which is not part of the method described in the SQL Server central articles. For that, please see the two options for the revised approach in my posts:

P.S. Thank you for spending the extra few minutes to not enable TRUSTWORTHY :-)

CLARIFICATION

Since you are trying to load an unsupported .NET Framework library, you won't be compiling that yourself so the steps I outlined above will not work here (though they are still relevant for someone simply misunderstanding and trying to create the Asymmetric Key initially within SQL Server as shown in the question).

But, given that:

  1. You are attempting to load System.ServiceModel.Internals and not System.ServiceModel, and
  2. You are not getting the error about a particular library being "malformed or not a pure .NET assembly"

then you can accomplish this (while keeping TRUSTWORTHY OFF!) by doing the following:

USE [master];
CREATE CERTIFICATE [UnsupportedFrameworkLibraryPermissions]
  FROM EXECUTABLE FILE =
  'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.ServiceModel.Internals.dll';

CREATE LOGIN [UnsupportedFrameworkLibraryPermissions]
  FROM CERTIFICATE [UnsupportedFrameworkLibraryPermissions];

GRANT UNSAFE ASSEMBLY TO [UnsupportedFrameworkLibraryPermissions];

And then:

USE [{your_DB_name_here}];

CREATE ASSEMBLY [System.ServiceModel.Internals]
  FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.ServiceModel.Internals.dll'
  WITH PERMISSION_SET = UNSAFE;

Just keep in mind that this technique does not work with System.ServiceModel.dll. That library went from being pure MSIL pre-.NET 4.0 to mixed in .NET 4.0, and only pure MSIL libraries can be loaded into SQL Server. Using ServiceModel worked in SQL Server 2005, 2008, and 2008 R2 because those versions use CLR 2.0 which is tied to .NET Framework versions 2.0, 3.0, and 3.5 in which ServiceModel was pure-MSIL.

But in general, David Browne is correct in that you should probably be using HttpWebRequest instead (depending on what you need System.ServiceModel.Internals for).

Related Question