Dynamic Assembly Loading
Simple question: what security permission level is required to allow dynamic loading of assemblies? Trick question: dynamic assembly loading is always disallowed under SQL CLR, even under UNSAFE.
This is mentioned twice in BOL:
CLR Integration Programming Model Restrictions :
“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.”
“Note that the ability to generate managed code dynamically, using the Reflection.Emit API, is not supported inside the CLR-hosted environment in SQL Server. Such code would not have the CAS permissions to run and, hence, would fail at run-time.”
Despite the BOL warnings, it’s easy to run into this restriction as the code doing the dynamic loading might be in a dependent assembly or even in the clr itself. The problem is also difficult to debug as many disparate types of applications might fail in completely different ways for this same underlying reason. Over the next couple of posts, I’ll go through some of the more common cases that I’m aware of and how to solve them.
First, the most common case and the easiest one to fix is covered by Vineet in this blog post last year, is described in BOL: XML Serialization from CLR Database Objects, and even has its own KB article.
When using Xml Serialization, you need to pre-generate the XmlSerializers assemblies to perform your serialization. Visual Studio will do this for you if you check the “Generate Serialization Assembly” on the Project Properties/Build pane, or you can use the sgen tool from the CLR SDK. Either way, you then need to register the XmlSerializers assembly in your database:
CREATE ASSEMBLY [MyAssembly.XmlSerializers] FROM ‘<path>\MyAssembly.XmlSerializers.dll’
If you do not do this, then you’ll see the following error when you try to use code that needs to serialize your class to xml:
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'MyClass':
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
Most of the time, this error is solved by performing the sgen steps listed above. Unfortunately, there are a small number of cases where this error can occur using XmlSerialization even if you correctly sgened your assembly. I’ll cover some of those cases and how to fix them in future posts.
-- Steven Hemingray