none
another dynamic assembly loading problem (XmlSerializer)

    Question

  • I am having an issue with loading dynamic generated assemblies in my CLR SQL stored procedure. I have tried turning on the "Generate serialization assembly: ON" and have read numerous articles regarding work around but have been very unsuccessful in getting this to work.


    The problem lies in when the code calls

    XmlSerializer xmlSerializer = new XmlSerializer(typeof(MyClass));

    where MyClass is a generated schema class using XSD. Since it wouldn't auto generate the serializer assembly, what I did instead was created a new class library project with only that line of code and provided the class definition within that same class without any of the attributes from the XSD. I am still getting an error on that same line. So what I did next was do the manual sgen and made sure it generated with the same signed assembly snk file. Manually loaded into sql server and still got the same error. Any ideas why this is?


    Code Snippet

    public partial class TestClass
    {
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void testThis()
    {
    MyOwnClass myClass = new MyOwnClass();
    //Serialize message to xml
    XmlSerializer xmlSerializer = new XmlSerializer(typeof(MyOwnClass));
    }
    }


    public class MyOwnClass
    {
    private string hi;
    public MyOwnClass()
    {
    hi = "1";
    }
    public string Hi
    {
    get
    {
    return hi;
    }
    }
    }




    Monday, April 23, 2007 8:40 PM

All replies

  • Your code works fine for me.  Are you sure you are creating the XmlSerializers assembly correctly under SQL Server? When you turn "Generate Serializaton Assembly" to on under VS, it will create the assembly but not deploy it to the database for you, so you need to create it yourself like so:

     

    create assembly [TestClass.XmlSerializers] from 'c:\assemblies\TestClass.XmlSerializers.dll'

     

    Also, what is the exact error message and call stack you are getting?

     

    Steven

    Tuesday, April 24, 2007 7:32 PM
  • In order to get Visual Studio to actually build a serialization assembly, you have to both

     

    1. Set the "Generate Serialization Assembly" to ON, and

    2. Add, by hand, the following SGEN task to your project file:

     

    <Target Name="GenerateSerializationAssembliesForAllTypes" DependsOnTargets="AssignTargetPaths;Compile;ResolveKeySource" Inputs="$(MSBuildAllProjects);@(IntermediateAssembly)" Outputs="$(OutputPath)$(_SGenDllName)">

    <SGen BuildAssemblyName="$(TargetFileName)" BuildAssemblyPath="$(OutputPath)" References="@(ReferencePath)" ShouldGenerateSerializer="true" UseProxyTypes="false" KeyContainer="$(KeyContainerName)" KeyFile="My_Key_File.snk" DelaySign="$(DelaySign)" ToolPath="$(SGenToolPath)">

    <Output TaskParameter="SerializationAssembly" ItemName="SerializationAssembly" />

    </SGen>

    </Target>

     

    There is, however,  a major problem with this approach: The SGEN task will only execute AFTER all post-build events have fired. Thus, if you have a post-build event that copies the serialization assembly to, say, a deployment folder, it will copy the old one because the copy occurs before the SGEN task is executed. This problem is best resolved by running SGEN from a post-build event like so:

     

    sgen /aEmbarrassed(ProjectDir)$(OutDir)$(ProjectName).dll /compiler:/keyfileEmbarrassed(ProjectDir)My_Key_File.snk /f

     

     

    But this is where your problems really begin. The only way I have been able to get SQL CLR to actually load the serialization assembly is to apply the XmlSerializationAssembly attributes to my serializable classes AND load the serialization assemblies into the GAC and SQL CLR. I have described this in detail in a previous post and it is the ONLY way I am able to invoke XML serialization from within a SQL CLR procedure.

     

    Developing SQL CLR components is substantially more difficult than it needs to be due to issues just like this - and documentation is almost impossible to find for all but the most trivial use cases. I was very excited about the integration of SQL Server and .Net. After having worked a bit with this technology though, I am much less enthusiastic....

    Wednesday, April 25, 2007 2:03 PM
  • Steve - the assembly is not getting generated with the "Generate Serializaton Assembly" set to On.  What I have been doing is like what NTDeveloper said (manually using SGEN).  I then manually deploy that serializer assembly into SQL.

    The error message i'm getting is the typical one that everyone sees

    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[])

    NTDeveloper - I can do it the way you mentioend in your post, but I don't need the XmlSerializationAssembly attributes, I just need to deploy it to GAC.  I don't like this approach however...
    Thursday, April 26, 2007 11:47 PM
  • TCHEN,

     

    You are absolutely correct; I too was able to get the assemblies to load by ONLY putting them into the GAC and NOT attaching the XmlSerializationAssembly attribute. Now how in the heck are we going to get this to work without putting these things in the GAC ??? This is also something I would very much like to avoid. According to Steve, SQL CLR will not load assemblies from the GAC unless the are on an internal, predefined list controlled by Microsoft. Obviously though, the GAC somehow plays a role here though; otherwise, the installation of these assemblies to the GAC would be irrelevant. Moreover, as I noted in another post, if the MVID (which gets regenerated each build) of the assembly in the GAC does not match the MVID assembly loaded into SQL CLR, you will get an exception that informs you of this.

     

     

    I am at a loss.

     

    Friday, April 27, 2007 4:48 PM
  • I believe I have figured out what the problem is. The XmlSerialization assembly binding code will only load a strong-named assembly if it has an Assembly Version specified.  I'm really not sure why this is, but I'll try to find out.  If there is no Version specified, then the sgened assembly will not be found and it will try to load one dynamically, which of course fails.  As NTDeveloper found, one workaround for this is to add the versionless .XmlSerializers assembly to the GAC.  Note that the actual XmlSerializers assembly will still be loaded from the database, the gac check occurs before the assembly attempts to load (I believe this check is done because the CLR can skip verifying the assembly strong name if it is loaded from the GAC).

     

    So, in short, in order to use XmlSerialization on a signed assembly in SQL CLR, your assembly must be annotated with an assembly version:

     

    [assembly:AssemblyVersion("1.2.3.4")]

    [assembly:AllowPartiallyTrustedCallers]

    public class ...

     

    (AllowPartiallyTrustedCallers is also required for Strong Named assembly, as per http://blogs.msdn.com/sqlclr/archive/2006/06/22/643554.aspx).

     

    Hopefully this solves your problem.  There is certainly no argument from me that this is a pretty complicated process.

     

    Steven

     

    Friday, April 27, 2007 9:45 PM
  • Steven,

     

    Thanks for your response.

     

    Unfortunatey, I don't believe the version of the assembly is the issue. When SGEN generates a serialization assembly S for an assembly A, it automatically versions S with the version of A. Thus, since all of my assemblies are versioned the serialization assemblies are versioned as well.

     

    Any other ideas?

     

    Thanks,

    Chris

    Monday, April 30, 2007 12:18 PM
  • Hmm, no I was sure it was going to be the AssemblyVersion issue as that is the only case I could see that fits your repro.  Without a repro that I can debug, I'm really not sure what else could be causing this behavior.   Can you try setting the Fusion ForceLog on and checking the Fusion Log to see what the output is when it tries to load the XmlSerializers assembly? This log should show exactly why it failed to load the pregenerated assembly.

    Steven
    Monday, April 30, 2007 5:18 PM
  • You actually may end up being right about the assembly version. Here's the deal:

     

    I have FINALLY managed to successfully invoke XML serialization from INSIDE SQL Server WITHOUT installing to the GAC !!! Other than the general difficulty of running managed code inside SQL Server (which, by the way, is considerably more difficult than it should be), I believe that the root of the problem was using Visual Studio to build the serialization assemblies via the SGEN build task. Although I changed my makefile, er, project file, to use the sgen.exe utility to generate the assemblies, I was still inadvertantly using the SGEN build task and overwriting the assemblies generated via sgen.exe. Once I completely wiped out the SGEN build task and relied only on the sgen.exe to build my serialization assemblies, everything began to work like MAGIC. Voila! No more need to deploy to the GAC, thus no need to deploy FROM the server, etc, etc. It may be that the SGEN build task manages to create unversioned assemblies; however, since I have completely removed these build tasks and the assemblies generated by it, I can't say for sure. At some point, I am going to create a test project and see whether this is the case and I will post a follow-up here indicating the results.

     

    Thanks for your help on this misery-inducing problem!

    Friday, May 04, 2007 2:27 PM
  • NTDeveloper,
      I'm a little confused.  When you say SGEN build task, do you mean you set "Generate Serialization Assemblies" to ON?

    Thanks.
    Monday, May 07, 2007 9:01 PM
  • Steve,

    I followed NTDeveloper's advice and started to manually sgen the serializer assembly instead of installing it to the GAC, and i'm still getting an error. Here is the latest stack trace.

    here is my sgen command

    "$(DevEnvDir)..\..\SDK\v2.0\bin\sgen.exe" /f /aEmbarrassed(TargetPath) /c:/keyfileEmbarrassed(ProjectDir)StrongMail.snk

    A .NET Framework error occurred during execution of user-defined routine or aggregate "SendNotificationEmail":
    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)
    at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
    at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
    at Microsoft.CSharp.CShar
    ...
    System.InvalidOperationException:
    at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
    at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, CompilerParameters parameters, Assembly assembly, Hashtable assemblies)
    at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
    at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)
    at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
    at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
    Monday, May 07, 2007 11:36 PM
  • Yes; You have to set then "Generate Serialization Assemblies" to ON

     

    AND

     

    Manually define the  SGEN build task within your project file like so:

     

    <Target Name="GenerateSerializationAssembliesForAllTypes" DependsOnTargets="AssignTargetPaths;Compile;ResolveKeySource" Inputs="$(MSBuildAllProjects);@(IntermediateAssembly)" Outputs="$(OutputPath)$(_SGenDllName)">

    <SGen BuildAssemblyName="$(TargetFileName)" BuildAssemblyPath="$(OutputPath)" References="@(ReferencePath)" ShouldGenerateSerializer="true" UseProxyTypes="false" KeyContainer="$(KeyContainerName)" KeyFile="My_Key_File.snk" DelaySign="$(DelaySign)" ToolPath="$(SGenToolPath)">

    <Output TaskParameter="SerializationAssembly" ItemName="SerializationAssembly" />

    </SGen>

    </Target>

     

    HOWEVER, as I indicated in the post above, a serialization assembly generated in this fashion will not load correctly inside SQL CLR. As Steve suggested, it may be a versioning issue; I just don't know. I haven't gone back and generated an assembly in this fashion and checked the version information.

     

    After using the SGEN.exe tool did you uninstall the previously generated assemblies from the GAC? If not, this may be the reason why you can't get the new serialiazation assembly to load properly. After I switched to using the SGEN.exe tool, instead of the Visual Studio build task, this issue hasn't recurred.

    Tuesday, May 08, 2007 6:15 PM
  • Yes I checked the GAC...its empty.  I had Generate Serialization to OFF since i'm using the SGEN.exe tool rather than doing it through visual studio.  Even with this manner its failing.

    If I do it with an empty task with XmlSerializer = new XmlSerializer(MyClass) however..that works.  But with the use of making a webservice call, it fails with the error above.

    btw the Fusion log only logs exceptions to disk from visual studio it seems.  none of my sql clr stuff is getting logged.
    Tuesday, May 08, 2007 6:25 PM
  • I was also unable to get the Fusion tool to work for SQL CLR.

     

    Have you used the SGEN.exe tool to create a serialization assembly for your SOAP proxy class(es)?

    Tuesday, May 08, 2007 9:40 PM
  • Can you post the code you're using to call the webservice? Are you sure the error occurs while attempting to serialize MyOwnClass vs. a different class? It seems the XmlSerializer assembly can be loaded correctly for MyOwnClass, so perhaps the problem is elsewhere (such as trying to serialize a class shipped in a system assembly).

     

    Steven

    Friday, May 11, 2007 4:11 AM
  • I actually believe it is failing in the WebService proxy constructor.  I will try and manually generate the proxy class and report back to see if that works.

    http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx
    Monday, May 14, 2007 6:21 PM
  • Hello NTDeveloper

    I'm facing a similar problem, as described below. What i intend to do is to push some xml messages into private queue (MSMQ) and benefit from acknowledgement queue in order to obtain message status, using sql clr - SqlServer2008.
    I didn't try to install assemblies to GAC, but i could manage to generate in every build instance the serializable assembly or just simply using sgen.exe. None of these seem to work 'though when is comes to execute inside of sql clr... So check the code snipet and t-sql scripts:

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.IO;
    using System.Data.SqlTypes;
    using System.Data.SqlClient;
    using Microsoft.SqlServer.Server;
    using System.Messaging;
    using System.Xml;

    namespace PLADAMSMQ.Sql
    {
    public class SqlMSMQ
    {
    ///
    /// Sends message to queue
    ///
    /// Queue path
    /// Queue path
    /// Message
    [SqlProcedure()]
    public static void Send(SqlString PladaQueue, SqlString AckQueue, SqlXml Msg)
    {

    Message loMessage = new Message();
    XmlReader loXmlReader = Msg.CreateReader();
    System.Xml.Serialization.XmlSerializer loXMLSerializer = new System.Xml.Serialization.XmlSerializer(typeof(XmlDocument));
    XmlDocument loXmlDocument = new XmlDocument();

    loXmlDocument = (XmlDocument)loXMLSerializer.Deserialize(loXmlReader);

    {
    loMessage.CorrelationId = loMessage.Id;

    loMessage.Body = loXmlDocument;

    loMessage.AcknowledgeType = AcknowledgeTypes.FullReachQueue | AcknowledgeTypes.FullReceive;

    loMessage.AdministrationQueue = new MessageQueue(AckQueue.ToString());


    loMessage.Recoverable = true;
    }

    using (MessageQueue msgQueue = new MessageQueue(PladaQueue.ToString(), QueueAccessMode.Send))
    {

    msgQueue.Formatter = new XmlMessageFormatter(new Type[] { typeof(XmlDocument) });

    msgQueue.Send(loMessage);

    }
    }
    }
    }

    Assume that my database is trustworthy, clr is enabled on the server and necessary system.messaging.dll assembly is already created.

    In order to get this done, and using sql clr i had to create the following assemblies;

    CREATE ASSEMBLY SqlMSMQ
    AUTHORIZATION dbo
    FROM 'C:\SqlMSMQ.dll'
    WITH PERMISSION_SET = UNSAFE

    and for the serealizing/deserealizing process

    CREATE ASSEMBLY SqlMSMQXml from 'C:\SqlMSMQ.XmlSerializers.dll'
    WITH PERMISSION_SET = SAFE


    If i try to exec my procedure, with permissions set like above, i'll get the error:

    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.

    On the other hand, if the SqlMSMQ assembly is created like this:

    CREATE ASSEMBLY SqlMSMQ
    AUTHORIZATION dbo
    FROM 'C:\SqlMSMQ.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS

    and executing therefore the procedure, i'll get the error:

    System.Security.SecurityException: That assembly does not allow partially trusted callers.
    System.Security.SecurityException:
    at PLADAMSMQ.Sql.SqlMSMQ.Send(SqlString PladaQueue, SqlString AckQueue, SqlXml Msg)

    I've searched for a solution and tried suggested tips posted by you but when it seems that i'm moving forward, something goes wrong in sql clr world...

    Did you actually manage to get it done? Probably, this isn't the right way to do it and after more than 2 years, i should consider another approach.

    Could you please advise? Thanks in advance!


    PM - IT Developer
    • Edited by PNMM Thursday, August 27, 2009 8:53 AM
    Tuesday, August 25, 2009 3:47 PM
  • The message about dynamic XML serializers usually occurs because you either do not have an XMLSerializer that matches your serialization assembly (by MVID) or there's a serializer in the GAC also. Make sure that you don't have either your main assembly or the serialization assembly in the GAC. The other thing to do is to make sure that the code path you're using will load a static XML serializer, because of the fact that you're using XML serializer over a (generic) XmlDocument. I've seen static XMLSerializer work with in SQLCLR over a specific call made by a web service client, but not over a generic XmlDocument. That doesn't mean it shouldn't work, but it is something you should check.
     
    Also, I'm surprised that you were able to catalog an assembly that uses System.Messaging as external_access at all. This is because System.Messaging is not on the list of approved assemblies, and therefore an assembly that uses it should have to be unsafe or the create assembly statement should fail. I'll do some testing with the code provided for this one.
     
    Cheers,
    Bob Beauchemin
    SQLskills
    Wednesday, August 26, 2009 4:02 PM
    Moderator
  • Thanks for the reply Bob Beauchemin,

    Nevertheless, i gave up from that approach... I was kind of stuck and i couldn't find any solution and workaround... As i read more than once and experienced by my self, SQL CLR seems a powerfull way of executing .net Code inside Sql, but when it comes to the real thing, some security issue arises in the assemblies world and you get the feeling you're trying to accomplish an impossible task... Nice try, MS.

    Anyway, i've chosen SSIS in order to achieve my goals. I've been told that i could use External Activator from Sql Service Broker. My choice was SSIS only because i'm confortable with it.

    If someone find himself stuck with a similar problem, please check http://www.sqlservercentral.com/Forums/Topic701921-386-1.aspx and see if helps.


    Thanks.




    PM - IT Senior Analyst/Developer
    Thursday, August 27, 2009 8:34 AM