SQL Server has supported CLR usage since version 2005. But support of .NET framework assemblies within SQL Server is limited per our support policy in KB http://support.microsoft.com/kb/922672.
Some users chose to use .NET framework assemblies outside the list in KB http://support.microsoft.com/kb/922672. This can cause various issues. Lately we have had a few reports of the following error following upgrade to SQL Server 2012.
Msg 6544, Level 16, State 1, Line 2
CREATE ASSEMBLY for assembly '<assembly name>' failed because assembly ‘<assembly name>’ is malformed or not a pure .NET assembly. Unverifiable PE Header/native stub.
A little background. When you develop your user assembly, you can reference .NET framework assemblies. If the referenced .NET framework assemblies are all from the supported list, you only need to register your own user assembly by using CREATE ASSEMBLY statement. When you use a .NET framework assembly that is not in the supported list, the following happens:
- You are required to mark your assembly to be unsafe.
- You are required to use CREATE ASSEMBLY statement to register .NET framework assembly and referenced assemblies (not in the supported list) within SQL Server database. In other words, the .NET framework assembly has to physically reside in a SQL Server database just the same as your own assembly.
- When you do this, you are presented with a warning: “Warning: The Microsoft .Net frameworks assembly 'AssemblyName' you are registering is not fully tested in SQL Server hosted environment.”
There are two types of .NET assemblies. Pure .NET assemblies only contain MSIL instructions. Mixed assemblies contain both unmanaged machine instructions and MSIL instructions. Mixed assemblies in general are compiled by C++ compiler with /clr switch but contain machine instructions resulting from native C++ code.
Regardless which version of SQL Server, CREATE ASSEMBLY only allows pure .NET assemblies to be registered. SQL Server has always required that an assembly to be loaded into SQL Server database with CREATE ASSEMBLY contains only MSIL instructions (pure assembly). CREATE ASSEMBLY will raise the above error if an assembly to be registered is mixed assembly.
Why are we seeing this issue now more often than before?
SQL Server 2005, 2008 and 2008 R2 use CLR 2.0. In SQL Server 2012, we upgraded CLR to use 4.0. As a result, all the .NET framework assemblies will need to be in version 4.0. If you have used a .NET framework assembly that is not in the supported list, you must re-register the 4.0 version using CREATE ASSEMBLY statement following upgrade. Some .NET framework assembly such as WCF started referencing mixed mode assembly in 4.0. Therefore you started to experience the issue in SQL 2012 instead of early versions.
A couple of clarifications
- The above error can occur in any version of .NET framework if the assembly you are trying to register (with CREATE ASSEMBLY) is not a pure .NET assembly. A .NET framework assembly is not guaranteed to be a pure .NET assembly in very version. Additionally, a newer version assembly may reference non-pure .NET assembly. In such situations, upgrade will fail with the above error.
- The issue occurs only if you use unsupported .NET framework assemblies which result in validation because of CREATE ASSEMBLY is involved. If your user assembly references the assemblies in the list documented in KB http://support.microsoft.com/kb/922672 (which will be updated to reflect the issue documented in this blog), we ensure it will work.
Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support