是否可以在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）。

我设法将程序集和UDF发布（并使工作）到SQL Server。首先打开数据库 TRUSTWORTHY （因为我的项目是UNSAFE），然后运行此实例：

 创建组件[System.Runtime.Serialization] 
来自'C：\Windows\Microsoft.NET\Framework\v4.0.30319\System.Runtime.Serialization.dll' 
 WITH PERMISSION_SET =不安全； 
  

Json.NET依赖于哪个程序集（如果我不这样做，我收到错误）

同时我读到打开TRUSTWORTHY可能有多糟糕，然后我尝试

在签署自己的程序集之前，我知道我必须为<$ c创建一个密钥$ c> System.Runtime.Serialization ，因为我的项目依赖于Json.Net，而后者又依赖于它，但是当我运行它时，我得到的是：

  USE [master]; 
 GO 
创建不对称密钥[SystemRuntimeSerializationKey] 
授权[dbo] 
来自可执行文件='C：\Windows\Microsoft.NET\Framework\v4.0.30319 \System.Runtime.Serialization.dll'; 
 GO 
 
消息15468，级别16，状态5，第3行
生成非对称密钥时发生错误。 
  

这对我没有多大帮助。

那么是否有可能为.NET Framework程序集生成这样的密钥，或者是否存在其他解决方法，而不是将TRUSTWORTHY设为ON？

解决方案

不，我从未找到实现此目的的方法。用于签名.NET Framework Assembly的密钥是Microsoft内部/私有的。尝试了以下选项：

• 将私钥提取/加载到SQL Server中：不可能，否则密钥将不是私钥 。签名/强命名系统很有效，因为外部人无法声称他们对代码进行了签名。




• 添加签名：我尝试使用<$添加一个新的c $ c> sn 实用程序。由于以下原因而无法使用

  
  

  无法重新签名程序集-程序集的公钥与签名公钥不匹配。 / p>
  

  但是，即使这样做确实可行，也可能并不意味着将其加载到SQL Server中就可以了，因为它与您作为资源添加到项目中。其他.NET Framework DLL，如果还有其他依赖项，则只会知道原始签名。




• 删除当前签名并添加一个新签名：我尝试过使用 ILDASM 分解 System.Runtime.Serialization.dll ，然后添加从<$ c创建的新私钥$ c> sn -k ，然后使用 ILASM 重新链接，但是在 ILASM 步骤（而且我没有时间进一步调查）。

  
  
  

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




本质上，如果加载的是您无法控制的DLL，那么唯一的希望就是使用 ILDASM进行反编译并使用 ILASM 重新编译，并指定一个新的 snk 文件，但如果其他文件则无效程序集链接到正在重新编译的内容。当然，.NET Framework DLL属于此类。

简单地说：如果要加载不受支持的.NET Framework DLL，则几乎需要设置 TRUSTWORTHY ON 。

但是：请记住，即使这样通过非对称密钥或证书确实可以加载所有内容，这并不意味着您不会遇到功能问题。这些库没有被批准/验证是有原因的。它们中有代码可以以您不期望的方式工作，例如将数据存储到静态字段。在Windows和控制台应用程序中，这不是问题，因为它是每个App Domain的一种用法。但是SQLCLR使用共享的App Domain，因此多个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:\Windows\Microsoft.NET\Framework\v4.0.30319\System.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:\Windows\Microsoft.NET\Framework\v4.0.30319\System.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屋！