1

I'm trying to use the Chilkat encryption library in SQL Server 2008 via a CLR assembly. Because of how the Chilkat library is put together (see their message below), I can't do this directly and need to create a wrapper-class that references the Chilkat library and yet is fully-managed the way SQL Server requires.

Below is the response from Chilkat when I asked about how to interface their library directly by SQL server (the short version is that you can't).

The Chilkat .NET assembly is a mixed-mode assembly, meaning that the implementation is written in C++ and compiles to native code. The outer layer is a managed interface. I've learned from other customers in the past that in this situation you can solve the problem by creating a simple wrapper class library in Visual Studio (fully managed) where the wrapper assembly references the Chilkat assembly, and the SQL Server objects instead reference your wrapper assembly. Given that you're probably only calling a handful of Chilkat methods (and properties), it shouldn't be much work to write the few methods to forward the call to the wrapped Chilkat object, and return the result.

Ok. So here's the problem. When I create a CLR project in Visual Studio 2008, I am completely unable to add any reference to the Chilkat library (or any other for that matter). There is no Browse tab / button / link or whatever to add an additional reference library. It's simply not there.

Please help before I'm forced to resort to Clipper or dBase3+ !

Particulars: SQL Server 2008, Visual Studio 2008, Chilkat evaluation library April 2011.

Jim M
  • 541
  • 1
  • 6
  • 18

1 Answers1

1

SQL Server is a bit picky about what CLR assemblies it'll allow to be loaded and Visual Studio prevents you from adding any old assembly reference willy nilly.

This is because you need to load third party assemblies via the New Assembly tool found under the "Programmability" node for your database (right click on Assemblies), or by using CREATE ASSEMBLY.

This gives SQL server a chance to inspect the assembly and validate to ensure that it is of a supported type for use with SQL CLR integration. It does this to ensure that the assembly isn't likely to kill SQL server or cause stability problems.

This section of SQL Books Online covers SQL CLR restrictions:

CLR Integration Programming Model Restrictions

The Chilikat assembly will fail at the PEVerify test (described under the EXTERNAL_ACCESS section) because mixed mode assemblies containing unmanaged functions are not verifiable type safe code.

If you try to install an unverifiable mixed mode assembly then you'll get an error such as:

CREATE ASSEMBLY for assembly 'ChilkatDotNet2' failed because assembly 'ChilkatDotNet2' is malformed or not a pure .NET assembly. Unverifiable PE Header/native stub. (Microsoft SQL Server, Error: 6544)

There is a discussion thread here that covers this topic and some suggested work arounds.

Kev
  • 118,037
  • 53
  • 300
  • 385