Avoiding TRUSTWORTHY ON and PERMISSION_SET = UNSAFE using System.Net.Http

Question

Trying to create a stored procedure from a DLL that I built to use with SQL using CLR Integration. I think I need a signed version of System.Net.Http and explain why below. Any advice or tips would be appreciated.

The solution works 100% if I use the command

ALTER DATABASE test2 SET TRUSTWORTHY ON

Then I Create the assembly using the following commands

CREATE ASSEMBLY [System.Net.Http]
AUTHORIZATION dbo
FROM 'C:\Windows\Microsoft.NET\assembly\GAC_MSIL\System.Net.Http\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Net.Http.dll'
WITH PERMISSION_SET = UNSAFE;
GO 

CREATE ASSEMBLY [CLRTest01]
AUTHORIZATION dbo
FROM 'C:\Windows\CLRTest01.dll'
WITH PERMISSION_SET = UNSAFE;
GO 

As I choose to trust in default settings, I would like to be able to do this without using Permission level 'UNSAFE' and instead use 'SAFE'. However when I try to do that for CLRTest01 which is reliant on System.Net.Http, I run into the problem of my System.Net.Http being unsigned or wrong format it seems.

CREATE ASSEMBLY [System.Net.Http]
AUTHORIZATION dbo
FROM 'C:\Windows\System.Net.Http.dll'
WITH PERMISSION_SET = SAFE;
GO 

Error when using v4.0_2.0.0.0__b03f5f7f11d50a3a - Assembly 'System.Net.Http' could not be installed because existing policy would keep it from being used.

Error when using v4.0_4.0.0.0__b03f5f7f11d50a3a - CREATE ASSEMBLY failed because type 'System.Net.Http.HttpContent' in safe assembly 'System.Net.Http' has a static field 'EncodingsWithBom'. Attributes of static fields in safe assemblies must be marked readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language.

Which leads me to believe I need a signed version of System.Net.Http.

Jerky

What version of SQL Server are you using? Is it possible to use HttpWebRequest instead? That is at least already part of SQL Server's CLR host. Also, you probably should be added the assembly from its original location, not directly from the GAC. – Pinhole

Answer 1

All .NET Framework libraries are already signed. The issue is that you need to create an Asymmetric Key or Certificate in [master] from the Public Key of either the Strong Name Key (SNK) or Certificate (CER) that was used to sign that Assembly (and often an Assembly has been both strongly named and signed with a certificate).

You can do the following to avoid TRUSTWORTHY (which is absolutely the right thing to do):

USE [master];

CREATE CERTIFICATE [MS.NETcer]
FROM EXECUTABLE FILE =
   'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Net.Http.dll';

CREATE LOGIN [MS.NETcer] FROM CERTIFICATE [MS.NETcer];

GRANT UNSAFE ASSEMBLY TO [MS.NETcer];

Then you can do this:

USE [SomeDatabase];

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

Beyond that, you will not be able to use PERMISSION_SET = SAFE with System.Net.Http due to the error message that you posted:

type 'System.Net.Http.HttpContent' in safe assembly 'System.Net.Http' has a static field 'EncodingsWithBom'. Attributes of static fields in safe assemblies must be marked readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language.

since you cannot mark that static field as readonly and recompile, you are stuck with UNSAFE.

For more information on working with SQLCLR in general (including instructions on setting Visual Studio up to handle the certificate signing, and even having that work in SQL Server 2017, which is more restrictive than prior versions):

SQLCLR.org

Pinhole

Thanks this worked for getting System.Net.Http installed. Now to get my custom DLL to work that depends on it. – Jerky

@Jerky The custom DLL needs to be signed with a Certificate, load that Certificate's public key (.cer file) into the master DB, create a Login from that Cert, and grant that Login the UNSAFE ASSEMBLY permission. Please visit SQLCLR.org for links to how to do that, including automating it in Visual Studio (instructions can be found on my blog, linked at that site, or here: SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1 or Part 3). – Pinhole