3

When I execute the SP CLR from this example, on SQL Server version 11.0.3128 (basically 2012 + SP1), sometime I got the following error, and sometime not :

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "XMLTest": 
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.RuntimeAssembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection, SecurityContextSource securityContextSource)
   at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
   at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
   at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
   at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources)
   at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, XmlSerializerCompilerParameters xmlParameters, Evidence evidence)
System.InvalidOperationException: 
   at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, XmlSerializerCompilerParameters xmlParameters, Evidence evidence)
   at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, XmlSerializerCompilerParameters parameters, Assembly assembly, Hashtable assemblies)
   at System.Xml.Serialization.XmlSerializer.GenerateTempAssembly(XmlMapping xmlMapping, Type type, String defaultNamespace)
   at System.Xml.Serialization.XmlSerializer..ctor(Type type, String defaultNamespace)
   at StoredProcedures.Person.GetXml()
   at StoredProcedures.XMLTest()

My question is: Why it doesn't append in all of SQL server 11.0.3128 ? I got two customers with this same version, one run into the problem and the other not.

I checked:

  • The compatibility version (both 11.0)
  • The clr framework version (both 4.0.30319)
  • The database settings Trustworthy (both true)
  • The assembly permission set (I tried Safe, Unsafe, External Access)

I know about the workaround with sgen.exe (as explained in the link), but i like to understand why this server doesn't behave the same than all the other, am I missing a settings somewhere ? Thx in advance.

Jimbot
  • 696
  • 5
  • 20
  • using *complex dynamic engines* like `XmlSerializer` doesn't seem a good fit for SQL-CLR, especially when SQL Server has xml built in... – Marc Gravell Mar 16 '18 at 15:39
  • 1
    Where did you get that CLR version from? From the DMV or the .NET folder in the `C:\Windows\Microsoft.NET\Framework64` folder? If so, that is the CLR version, not necessarily the .NET Framework version. Go to "Add / Remove Programs" and find the highest level Framework patch installed. That is the Framework version being used. I would set the Assembly back to `SAFE` and `TRUSTWORTHY` to `OFF` in both servers. Is the security the same on both servers for the login executing the function? Is one a sysadmin and the other not, or something like that? Is the assembly signed? – Solomon Rutzky Mar 16 '18 at 15:41
  • 1
    SQL Server uses CAS (Code Access Security) to restrict such things, which has been deprecated not so recently (but *should* still work for 4.0 hosts). Two things to check: has the customer turned off CAS system-wide, and what's the [real version](https://learn.microsoft.com/dotnet/framework/migration-guide/how-to-determine-which-versions-are-installed) installed on both machines, beyond the generic 4.0.30319 which is the same for all runtimes? – Jeroen Mostert Mar 16 '18 at 15:41
  • Thx @SolomonRutzky, I didn't realize they don't have the same framework version. – Jimbot Mar 21 '18 at 09:03
  • Thx @JeroenMostert , I didn't realize they don't have the same framework version. – Jimbot Mar 21 '18 at 09:03

0 Answers0