是否可以在 SQL Server 2014 中为 .NET 框架程序集创建非对称密钥? [英] Is it possible to create an asymmetric key for a .NET framework assembly in SQL Server 2014?

问题描述

我正在 Visual Studio 中开发一个 SQL Server 数据库项目，它实际上是一个用户定义的函数.在这个项目中，我包含了 Json.NET 作为参考(使用 NuGet).

我通过首先打开数据库TRUSTWORTHY ON(因为我的项目不安全)然后运行它，设法将我的程序集和 UDF 发布(并使其工作)到我的 SQL Server 实例:

CREATE ASSEMBLY [System.Runtime.Serialization]从'C:WindowsMicrosoft.NETFrameworkv4.0.30319System.Runtime.Serialization.dll'WITH PERMISSION_SET = 不安全；

结果是Json.NET依赖的程序集(如果我不这样做，我会得到一个错误)

与此同时 我读到有多糟糕可以开启可信任，我尝试采用非对称密钥的方式来避免开启它.>

甚至在签署我自己的程序集之前，我知道我必须为 System.Runtime.Serialization 创建一个密钥，因为我的项目依赖于 Json.Net，而后者又依赖于它，但是当我运行它，我明白了:

USE [master];走创建不对称密钥 [SystemRuntimeSerializationKey]授权 [dbo]从可执行文件 = 'C:WindowsMicrosoft.NETFrameworkv4.0.30319System.Runtime.Serialization.dll';走Msg 15468, Level 16, State 5, Line 3生成非对称密钥时出错.

这对我帮助不大.

那么是否有可能为 .NET 框架程序集生成这样的密钥，或者是否存在其他解决方法可以打开 TRUSTWORTHY?

解决方案

不，我从来没有找到实现这一点的方法.用于签署 .NET Framework 程序集的密钥是 Microsoft 的内部/私有密钥.尝试的选项:

• 提取私钥/加载到 SQL Server:不可能，否则密钥不会是私有的".签名/强命名系统是有效的，因为外人不能声称他们签署了代码.

• 添加签名:我尝试使用 sn 实用程序添加一个新签名.无法正常工作，原因如下:

  <块引用>

  重新签署程序集失败 -- 程序集的公钥与签名公钥不匹配.

  但是，即使这确实有效，也可能并不意味着将其加载到 SQL Server 中会有效，因为它与您作为资源添加到项目中的程序集不同.其他 .NET Framework DLL，如果有任何其他依赖项，只会知道原始签名.

• 删除当前签名并添加一个新签名:我尝试使用ILDASM反汇编System.Runtime.Serialization.dll，然后添加一个新的私钥从 sn -k 创建，然后使用 ILASM 重新链接，但在 ILASM 步骤失败(我没有时间进一步调查).

  但就像上面的选项一样，即使这确实有效，您也必须将 System.Runtime.Serialization 的 Json.NET 项目引用更改为这个新的 DLL，重新编译它，然后将您的项目引用更改为新的 DLL，然后重新编译它.但这只会让您能够干净地加载 DLL，如果它们具有需要原始 Microsoft 签名的外部依赖项，则不能保证它们会工作.

本质上，如果您加载的 DLL 在签名方面您无法控制，那么唯一的希望是使用 ILDASM 反编译并使用 ILASM 重新编译>，指定一个新的 snk 文件，但如果其他程序集链接到正在重新编译的内容，则该文件将不起作用.当然 .NET Framework DLL 也属于这一类.

简单地说:如果您正在加载不受支持的 .NET Framework DLL，那么您几乎需要设置 TRUSTWORTHY ON.

但是:请记住，即使这确实可以通过非对称密钥或证书加载所有内容，但这并不意味着您不会遇到功能问题.这些库未被批准/验证是有原因的.它们中的代码可以以您意想不到的方式工作，例如将数据存储到静态字段.在 Windows 和控制台应用程序中，这不是问题，因为它是每个应用程序域的一次使用.但是 SQLCLR 使用共享应用程序域，因此多个 SQL Server 会话将共享这些静态变量.可能是 Json.NET 库调用的方法没有使用那些不安全的东西，但我们无从得知，即使我们知道，现在也无能为力:-(.<小时>

我一直在考虑的一件事是跟踪不受支持的 .NET Framework DLL 中调用的方法，并假设它没有做任何不安全的事情，然后将该代码直接复制到项目中.理论上应该可行，只要对 System.Runtime.Serialization 的调用不调用其他不受支持的 DLL 或做不安全"的事情等.但是，我没有时间对此进行测试.

I am developping an SQL Server Database Project in Visual Studio which is in fact a User Defined Function. In this project, I included Json.NET as a reference (using NuGet).

I managed to publish (and make work) my assembly and the UDF to my SQL Server instance by first turning the database TRUSTWORTHY ON (since my project is UNSAFE) and then running this:

CREATE ASSEMBLY [System.Runtime.Serialization] 
FROM 'C:WindowsMicrosoft.NETFrameworkv4.0.30319System.Runtime.Serialization.dll'
WITH PERMISSION_SET = UNSAFE;

Which turns out to be an assembly Json.NET depends on (if I don't do it, I get an error)

Meanwhile I read about how bad turning TRUSTWORTHY ON can be and I tried to go the asymmetric key way to avoid turning it on.

Before even signing my own assembly, I know I will have to create a key for System.Runtime.Serialization since my project depends on Json.Net which in turn depends on it, but when I run it, I get this:

USE [master];
GO
  CREATE ASYMMETRIC KEY [SystemRuntimeSerializationKey]
    AUTHORIZATION [dbo]
    FROM EXECUTABLE FILE = 'C:WindowsMicrosoft.NETFrameworkv4.0.30319System.Runtime.Serialization.dll';
GO

Msg 15468, Level 16, State 5, Line 3
An error occurred during the generation of the asymmetric key.

Which does not help me much.

So is it possible to generate such a key for .NET framework assemblies, or does it exist a workaround other that turning TRUSTWORTHY ON ?

解决方案

No, I have never found a way to accomplish this. The key used to sign the .NET Framework Assembly is internal / private to Microsoft. The options tried:

• Extract Private Key / load into SQL Server: not possible, else the key wouldn't be "private". The signature / strong naming system is effective because outsiders cannot claim that they signed the code.

• Add a signature: I tried adding a new one using the sn utility. Doesn't work due to:

  Failed to re-sign the assembly -- Public key of assembly did not match signing public key.

  But, even if this did work, that might not mean that loading it into SQL Server would work since it is not the same Assembly that you added as a resource to your project. Other .NET Framework DLLs, if there are any other dependencies, would only know of the original signature.

• Remove current signature and add a new one: I tried using ILDASM to disassemble System.Runtime.Serialization.dll, and then add a new private key created from sn -k and then relink using ILASM, but that failed on the ILASM step (and I don't have time to investigate further).

  But just like the option above, even if this did work, you would have to change the Json.NET project reference for System.Runtime.Serialization to be this new DLL, recompile it, then change your project reference to be the new DLL, and then recompile that. But this would only get you the ability to load the DLLs cleanly, it would not guarantee that they would work if they have external dependencies that are expecting the original Microsoft signature.

Essentially, if you are loading DLLs that you don't have control of in terms of the signature, then the only hope is to decompile using ILDASM and recompile using ILASM, specifying a new snk file, but that won't work if other Assemblies are linked to what is being recompiled. And certainly .NET Framework DLLs fall into this category.

Simply put: if you are loading unsupported .NET Framework DLLs, then you pretty much need to set TRUSTWORTHY ON.

BUT: Keep in mind that even if this did work to load everything via an Asymmetric Key or Certificate, that does not mean that you won't run into functional problems. There is a reason why these libraries have not been approved / validated. There is code in them doing things that could work in ways that you are not expecting, such as storing data to static fields. In Windows and Console apps this is not an issue as it is one use per App Domain. But SQLCLR employs a shared App Domain, so multiple SQL Server Sessions will share those static variables. It might be that the methods called by the Json.NET library don't use those unsafe things, but there is no way of knowing, and even if we did know, not much we can do about it now :-(.

---

One thing that I have been contemplating is tracing through the methods called in the unsupported .NET Framework DLL, and assuming that it is not doing anything unsafe, copying that code directly into the project. Theoretically that should work, as long as the calls into System.Runtime.Serialization do not call other unsupported DLLs or do "unsafe" things, etc. But, I haven't had time to test this out.

这篇关于是否可以在 SQL Server 2014 中为 .NET 框架程序集创建非对称密钥?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！