使用ICSharpCode.SharpZipLib的SQLCLR项目发布失败问题排查

Alright, let's dig into this issue since your SQLCLR project was running smoothly in EXTERNAL_ACCESS mode with a signed key until you added that SharpZipLib function. Compilation worked after fixing version compatibility, but deployment's failing at the final step—here's what's likely going wrong and how to fix it:

• Verify SharpZipLib's Strong Name Signing
  SQLCLR enforces strong name signing for all assemblies running in EXTERNAL_ACCESS or UNSAFE mode. The default NuGet package of SharpZipLib is usually not strongly signed, which will break deployment even if your main project is signed. You have two options here:

  1. Grab SharpZipLib's source code, recompile it using the same .snk signature key your original SQLCLR project uses.
  2. Use ildasm to decompile the existing SharpZipLib DLL, then recompile it with ilasm while specifying your project's .snk file for signing.

  Critical note: Using a different key for SharpZipLib will make SQL Server treat it as an untrusted dependency—stick to your project's existing signature key.

• Fix Deployment Order & Permission Settings
  SQL Server needs dependencies deployed first, and SharpZipLib may require higher permissions than EXTERNAL_ACCESS. Follow this order:

  1. Deploy SharpZipLib to your database first with UNSAFE permissions (it often handles low-level file operations that EXTERNAL_ACCESS doesn't allow):

     CREATE ASSEMBLY [ICSharpCode.SharpZipLib]
     FROM 'C:\Your\Path\To\ICSharpCode.SharpZipLib.dll'
     WITH PERMISSION_SET = UNSAFE;
     

  2. Then deploy your main SQLCLR project assembly, keeping its PERMISSION_SET as EXTERNAL_ACCESS.

• Link SharpZipLib's Signature to SQL Server Permissions
  Since your project uses a signed key for EXTERNAL_ACCESS privileges, you need to ensure SharpZipLib's signature is trusted by SQL Server:

  • If you signed SharpZipLib with your project's existing key: Confirm the key's associated login has EXTERNAL ACCESS ASSEMBLY permissions (it should already if your original project worked).
  • If you used a different key for SharpZipLib (not recommended, but if necessary):

    -- Import the SharpZipLib assembly's public key
    CREATE ASYMMETRIC KEY SharpZipLibKey
    FROM EXECUTABLE FILE = 'C:\Your\Path\To\ICSharpCode.SharpZipLib.dll';
    
    -- Create a login linked to this key
    CREATE LOGIN SharpZipLibLogin
    FROM ASYMMETRIC KEY SharpZipLibKey;
    
    -- Grant required permissions
    GRANT EXTERNAL ACCESS ASSEMBLY TO SharpZipLibLogin;
    

• Check Detailed Deployment Error Logs
  Don't just rely on the top-level error message! Dig into the full deployment logs in Visual Studio or run the deployment SQL manually to see the root cause. Common specific errors include:

  • "Assembly 'ICSharpCode.SharpZipLib' is not trusted": Signature or permission issue.
  • "Could not find assembly 'ICSharpCode.SharpZipLib'": Deployment order is reversed.
  • "The assembly is built with a runtime newer than the currently loaded runtime": You fixed version compatibility for compilation, but need to ensure SQL Server supports the .NET version SharpZipLib targets.

One last tip: If you're using Visual Studio's SQL Database Project, go to your SQLCLR project properties, add SharpZipLib as a dependency, and set its Permission Set to Unsafe. This ensures Visual Studio handles the deployment order correctly automatically.

内容的提问来源于stack exchange，提问作者Satya