无法将System.IO.Compression添加到SQL Server中的受信任程序集 [英] Can't add System.IO.Compression to trusted assemblies in SQL Server

问题描述

我试图在NET 4.5中创建一个用ZIP文件摆弄的SQLCLR存储过程.显然， System.IO.Compression 不在SQL Server的批准列表中，但这是当我尝试通过SQL Server Management Studio手动添加它时得到的.如果我尝试通过查询执行 CREATE ASSEMBLY ，也会发生同样的情况.有任何想法吗?为什么这是禁忌?

我也尝试过在SSMS中运行以下命令:

 创建装配系统IOCOMPRESSION从'C:\ Program Files(x86)\ Reference Assemblies \ Microsoft \ Framework \ .NETFramework \ v4.5.1 \ System.IO.Compression.dll'使用PERMISSION_SET = EXTERNAL_ACCESS 

...，结果与我通过SSMS UI尝试时的结果相同.

解决方案

您正在尝试导入错误的DLL.那是参考" DLL，而不是实际的框架" DLL.我将不仅可以使用SQL Server 2012尝试以下操作，而且还可以导入 System.IO.Compression.FileSystem 并执行 System.IO.Compression.ZipFile.CreateFromDirectory():

1. 这是必需的，因为无法从.Net Framework DLL创建 ASYMMETRIC密钥:

     ALTER DATABASE [{dbname}]设置为可信任； 

2. 至少您需要这样做:

    创建组件[System.IO.Compression]从N'C:\ Windows \ Microsoft.NET \ Framework \ v4.0.30319 \ System.IO.Compression.dll'WITH PERMISSION_SET = EXTERNAL_ACCESS; 

3. 如果要使用ZipFile 或 ZipFileExtensions 类，您将需要此(不幸的是，这个需要设置为 UNSAFE ):

    创建组件[System.IO.Compression.FileSystem]从N'C:\ Windows \ Microsoft.NET \ Framework \ v4.0.30319 \ System.IO.Compression.FileSystem.dll'WITH PERMISSION_SET =不安全； 

4. 以防万一，在上述程序集中调用方法的程序集还需要具有 EXTERNAL_ACCESS 的 PERMISSION_SET .

p>

5. 对于所有可能不知道这一点的人来说，请完全清楚:

   1. Zip * 类以及 System.IO.Compression 和 System.IO.Compression.FileSystem 程序集是新的.Net 4.5
   2. 您只能使用SQL Server 2012和2014(当前版本)中.Net 4.0/4.5的新项目；SQL Server 2005/2008/2008 R2停留在.Net 2.0系列上.

I am trying to create a SQLCLR stored procedure in NET 4.5 that fiddles with ZIP files. Obviously System.IO.Compression is not on SQL Server's approved list but this is what I get when I try to add it manually via SQL Server Management Studio. The same happens if I try to execute CREATE ASSEMBLY via a query. Any ideas? Why is this a no-no?

I have also tried running this command in SSMS:

CREATE ASSEMBLY SystemIOCOMPRESSION
  FROM 'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5.1\System.IO.Compression.dll'
  WITH PERMISSION_SET = EXTERNAL_ACCESS

... and the result was the same as when I tried via SSMS UI.

解决方案

You are trying to import the wrong DLL. That is the "reference" DLL, not the actual "framework" DLL. The following will work as I not only tried it using SQL Server 2012, but I was able to also import System.IO.Compression.FileSystem and execute System.IO.Compression.ZipFile.CreateFromDirectory():

1. This is needed as you cannot create an ASYMMETRIC KEY from the .Net Framework DLLs:

   ALTER DATABASE [{dbname}] SET TRUSTWORTHY ON;
   

2. At minimum you need this:

   CREATE ASSEMBLY [System.IO.Compression]
   FROM N'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.IO.Compression.dll'
   WITH PERMISSION_SET = EXTERNAL_ACCESS;
   

3. If you want to use either the ZipFile or the ZipFileExtensions classes, you will need this (and this one needs to be set to UNSAFE, unfortunately):

   CREATE ASSEMBLY [System.IO.Compression.FileSystem]
   FROM N'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.IO.Compression.FileSystem.dll'
   WITH PERMISSION_SET = UNSAFE;
   

4. Just in case it is not obvious, your assembly that calls methods in the above mentioned assemblies will also need to have a PERMISSION_SET of EXTERNAL_ACCESS.

5. And just to be completely clear for anyone who might not be aware of this:

   1. The Zip* classes as well as the System.IO.Compression and System.IO.Compression.FileSystem assemblies are new as of .Net 4.5
   2. You can only use items new to .Net 4.0 / 4.5 in SQL Server 2012 and 2014 (the current version); SQL Server 2005 / 2008 / 2008 R2 are stuck on the .Net 2.0 series.

这篇关于无法将System.IO.Compression添加到SQL Server中的受信任程序集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！