SQL Server: “CREATE ASSEMBLY for assembly 'Tes

问题:

I get this error when I attempt to load a mixed mode C++/CLI assembly into SQL Server 2012:

CREATE ASSEMBLY [Test]
AUTHORIZATION [dbo]
from 'H:\test.dll'
WITH PERMISSION_SET = SAFE

Msg 6544, Level 16, State 1, Line 1 CREATE ASSEMBLY for assembly 'Test' failed because assembly 'Test.dll' is malformed or not a pure .NET assembly. Unverifiable PE Header/native stub.

回答1:

Mixed-mode Assemblies are not allowed in SQLCLR Assemblies; only pure MSIL Assemblies are allowed. This is implied in the MSDN documentation for CREATE ASSEMBLY in the Remarks section:

Assembly Validation
SQL Server performs checks on the assembly binaries uploaded by the CREATE ASSEMBLY statement to guarantee the following:

• The assembly binary is well formed with valid metadata and code segments, and the code segments have valid Microsoft Intermediate language (MSIL) instructions.

Please see the following answer (also here on Stack Overflow) for more details:

SQL Server custom CLR fails with error "Could not load file or assembly or one of its dependencies. The system cannot find the file specified."

especially the link to the Microsoft KB article Support policy for untested .NET Framework assemblies in the SQL Server CLR-hosted environment which states this restriction.

One possible reason why only managed code is allowed is that allowing unmanaged code would probably prevent SQL Server from enforcing certain restrictions (i.e. Host Protection Attributes, etc) for SAFE and EXTERNAL_ACCESS levels, as well as prevent verification.

And while setting an Assembly to UNSAFE lifts most of the restrictions, this one is still enforced:

Runtime Checks

At runtime, the code assembly is checked for the following conditions. If any of these conditions are found, the managed code will not be allowed to run and an exception will be thrown.

UNSAFE

Loading an assembly—either explicitly by calling the System.Reflection.Assembly.Load() method from a byte array, or implicitly through the use of Reflection.Emit namespace—is not permitted.

The quote above was taken from the following MSDN page: CLR Integration Programming Model Restrictions.

More info found at: CLR Hosted Environment.

回答2:

It is impossible to load a mixed mode C++/CLI assembly into SQL Server.

You can only load a "pure" C++/CLI assembly:

However, there may be a way around this. Use P/Invoke in your C# file. This is equivalent to writing a mixed mode C++/CLI assembly wrapper, because when CLR code calls native code in your assembly, it performs an implicit P/Invoke (let me know if this works for you).

Why does SQL server prevent mixed mode assemblies from being loaded?

This is my theory:

If we go back to 2005, things were different. When they released the first version of SQL Server to support the CLR, mixed mode assemblies would sometimes deadlock on a load. See Microsoft - Mixed DLL Loading Problem. Even after they fixed this issue, there was still arguments centered around security and stability which tipped the scales against allowing loading of mixed mode C++/CLI assemblies into the core of SQL server.

If anyone on the original Microsoft design team can fill in the blanks, I'd be very curious!