2

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.

  • Yes, I made SampleClrLogin as sysadmin as well as DB Owner to both master database and my database where I am trying to deploy the assembly. But error is still there. – Murali Dhar Darshan Dec 3 '18 at 8:59
  • if you are talking about this: exec sp_changedbowner 'SampleClrLogin', 'true'....then its giving me error: An entity of type database cannot be owned by a role, a group, an approle, or by principals mapped to certificates or asymmetric keys. – Murali Dhar Darshan Dec 3 '18 at 9:46
  • Can you tag the version of SQL Server or put it in the output above? The security boundary had changed in SQL Server 2016 for CLR. – Sean says Remove Sara Chipps Dec 3 '18 at 11:34
  • Its SQL Server 2012 – Murali Dhar Darshan Dec 3 '18 at 11:59
2

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).

  • When I tried creating Asymmetric Key, it gave me following error: Please create a master key in the database or open the master key in the session before performing this operation. So I used following query to create master key CREATE MASTER KEY ENCRYPTION BY PASSWORD='passwd#1' I went through your blogs on SQLCLR, but nowhere mentioned about this scenario in which we need to create master key. Can you please tell what is the relation between master key and creating asymmetric key for sqlclr deployment. – Murali Dhar Darshan Dec 11 '18 at 11:47
  • @MuraliDharDarshan I never said anything about an Asymmetric Key. This approach only works with a Certificate. Please just copy and paste the example code from my answer. It should work with no modifications needed, outside of specifying your DB name. – Solomon Rutzky Dec 11 '18 at 16:24
0

System.ServiceModel.Internals

You can stop right there. WCF cannot be used in SQL CLR. SQL CLR only supports pure MSIL assemblies, and WCF in .NET 4.x references some mixed-mode assemblies.

See Unable to register .NET framework assembly not in the supported list

and https://stackoverflow.com/questions/40349838/sql-server-2016-create-assembly-for-assembly-system-servicemodel-failed

If you are calling a web service from SQL CLR you can use the WebClient API, or the lower-level HttpWebRequest, both of which are implemented in System.dll.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.