使用 System.Net.Http 避免 TRUSTWORTHY ON 和 PERMISSION_SET = UNSAFE [英] Avoiding TRUSTWORTHY ON and PERMISSION_SET = UNSAFE using System.Net.Http

问题描述

尝试从我构建的 DLL 中创建一个存储过程，该 DLL 使用 CLR 集成与 SQL 一起使用.我想我需要 System.Net.Http 的签名版本并在下面解释原因.任何建议或提示将不胜感激.

如果我使用命令，该解决方案 100% 有效

ALTER DATABASE test2 SET TRUSTWORTHY ON

然后我使用以下命令创建程序集

创建程序集 [System.Net.Http]授权 dbo从'C:\Windows\Microsoft.NET\assembly\GAC_MSIL\System.Net.Http\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Net.Http.dll'WITH PERMISSION_SET = 不安全；去创建程序集 [CLRTest01]授权 dbo从 'C:\Windows\CLRTest01.dll'WITH PERMISSION_SET = 不安全；去

当我选择信任默认设置时，我希望能够在不使用权限级别不安全"的情况下执行此操作，而是使用安全".但是，当我尝试为依赖 System.Net.Http 的 CLRTest01 执行此操作时，我遇到了 System.Net.Http 未签名或格式错误的问题.

创建程序集 [System.Net.Http]授权 dbo从'C:\Windows\System.Net.Http.dll'WITH PERMISSION_SET = 安全；去

<块引用>

使用 v4.0_2.0.0.0__b03f5f7f11d50a3a 时出错- 无法安装程序集System.Net.Http"，因为现有策略会阻止使用它.

使用 v4.0_4.0.0.0__b03f5f7f11d50a3a 时出错- CREATE ASSEMBLY 失败，因为安全程序集System.Net.Http"中的类型System.Net.Http.HttpContent"具有静态字段EncodingsWithBom".安全程序集中的静态字段的属性在 Visual C# 中必须标记为只读，在 Visual Basic 中必须标记为只读，或者在 Visual C++ 和中间语言中必须标记为 initonly.

这让我相信我需要 System.Net.Http 的签名版本.

解决方案

所有 .NET Framework 库都已签名.问题是您需要根据用于对该程序集进行签名的强名称密钥 (SNK) 或证书 (CER) 的公钥在 [master] 中创建非对称密钥或证书(并且通常程序集被强命名并用证书签名).

您可以执行以下操作以避免可信(这绝对是正确的做法):

USE [master];创建证书 [MS.NETcer]从可执行文件='C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Net.Http.dll';从证书 [MS.NETcer] 创建登录 [MS.NETcer]；授予 [MS.NETcer] 不安全组件；

然后你可以这样做:

USE [SomeDatabase];创建程序集 [System.Net.Http]从'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Net.Http.dll'WITH PERMISSION_SET = 不安全；

除此之外，由于您发布的错误消息，您将无法将 PERMISSION_SET = SAFE 与 System.Net.Http 一起使用:

<块引用>

安全程序集System.Net.Http"中的类型System.Net.Http.HttpContent"具有静态字段EncodingsWithBom".安全程序集中的静态字段的属性在 Visual C# 中必须标记为只读，在 Visual Basic 中必须标记为只读，或者在 Visual C++ 和中间语言中必须标记为 initonly.

由于您无法将该静态字段标记为 readonly 并重新编译，因此您会遇到 UNSAFE.

有关一般使用 SQLCLR 的更多信息(包括有关设置 Visual Studio 以处理证书签名的说明，甚至在 SQL Server 2017 中使用此功能的说明，这比以前的版本更具限制性):

SQLCLR.org

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.

解决方案

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

这篇关于使用 System.Net.Http 避免 TRUSTWORTHY ON 和 PERMISSION_SET = UNSAFE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！