A made a brokermethod just like in the helloworld_clr example in de service
broker example dir in sql 2005. The brokermethod receives a message and trie
s
to deserialize the message body which is a list of customer objects.
But when I try to deserialize I get the following exception.
It’s about something with permissions on an assembly. But I don’t have a
ny
experience with that.
Could someone tell me what I’m doing wrong?
Any help would be greatly appreciated.
CustomerMessage exception:
System.Security.SecurityException:
Request for the permission of type
'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0,
Culture=neutral,
PublicKeyToken=b77a5c561934e089' failed.
at
System.Runtime.Serialization.Formatters.Binary.ObjectReader.CheckSecurity(Pa
rseRecord pr)
at
System.Runtime.Serialization.Formatters.Binary.ObjectReader.ParseObject(Pars
eRecord pr)
at
System.Runtime.Serialization.Formatters.Binary.ObjectReader.Parse(ParseRecor
d
pr)
at
System.Runtime.Serialization.Formatters.Binary.__BinaryParser.ReadObjectWith
MapTyped(BinaryObjectWithMapTyped record)
at
System.Runtime.Serialization.Formatters.Binary.__BinaryParser.ReadObjectWith
MapTyped(BinaryHeaderEnum binaryHeaderEnum)
at System.Runtime.Serialization.Formatters.Binary.__BinaryParser.Run()
at
System.Runtime.Serialization.Formatters.Binary.ObjectReader.Deserialize(Head
erHandler
handler, __BinaryParser serParser, Boolean fCheck, Boolean isCrossAppDomain,
IMethodCallMessage methodCallMessage)
at
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Deserialize(S
tream
serializationStream, HeaderHandler handler, Boolean fCheck, Boolean
isCrossAppDomain, IMethodCallMessage methodCallMessage)
at
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Deserialize(S
tream serializationStream)
at testservice.services.CustomerService.CustomerMessage(Message msgReceived,
SqlConnection connection, SqlTransaction transaction)
The action that failed was: Demand
The type of the first permission that failed was:
System.Security.Permissions.SecurityPermission
The Zone of the assembly that failed was: MyComputerIs the broker method activated (WITH ACTIVATION (...) on the queue) or are
you invoking it from a user connection?
Activation has certain security restrictions because of the EXECUTE AS
context, see this blog
http://blogs.msdn.com/remusrusanu/a.../12/512085.aspx
Also, what kind of serialization are you using? Inside SQL Server there are
certain restrictions as the ordinary Xml serialization cannot be used, see
this blog http://blogs.msdn.com/sqlclr/archiv.../25/Vineet.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"arn0" <arn0@.discussions.microsoft.com> wrote in message
news:E09E3890-12B6-491E-B990-1BECE84C5F62@.microsoft.com...
>A made a brokermethod just like in the helloworld_clr example in de service
> broker example dir in sql 2005. The brokermethod receives a message and
> tries
> to deserialize the message body which is a list of customer objects.
> But when I try to deserialize I get the following exception.
> It's about something with permissions on an assembly. But I don't have any
> experience with that.
> Could someone tell me what I'm doing wrong?
> Any help would be greatly appreciated.
> CustomerMessage exception:
> System.Security.SecurityException:
> Request for the permission of type
> 'System.Security.Permissions.SecurityPermission, mscorlib,
> Version=2.0.0.0,
> Culture=neutral,
> PublicKeyToken=b77a5c561934e089' failed.
> at
> System.Runtime.Serialization.Formatters.Binary.ObjectReader.CheckSecurity(
ParseRecord
> pr)
> at
> System.Runtime.Serialization.Formatters.Binary.ObjectReader.ParseObject(Pa
rseRecord
> pr)
> at
> System.Runtime.Serialization.Formatters.Binary.ObjectReader.Parse(ParseRec
ord
> pr)
> at
> System.Runtime.Serialization.Formatters.Binary.__BinaryParser.ReadObjectWi
thMapTyped(BinaryObjectWithMapTyped
> record)
> at
> System.Runtime.Serialization.Formatters.Binary.__BinaryParser.ReadObjectWi
thMapTyped(BinaryHeaderEnum
> binaryHeaderEnum)
> at System.Runtime.Serialization.Formatters.Binary.__BinaryParser.Run()
> at
> System.Runtime.Serialization.Formatters.Binary.ObjectReader.Deserialize(He
aderHandler
> handler, __BinaryParser serParser, Boolean fCheck, Boolean
> isCrossAppDomain,
> IMethodCallMessage methodCallMessage)
> at
> System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Deserialize
(Stream
> serializationStream, HeaderHandler handler, Boolean fCheck, Boolean
> isCrossAppDomain, IMethodCallMessage methodCallMessage)
> at
> System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Deserialize
(Stream
> serializationStream)
> at testservice.services.CustomerService.CustomerMessage(Message
> msgReceived,
> SqlConnection connection, SqlTransaction transaction)
> The action that failed was: Demand
> The type of the first permission that failed was:
> System.Security.Permissions.SecurityPermission
> The Zone of the assembly that failed was: MyComputer|||Hello arn0,
> A made a brokermethod just like in the helloworld_clr example in de
> service broker example dir in sql 2005. The brokermethod receives a
> message and tries to deserialize the message body which is a list of
> customer objects.
> But when I try to deserialize I get the following exception.
> It’s about something with permissions on an assembly. But I don’t have
> any
> experience with that.
> Could someone tell me what I’m doing wrong?
> Any help would be greatly appreciated.
> CustomerMessage exception: System.Security.SecurityException: Request
> for the permission of type
> 'System.Security.Permissions.SecurityPermission, mscorlib,
> Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
> failed.
[SNIP]
> testservice.services.CustomerService.CustomerMessage(Message
> msgReceived, SqlConnection connection, SqlTransaction transaction)
> The action that failed was: Demand The type of the first permission
> that failed was: System.Security.Permissions.SecurityPermission The
> Zone of the assembly that failed was: MyComputer
Adding this to the .CLR and .ServiceBroker newsgroups as folks in those grou
ps
might be able to add more.
The example in question doesn't deserialize an object graph, at least from
what I can today. So unless you can post the code in question, we're probabl
y
not going to be help you much. I've done some work with simple serialization
of objects with SQLCLR and haven't run into this yet. Chances are this isn't
an object with the permissions of the assembly. You can change the permissio
n
level of an assembly when you catalog it by adding "with permission_set =
" and one of SAFE, EXTERNAL_ACCESS or UNSAFE. Try unsafe first here, but
I doubt its going to get your much as I'm guessing you're doing something
with CAS that SQLCLR doesn't like.
Oh BTW, are you trying to load the base objects from the GAC?
PS: send messages, not object graphics -- just had to get that out of my
system... :)
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||the customer object comes from a assambly I registered this assembly in the
db with this command
create ASSEMBLY CustomerAssembly
FROM 'd:\development\Customer.dll'
with PERMISSION_SET = unsafe
GO
this is the brokermethod code
<BrokerMethod("CustomerMessage")> _
Public Sub CustomerMessage(ByVal msgReceived As Message, ByVal
connection As SqlConnection, ByVal transaction As SqlTransaction)
writeToLog("CustomerMessage start", connection, transaction)
Try
Dim customerArray As List(Of Customer)
Dim bfmt As BinaryFormatter = New BinaryFormatter()
Dim cmd As SqlCommand
msgReceived.Body.Position = 0
customerArray = bfmt.Deserialize(msgReceived.Body)
cmd = connection.CreateCommand
cmd.Transaction = transaction
cmd.CommandText = "INSERT INTO [arnotest].[dbo].[customer]
([name],[lastname]) VALUES(@.name,@.lastname)"
Dim name As IDataParameter = cmd.Parameters.Add("@.name",
SqlDbType.NVarChar)
Dim lastname As IDataParameter = cmd.Parameters.Add("@.name",
SqlDbType.NVarChar)
For Each Item As Customer In customerArray
name.Value = Item.name
lastname.Value = Item.lastname
cmd.ExecuteNonQuery()
Next
Catch ex As Exception
writeToLog(String.Concat("CustomerMessage exception: ",
ex.ToString), connection, transaction)
End Try
End Sub|||Hello arn0,
> the customer object comes from a assambly I registered this assembly
> in the db with this command
> create ASSEMBLY CustomerAssembly
> FROM 'd:\development\Customer.dll'
> with PERMISSION_SET = unsafe
> GO
> this is the brokermethod code
> <BrokerMethod("CustomerMessage")> _
> Public Sub CustomerMessage(ByVal msgReceived As Message, ByVal
> connection As SqlConnection, ByVal transaction As SqlTransaction)
> writeToLog("CustomerMessage start", connection, transaction)
> Try
> Dim customerArray As List(Of Customer)
> Dim bfmt As BinaryFormatter = New BinaryFormatter()
> Dim cmd As SqlCommand
> msgReceived.Body.Position = 0
> customerArray = bfmt.Deserialize(msgReceived.Body)
> cmd = connection.CreateCommand
> cmd.Transaction = transaction
> cmd.CommandText = "INSERT INTO [arnotest].[dbo].[customer]
> ([name],[lastname]) VALUES(@.name,@.lastname)"
> Dim name As IDataParameter = cmd.Parameters.Add("@.name",
> SqlDbType.NVarChar)
> Dim lastname As IDataParameter =
> cmd.Parameters.Add("@.name",
> SqlDbType.NVarChar)
> For Each Item As Customer In customerArray
> name.Value = Item.name
> lastname.Value = Item.lastname
> cmd.ExecuteNonQuery()
> Next
> Catch ex As Exception
> writeToLog(String.Concat("CustomerMessage exception: ",
> ex.ToString), connection, transaction)
> End Try
> End Sub
Okay, so this is very similar (on the surface) to my lightweight example
except that I'm using XML rather than binary serialization. See: [0].
What does the Customer Class look like?
[0]: http://www.sqljunkies.com/WebLog/kt...serialized.aspx
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi arn0,
Sounds like you may be running into "SQL Server doesn't allow dynamic
serializer assemblies" behavior. If you're using "add web reference" you
need to pregenerate the serializer DLL (using sgen.exe) and catalog it
separately. The serializer needs to match the class its "serializing for"
WRT version.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"arn0" <arn0@.discussions.microsoft.com> wrote in message
news:D5AE8A90-EA04-4484-A5E2-EABD04584CFA@.microsoft.com...
> the customer object comes from a assambly I registered this assembly in
> the
> db with this command
> create ASSEMBLY CustomerAssembly
> FROM 'd:\development\Customer.dll'
> with PERMISSION_SET = unsafe
> GO
> this is the brokermethod code
> <BrokerMethod("CustomerMessage")> _
> Public Sub CustomerMessage(ByVal msgReceived As Message, ByVal
> connection As SqlConnection, ByVal transaction As SqlTransaction)
> writeToLog("CustomerMessage start", connection, transaction)
> Try
> Dim customerArray As List(Of Customer)
> Dim bfmt As BinaryFormatter = New BinaryFormatter()
> Dim cmd As SqlCommand
> msgReceived.Body.Position = 0
> customerArray = bfmt.Deserialize(msgReceived.Body)
> cmd = connection.CreateCommand
> cmd.Transaction = transaction
> cmd.CommandText = "INSERT INTO [arnotest].[dbo].[customer]
> ([name],[lastname]) VALUES(@.name,@.lastname)"
> Dim name As IDataParameter = cmd.Parameters.Add("@.name",
> SqlDbType.NVarChar)
> Dim lastname As IDataParameter = cmd.Parameters.Add("@.name",
> SqlDbType.NVarChar)
> For Each Item As Customer In customerArray
> name.Value = Item.name
> lastname.Value = Item.lastname
> cmd.ExecuteNonQuery()
> Next
> Catch ex As Exception
> writeToLog(String.Concat("CustomerMessage exception: ",
> ex.ToString), connection, transaction)
> End Try
> End Sub|||Hello Bob,
> Sounds like you may be running into "SQL Server doesn't allow dynamic
> serializer assemblies" behavior. If you're using "add web reference"
> you need to pregenerate the serializer DLL (using sgen.exe) and
> catalog it separately. The serializer needs to match the class its
> "serializing for" WRT version.
Nope, its not a Web Reference -- first thing I checked. As you can see from
the code provided, he using BinaryFormatter on the instance. But, is SGEN
only XML Serialization specific? If not, this is proably worth doing.
My current thought is that there's a CAS setting in the class (or something
that the class inherits) has a prohibited behavior.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
