CLR Security Permissions on a sql 2005 server, service broker

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/

CLR procedure to create entries in eventlog

Hi,

I created a SP in C# to write entries to the eventlog.

I works fin but I need to put the security level in External_Access.

Is there a way to do the same with permission level on save by making use of the
System.Diagnostics.EventLogPermission class or is externale_access to only possible way to use it.

Unfortunately you would need external access for this and you wouldnt be able to use SAFE. External Access provides you equal reliablility guarantees as SAFE but however it is a high privilege from security perspective.

Thanks,
-Vineet.

CLR problems with security under ADO.NET and SQL Server 2005

Ok I'm on this... in T-SQL Say I have TableA and it has no select
permissions on it for the user... but the user has a stored procedure with
execute permission on it and contents of it is SELECT * from TableA... well
of course this executes! and returns the TableA contents... where doing a
SELECT * Fromt TableA in a stand alone query returns a permission error...
now when I do a similar thing in the new CLR compiled stored procedures
using an ADO.NET Command object... with SELECT * FROM TableA it comes back
with the permission error that I cant select from tablea... why?! if i can
do it in T-SQL why can't I in a CLR stored procedure? which is more secure
to start with then a plain text T-SQL statement! I'm trying to convert some
T-SQL procs to ADO.NET's CLR store procs on SQL Server 2005 but this hitch
kinda put everything im doing at a hault because i cant do anything if i
cant get select permission! and security wise i dont want to give people
select permission on tables... anything to help with this problem? thanks!Short answer:
use EXECUTE AS OWNER clause in your CREATE PROC statement.
Longer answer:
This works by default in TSQL due to ownership chaining -- if the table and
procedure are owned by the same entity, then user permissions are not
checked against the table, just against the procedure. In Sql Server 2005,
there is a new paradigm using the EXECUTE AS clause on various DDL
statements. This allows you to specify that the code should execute with
the credentials of a particular entity, including a couple of dynamic ids
(USER, OWNER, SELF). For better security, .Net procedures execute as USER
by default. Executing as OWNER is the closest match to the TSQL default
ownership chaining.
~Alazel
Alazel Acheson
Software Developer
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian Henry" <nospam@.nospam.com> wrote in message
news:eia7VRZMGHA.2336@.TK2MSFTNGP12.phx.gbl...
> Ok I'm on this... in T-SQL Say I have TableA and it has no select
> permissions on it for the user... but the user has a stored procedure with
> execute permission on it and contents of it is SELECT * from TableA...
> well of course this executes! and returns the TableA contents... where
> doing a SELECT * Fromt TableA in a stand alone query returns a permission
> error... now when I do a similar thing in the new CLR compiled stored
> procedures using an ADO.NET Command object... with SELECT * FROM TableA it
> comes back with the permission error that I cant select from tablea...
> why?! if i can do it in T-SQL why can't I in a CLR stored procedure? which
> is more secure to start with then a plain text T-SQL statement! I'm trying
> to convert some T-SQL procs to ADO.NET's CLR store procs on SQL Server
> 2005 but this hitch kinda put everything im doing at a hault because i
> cant do anything if i cant get select permission! and security wise i dont
> want to give people select permission on tables... anything to help with
> this problem? thanks!
>|||thanks for the clarification!
"Alazel Acheson [MS]" <alazela@.online.microsoft.com> wrote in message
news:%236lVdTbMGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Short answer:
> use EXECUTE AS OWNER clause in your CREATE PROC statement.
> Longer answer:
> This works by default in TSQL due to ownership chaining -- if the table
> and procedure are owned by the same entity, then user permissions are not
> checked against the table, just against the procedure. In Sql Server
> 2005, there is a new paradigm using the EXECUTE AS clause on various DDL
> statements. This allows you to specify that the code should execute with
> the credentials of a particular entity, including a couple of dynamic ids
> (USER, OWNER, SELF). For better security, .Net procedures execute as USER
> by default. Executing as OWNER is the closest match to the TSQL default
> ownership chaining.
> --
> ~Alazel
> Alazel Acheson
> Software Developer
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Brian Henry" <nospam@.nospam.com> wrote in message
> news:eia7VRZMGHA.2336@.TK2MSFTNGP12.phx.gbl...
>

CLR Option :: DBA Standpoint

What can be the implications of enabling CLR option; especially in a shared hosting environment? What are security considerations? And what should a database administrator be aware of?

Pointers to relevant information will be highly appreciated.

Check out the following webcast, it has great pointers, FYI, and additional articles:

http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032271687&EventCategory=3&culture=en-US&CountryCode=US

CLR Integration Security

Hi,
In BOL it is mentioned that we can control the access of users to file
system resources by using impersonation (WindowsIdentity.Impersonate).
Imagine I have a SP that creates a file on disk but the user sends the path
as a parameter. Although I can control the user to access only to particular
path, but how can I control the developer herself? As a DBA, I need to
control the developer while they can write their codes without using
"WindowsIdentity.Impersonate". If they don't do impersonation, they can work
under the security account of SQL Server.
I want to give permissions based on the user accounts that they log into
their Windows.
Thanks in advance,
LeilaLeila wrote:
> Hi,
> In BOL it is mentioned that we can control the access of users to file
> system resources by using impersonation (WindowsIdentity.Impersonate).
> Imagine I have a SP that creates a file on disk but the user sends the pat
h
> as a parameter. Although I can control the user to access only to particul
ar
> path, but how can I control the developer herself? As a DBA, I need to
> control the developer while they can write their codes without using
> "WindowsIdentity.Impersonate".
I do not really understand the question. What I mean is that; if the
developer writes the code without Impersonate, then everyone runs
without impersonation - so IMHO, it is not a question about dev vs.
regular users. If you are worried about dev doing things they shouldn't
do - well make sure they only have necessary rights on dev machines and
not production boxes.

> If they don't do impersonation, they can work
> under the security account of SQL Server.
> I want to give permissions based on the user accounts that they log into
> their Windows.
Well, I would assume that the account SQL runs under has very low
priviliges, and if you want to give rights according to the log-ins -
require that the code has Impersonate. Also, remember that running under
the account of SQL is only applied when you execute out of SQL Server,
i.e. file I/O etc.
Niels