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...
>

Client-side problem with mirroring

Hi!

I posted this on the adonet forum but didn't get any response so I might as well post it here. Hope anyone can help me.

-

I have set up mirroring and trying to get it to work from my .NET
application. Mirroring seems to work ok.
When I do a manual failover the witness is updated properly in the
sys.database_mirroring_witnesses table so it seems to be configured
correctly. I also can shutdown the master database and the mirror takes over.

My application is configured with Server set to my master database and
FailOverPartner to my mirror database.

If I am connected to the master database and do a manual fail-over all my
attempts to communicate with the database fail with an exception, even after
repeated retries.
(System.Data.SqlClient.SqlException: A transport-level error has occurred
when sending the request to the server. (provider: TCP Provider, error: 0 -
An existing connection was forcibly closed by the remote host.).)

If I do a manual fail-over and restart my application it connects to the
mirror database and everything is working. If I now do a manual fail-over I
get the same exception, but after a few retries it connects to the master
database.

The field Connection.Datasource always shows the master database regardless
if it's connected to the mirror.

What am I doing wrong? Is this how it's supposed to work?

-

I have been pulling my hair over this problem and I just can't get it to work.

I'm using SQL Server Authentication to access the database and I can connect
to the master when it's acting as principal and I can connect to the mirror
when it's acting as principal, but when doing a fail-over, manual or shutting
down the master server it never tries to connect automatically to the mirror
even if it's changed role to principal.

Do the client need access to the witness in any way?

I'm using version 2.0.50727 of System.Data.dll.

I'm wondering if when you say "master database", you are refering to the system database called master or the principal database. It isn't clear. So I am going to assume that you mean "principal" database when you say "master" database, but your comments are very confusing. Also remember that Principal and Mirror are roles, it is better to state Server A and Server B and the role they are currently in. Also, when you say master database, everyone in the world thinks you are refering to the system database called "master," which cannot be mirrored. We'll get it figured out. :)

"My application is configured with Server set to my master database and
FailOverPartner to my mirror database."

You mean Server set to Server A and Failover_Partner is set to Server B, correct? You should not be putting database names in where there should be server names...

"Do the client need access to the witness in any way?"

No.

"I'm using version 2.0.50727 of System.Data.dll."

Can you verify that you are using the same System.data.dll as in this KB? http://support.microsoft.com/kb/912151/

"but after a few retries it connects to the master
database."

By retries, do you mean that the application is processing the network error and issues a reconnect?

Thanks,

Mark