April 21, 2009 at 3:34 pm
Farily new to 2005 CLR - I am trying to send an xml message via MSMQ from a stored procedure.
I am passing an XmlDocument object to a Message and am receiving errors.
First I resolved this issue (I think), only to start seeing this issue and can't seem to get past the "assembly does not allow ..." issue.
This method works via web page, but this has to be a SQL Server 2005 security issue, becuase that is the only place it is failing.
I have been looking everywhere and have tried at least 20 different iterations.
I created (sgen) the serialized version, my assembly is strong named, the XmlSerializer is strong named, the assembly property AllowPartiallyTrustedCallers is set.
My db is set trustworthy on
assemblies Permission set: Safe - primary and XmlSerializer
What else can I do, how else can I gather more info to help me troubleshoot?
April 21, 2009 at 3:40 pm
Sample code sending xml message to msmq from stored procedure:
public static void Send(SqlString queue, SqlString msg)
{
XmlDocument _xmlDoc;
XmlNode _xmlNode;
XmlElement _xmlElement1;
XmlElement _xmlElement2;
XmlText _xmlText;
_xmlDoc = new XmlDocument();
_xmlNode = _xmlDoc.CreateNode(XmlNodeType.XmlDeclaration, "", "");
_xmlDoc.AppendChild(_xmlNode);
_xmlElement1 = _xmlDoc.CreateElement("", "ROOT", "");
_xmlText = _xmlDoc.CreateTextNode("");
_xmlElement1.AppendChild(_xmlText);
_xmlDoc.AppendChild(_xmlElement1);
_xmlElement2 = _xmlDoc.CreateElement("", "PLCID", "");
_xmlText = _xmlDoc.CreateTextNode("1");
_xmlElement2.AppendChild(_xmlText);
_xmlDoc.ChildNodes.Item(1).AppendChild(_xmlElement2);
using (MessageQueue msgQueue = new MessageQueue(queue.ToString(), QueueAccessMode.Send))
{
//// version1
//msgQueue.Formatter = new XmlMessageFormatter(new System.Type[] { typeof(string) });
msgQueue.Formatter = new XmlMessageFormatter(new Type[] { typeof(string) });
msgQueue.Send(msg.Value);
//msgQueue.Send(_xmlDoc);
//version2
//XmlNode node = _xmlDoc;
//XmlSerializer XS = new XmlSerializer(typeof(XmlNode));
//MemoryStream MS = new MemoryStream();
//XS.Serialize(MS, node);
//string str = System.Text.Encoding.ASCII.GetString(MS.GetBuffer());
//string label = string.Format("Sent at {0}", DateTime.Now);
//msgQueue.Send(str, label);
}
}
April 21, 2009 at 3:42 pm
Sample database code:
alter database test set trustworthy on
create assembly Messaging
authorization dbo
from 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
with permission_set = unsafe
go
-- version 2
create assembly [nsSqlMsmq] from 'C:\tempsSqlMsmq.dll'
create assembly [nsSqlMsmq.XmlSerializers.dll] from 'C:\tempsSqlMsmq.XmlSerializers.dll'
-- Create procedures
create PROCEDURE uspMSMQSend
@queue nvarchar(200),
@msg nvarchar(MAX)
AS EXTERNAL NAME nsSqlMsmq.[nsSqlMsmq.clsSqlMsmq].Send
GO
--EXEC uspMSMQSend 'server-name\private$\queuename', '1'
April 27, 2009 at 7:09 am
No input at all?
August 24, 2009 at 7:44 am
Hello
I'm struggling with a similar problem... I'm also using SQL CLR in order to push xml messages into private queue (MSMQ).
I've also got that annoying error "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" and did everything that was suggested by experts in order to solve it.
After correcting permission assembly (i missed that one) into:
CREATE ASSEMBLY SqlMSMQ
AUTHORIZATION dbo
FROM 'C:\SqlMSMQ.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
------------------------------------------------
--This was the permission level i was using before
--WITH PERMISSION_SET = UNSAFE
------------------------------------------------
and registering proper assembly to serialize:
CREATE ASSEMBLY SqlMSMQXml from 'C:\SqlMSMQ.XmlSerializers.dll'
WITH PERMISSION_SET = SAFE
now i get the security error (the one that you're also familiar with):
"That assembly does not allow partially trusted callers.
System.Security.SecurityException:
at PLADAMSMQ.Sql.SqlMSMQ.Send(SqlString PladaQueue, SqlString AckQueue, SqlXml Msg)"
Have you manage to solve the problem? Anyone can help please?
Thanks in advance...
PM
August 24, 2009 at 8:55 am
I've also got that annoying error "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" and did everything that was suggested by experts in order to solve it.
The reason the above is not allowed in most hosting companies is the Asp.net reflection permission requirement because LoadFrom is from the Assembly class in System.Runtime.Reflection which is defined without a default constructor. That makes you application to require Full Trust which is very high permissions. If all you need is serializing XML then you could try the IXMLSerializable interface it was undocumented in .NET 1.1 and documented in .NET 2.0 and above.
http://msdn.microsoft.com/en-us/library/system.xml.serialization.ixmlserializable.aspx
Kind regards,
Gift Peddie
August 25, 2009 at 3:32 am
Thanks for the help Gift Peddie,
I will try to explain better what i intend to do. Maybe i'm going in the wrong direction and perhaps you can point me out the right and better solution.
All i want is to push xml messages into a private queue and i'm using SQL CLR to do that (my first approach was SSIS with Message Queue Task, but i gave up as i couldn't even establish the connection to private queue; see my post in http://www.sqlservercentral.com/Forums/Topic542430-148-1.aspx).
As i was saying, i'm using SQL CLR (SqlServer2008) and actually i can push messages into my private queue (MSMQ). All i want is to take advantage of acknowledgement queue and deal with message status. The way i found to do that was to read xml from sql procedure and de-serialize it into a xmldocument in order to create a message queue and go from there...
Bellow is my code:
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
{
///
///
/// 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);
}
}
}
}
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 then if i exec 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 from experts but when it seems that i'm moving forward, something goes wrong in sql clr world... Maybe i should go in other direction.
Please advise and thanks for the help.
PM
August 25, 2009 at 8:37 am
pnmm (8/25/2009)
I will try to explain better what i intend to do. Maybe i'm going in the wrong direction and perhaps you can point me out the right and better solution.
What you are seeing is what many other developers have already had to learn the hard way: SQLCLR is a great thng, but, it does not nearly live up to the implicit hype that MS has used to promote it.
In particular, virtually all developers start out with the following two impressions of why SQLCLR is good, or when they should use it:
1) Use SQLCLR when SQL is too slow and you need the speed of compiled .NET. and,
2) Use SQLCLR to do anything that you cannot easily do in SQL
Unfortunately, neither of these impressions is accurate (though confusingly, neither are they entirely incorrect either). I won't belabor the performance point here except to say that it's only achievable about half the time and almost never for simple things (in other words: complex functions can be faster, but simple fnctions are almost always *slower*).
As for the second issue, the ugly honest truth is that SQLCLR is one of the most restrictive and byzantine development environments in all of MS-world currently. In fact, it is arguably more restrictive and certainly more byzantine than T-SQL itself. For instance, there is a huge amount of things that developers take for granted , but that can ONLY be done with UNSAFE permissions. And there is a real, significant number of things that cannot be done at all in SQLCLR. And then there remains all of the stuff that can be done, but it requires, obscure, complex, roundabout (i.e., "byzantine") ways of achieving them.
However, to be fair, there are usually ways to "get there" in SQLCLR, but they are neither easy nor pretty.
I will try to explain better what i intend to do. Maybe i'm going in the wrong direction and perhaps you can point me out the right and better solution.
This is exactly the right perspective that you need right now. What developers miss when they jump into SQLCLR, is that there are a LOT of other options, overshadowed and obscured by SQLCLR's hype, but very capable and often much easier to get working.
All i want is to pull xml messages into a private queue and i'm using SQL CLR to do that (my first approach was SSIS with Message Queue Task, but i gave up as i couldn't even establish the connection to private queue; see my post in http://www.sqlservercentral.com/Forums/Topic542430-148-1.aspx).
As i was saying, i'm using SQL CLR (SqlServer2008) and actually i can pull messages into my private queue (MSMQ). All i want is to take advantage of acknowledgement queue and deal with message status. The way i found to do that was to read xml from sql procedure and de-serialize it into a xmldocument in order to create a message queue and go from there...
Given the work that you've already put into the .net code, I think that your best bet is going to the the External Activator(EA). This is a little known free add-on from MS that is in many ways a complement to SQLCLR. Both are written in .net, however, instead of being a hosted library being called by SQL Server (which is what is really the cause of all of SQLCLR's limitations), it instead acts as a SQL Client calling SQL Server itself (you may have noticed that SQL Clients have none of these CLR restrictions). The problem of reversing the activation/invocation logic is handled with Event Notifications. And although it was released from MS for 2008, it works just fine on 2005 also (and is supported).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 25, 2009 at 8:56 am
Note: the explanation and announcement for External Activator is here: http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/36a58004-dbef-46ad-85a2-93563f3f8f0e
and you can download it from here: http://www.microsoft.com/downloads/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 25, 2009 at 9:16 am
If i create a private queue on the server side, i can easily connect to it, so why does not work with a remote private queue?
I got the above from your other thread and it is covered in the docs that it is not allowed and I have found a work around similar to what I think you could do but you still cannot run it hosted site because you still need reflection permissions.
http://www.scott-hill.co.uk/blog/
Kind regards,
Gift Peddie
August 25, 2009 at 10:53 am
Thanks for the replies!
RBarryYoung, External Activator - featured inside Service Broker - seems one of the rightest but not the easiest approach... SSIS Message Queue Task shouldn't get the job done more effectively and without much work?
Remember that i only wan't to push some xml messages into remote private MSMQ queue recurring to acknowledgement queue in order to obtain message status. The messages should be generated by SqlServer (XML) and submitted into remote private queue (by a stored procedure)... Simple as that.
Can you please point some links or some samples in order to compare which approach is better and implies less effort in terms of development?
Thanks for the advices once again!
PM
August 25, 2009 at 2:13 pm
If SSIS works for you, then go with it.
But External Activator is a *fairly* simple fix for many SQLCLR efforst that are falling on the rocks. I am posting from my hospital bed, so I have no access to examples, however, here is what I have done in the past:
----------
Essentially, I was trying to Send records to Oracle DB's call calling a SQLCLR proc that used OrcaleClient to execute corresponding login on the oracle side. All worked fine in Net, and in devel, but when we got to QA, we couldn't get it to work with EXTERNAL_ACCESS permissions (as documented), it would only woirk with UNSAFE which the client had specifically declined. Turns out that MS was wrong and although the OracleClient was classified as an EXTERNAL_ACCESS assembly, it could not be used that way and has now been re-classified by Microsoft as UNSAFE. That was no help to me, because my client still won't accept an UNSAFE assembly.
So what we did, is to rewrite it into an External Activator project. (ExternalActivator was not released yet, but their approach and our approach turned out to be the same thing).
In SQL Server, we changed the call fro a call to our CLR proc, to a proc that instead put the call parameters and data into a Service Broker queue and sent it.
On the .NET side, we rewote the SQLProcedure and SQLCLR assembly to instead be an executable program that runs as a service and connects to SQL as a client. Then we wrote a "dispatcher" loop that sinmply executed a T-SQL WAITFOR RECEIVE on the Service Broker Queue, would unpack the call parameters and data packed into the XML message, and then would call internally call the very same method that the SQLProcedue invocation was calling.
Now, when the SB message was sent, that would immediately wake up the WAITFOR to receive the new message, so it coud in turn immediatly continue the execution chain. The additional latency was minmal (milliseconds) and from the customer's point of view, there was no material difference.
The advantage for us in this approach, was that it persevered everything else that we had worked on: the functional content and action of both our SQL code and .NET code was the same (untouched in fact) all we had to do was to change the external "frame" and housekeeping of the .NET project that housed the CLR code, and to put a "shim" into the SQL proc call to add the SB send & queue in-between.
And best of all, that was the end of the previously "endless" series of SQLCLR "gotchas", from that point on it worked just like any other .Net project.
Now, that was the right solution for us. If your priorities and situation are different, then maybe it isn't right for you. Maybe using SSIS, is right and you can now get past what was blocking you in it before (I do not know enough about SSIS's MSMQ interfacing to say).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 26, 2009 at 2:22 am
Thanks for your replies RBarryYoung,
Before anything else, hope to hear from your good recovery soon...
I'll will search further into some External Activator implementations and at the same time take a close look into Message Queue Task. I'll have to choose one from another and don't have much time to compare nor run necessary tests.
Anyway, thanks for you help.
If someone else has a different perspective of the subject or some links that could help me to decide better between these two implementations, please share it.
Thanks guys!
PM
August 26, 2009 at 10:08 am
Ok Guys...
After some research, i've chosen the Messaque Queue Task approach (i'm more confortable with SSIS than Sql Broker's EA).
I've already implemented the necesssary line codes in order to push xml messages into remote private queue having its ack status of received/delivered.
Brief explanation:
1. In BIS create package script task and inside script task, connect to your remote private queue. Create a variable inside package of string type (scope should be at package level) - it will match the xml file path (xml that we want to send to private queue).
2. Inside script task implement the code in order to readxml from file (into xmldocument). Create your message and associate it into acknowledgment queue. Load your message with your xmldocument. Send the message to the queue.
3. Build your SSIS package
4. Invoke your package, let's say, from stored procedure with dtexec, passing by argument the path of the xml file.
I'm aware of security issues this approach implies, but i didn't find any other way of sending a parameter into SSIS package.
I suppose if you want, you could send xml directly into SSIS package and de-serialize in order to obtain xml. I didn't try it but i guess it should be possible too.
Nevertheless, thanks for the advices.
PM
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply