Can't add System.IO.Compression to trusted assemblies in SQL Server

Question

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.

Answer 1

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.