sql server tools: ado

We have an an application that was written using OLE DB (ADO) against a SQL 2000 Server that uses an Application role to give rights to the database objects. It connects, calls sp_setapprole and goes on. If the database needs to LOCK a record, it is creating a new ADO Connection and instantiating the Approle again. This model has been working fine up til now.

Now we are installing a SQL 2005 server for the latest version of the product we are working on and are running into an error. The error is

Error: 18059, Severity: 20, State: 1.
The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. This scenario is not supported. See "Impersonation Overview" in Books Online.

It's happening when the second ADO Connection for locking a record is being created and the sp_setapprole is being executed.

One of my questions is what is the problem with executing the approle on a different connection? Our code has not changed, so obviously SQL 2005 is doing something different. The other is What can we do to correct this?

Is the resource pooling different? We had problems in the beginning with approles and figured out through research that we needed to add OLE DB Services=-2 to the connection string to turn off resource pooling.

Is there an extra step to using Approles in SQL 2005?

Any help would be greatly appreciated as we need to resolve this ASAP.

Thanks,

David

SQL Server 2005 supports a richer impersonation model, and we made some changes to prevent potential for undesired escalation of privileges on both the existing and new impersonation mechanisms, including approles.

From your description I can guess that you are using a connection pool, and apparently the pool is misusing an impersonated context (approle). Does it still fail if you disable the connection pool?

If this is the case, one solution for using approles and connection pool would be to use the new @.fCreateCookie and @.cookie parameters in sp_setapprole along with sp_unsetapprole (http://msdn2.microsoft.com/en-us/library/ms365415.aspx). This allows the client code to obtain a server-generated cookie that can be used to revert to the original context before resetting the connection.

Unfortunately I haven’t experienced any behavior like the one you described; at this point I cannot really tell if the behavior you are experiencing is a problem on the way the client code is calling SQL Server, if this is a scenario where we decided to change the behavior of SQL Server to prevent an undesired escalation of privileges or a real bug in SQL Server 2005.

Please, let us know if the workaround I suggested work, if not I would like to understand more about the scenario so I can try to reproduce this behavior on my test environment.

Thanks a lot,

-Raul Garcia
SDE/T

SQL Server Engine

|||

We don't need to revert to the original context, so It's supposed to be disabling the resource pooling by adding "OLE DB Services=-2" on the connection string of the ADOConnection. We found the problem with connection pooling coding against SQL 2000.

Is that still effective in a SQL 2005 environment? Has the setting changed? It seems like something isn't set right since this worked in SQL 2000 and doesn't in SQL 2005.

Thanks,

David

|||

To clarify this situation more....

We aren't using .NET and the SqlClient for this app.

We are running a Win32 application, connecting to the SQL Server using ADO (MDAC 2.7 or 2.8), holding this connection open until the application closes at which point we close the connection. We are turning on ROW Level locking in SQL 2000 (not sure if this has been done or not on the SQL 2005 server) and using a separate connection with "locking hints" on the tables when a lock call is made to force a model we have to use for backwards compatability. (i.e.

select * from authors as a WITH (NOLOCK) { normal get }

select * from authors as a with (UPDLOCK) where identitycol = 1 { get with a lock for updating }

)

I wasn't sure how SQL Server 2005 would handle connectivity from the SQL 2000 client with sp3.

|||

Ok. Found the problem.

In certain circumstances, the connection string was being set with the OLE DB Services=-2 like it was supposed to be. Therefore the Pooling was still on and causing the problem.

Thanks for the assistance!

Hi !
I have problem with locking concept in my application (ADO.NET , C#).
Application has several documents which looks like Order.
My question is what and how should I lock those documents.
1.Lock document as a whole (Order + OrderLines) so another user can't work
on this document (pessimistic lock)?
1.1. How to implement this on SQL server ?
2.Should I use optimistic locking on Order and OrderLines ?
3.If I use optimistic locking on OrderLines should I each time update
timestamp of Order
and by each insert, update and read of OrderLine check whether somebody
has changed this Order and/or OrderLine which is both possible ?
Any thoughts are appreciated!"Marek" <marek@.home.puton.cz> wrote in message
news:%23OCPpamIGHA.3192@.TK2MSFTNGP10.phx.gbl...
> Hi !
>
> I have problem with locking concept in my application (ADO.NET , C#).
> Application has several documents which looks like Order.
> My question is what and how should I lock those documents.
>
> 1.Lock document as a whole (Order + OrderLines) so another user can't work
> on this document (pessimistic lock)?
> 1.1. How to implement this on SQL server ?
my advice: forget about #1. what #1 really means? either selecting the data
with (updlock,holdlock) or something similar, and keeping the transaction
open for indefinite amount of time (no, no, no, and no), or setting some
flag, marking that the document is being edited by a client, which is
somewhat better, but still leaves you with the housekeeping problems that
have to be dealt with from the application (and relying on application to
take care of things is generally not a good idea).

> 2.Should I use optimistic locking on Order and OrderLines ?
i would. and i do.

> 3.If I use optimistic locking on OrderLines should I each time update
> timestamp of Order
> and by each insert, update and read of OrderLine check whether somebody
> has changed this Order and/or OrderLine which is both possible ?
a timestamp column will be updated automatically each time data in the row
is updated, you just have to check whether it's value is same as what it was
when the row was read.
it's just my opinion, of course..
dean|||
> a timestamp column will be updated automatically each time data in the row
> is updated, you just have to check whether it's value is same as what it
> was when the row was read.
> it's just my opinion, of course..
> dean
Tnx Dean, but I believe that you answer is over simplified
and does not address all the issues ...
What should happened when orderline is updated ?
should order also be touched (so timestamp can change)
Is this sequence right when updating Orderline ?
1.Get TimeStamp of Order
2. Get TimeStamp of OrderLine
3. User changes orderline
4. Check if Order was changed meanwhile
4. 1 If yes there is a conflict since somebody changed order while I was
updating OrderLine
5. Check if OrderLine was changed
5.1 If yes there is a conflict since somebody changed OrderLine line while I
was updating OrderLine
6. Update OrderLine
7. Touch Order
or there is not need to check for Order Timestamp
when updating OrderLine in this case|||If you acquire locks in the correct order and hold them until the end of the
transaction, no other user will be able to change any rows that you have
locked until the transaction is either committed or rolled back.
ML
http://milambda.blogspot.com/|||hi, marek
yes, it was somewhat simplified, i agree. the actual implementation depends
on the actual business requirements (eg, is it ok that two people work with
different lineitems of the same document?), so this sequence could be
right - provided that everything from (and including) #4 is isolated inside
a transaction.
dean
"Marek" <marek@.home.puton.cz> wrote in message
news:uQlLHnnIGHA.3060@.TK2MSFTNGP10.phx.gbl...
>
> Tnx Dean, but I believe that you answer is over simplified
> and does not address all the issues ...
> What should happened when orderline is updated ?
> should order also be touched (so timestamp can change)
> Is this sequence right when updating Orderline ?
> 1.Get TimeStamp of Order
> 2. Get TimeStamp of OrderLine
> 3. User changes orderline
> 4. Check if Order was changed meanwhile
> 4. 1 If yes there is a conflict since somebody changed order while I was
> updating OrderLine
> 5. Check if OrderLine was changed
> 5.1 If yes there is a conflict since somebody changed OrderLine line while
> I was updating OrderLine
> 6. Update OrderLine
> 7. Touch Order
> or there is not need to check for Order Timestamp
> when updating OrderLine in this case
>
>|||Well it's interesting how would you solve the problem
when two people must not work with 2 different Orderlines
of the same Order ? Should we use pessimistic locking than ?
Another question that arises when considering scenario below is
how many roundtrips to SQL requires such a solution ?
Steps 1,2 - one roundtrip
Steps 4,5 - one roundtrip
Steps 6,7 - one roundtrip
Can this be smaller number than 3 ?
> yes, it was somewhat simplified, i agree. the actual implementation
> depends on the actual business requirements (eg, is it ok that two people
> work with different lineitems of the same document?), so this sequence
> could be right - provided that everything from (and including) #4 is
> isolated inside a transaction.
>|||not more than 2 roundtrips - 1&2 is one, 4-7 the other one, inside a stored
procedure, inside a transaction.
dean
"Marek" <marek@.home.puton.cz> wrote in message
news:OIqObNoIGHA.524@.TK2MSFTNGP09.phx.gbl...
> Well it's interesting how would you solve the problem
> when two people must not work with 2 different Orderlines
> of the same Order ? Should we use pessimistic locking than ?
> Another question that arises when considering scenario below is
> how many roundtrips to SQL requires such a solution ?
> Steps 1,2 - one roundtrip
> Steps 4,5 - one roundtrip
> Steps 6,7 - one roundtrip
> Can this be smaller number than 3 ?
>
>
>|||Tnx. Dean
That looks like a reasonable solution
with minimum overhead on SQL server side.
And what would be theoretical scenario:
1. when two people must not work with 2 different Orderlines of the same
order
2. two people must work not with the same Order at all ?
Does this require pessimistic lock and an open connection during
order/orderline update or is there better simpler solution ?
I have two solutions on my mind:
1. Lock the whole Order and child Orderlines with hold lock so nobody
can update those records.
2. Create LockingTable (RecordId, UserId) and hold connection open
on this record in LockingTable so if program crashes
SQL server will release lock. Before reading record With 'SELECT (NOLOCK)'
read
wheather record is locked and if it is tell the user who locks the record.
But both solutions require open connection ?
I'm I missing something ?
"Dean" <dvitner@.nospam.gmail.com> wrote in message
news:uxNdmVoIGHA.1836@.TK2MSFTNGP11.phx.gbl...
> not more than 2 roundtrips - 1&2 is one, 4-7 the other one, inside a
> stored procedure, inside a transaction.
> dean
> "Marek" <marek@.home.puton.cz> wrote in message
> news:OIqObNoIGHA.524@.TK2MSFTNGP09.phx.gbl...
>|||hi again,
as i see it, this whole optimistic vs pessimistic thing is really a business
problem. in most cases, unless you're dealing with some very chaotic
organization, it is well known who can do what with what data. it is not
unusual to have an owner for the document, and only that person is permitted
to make updates to the specific document. this type of scenario (involving
update conflicts) is rather unlikely in the real world, imo. sometimes it's
practically impossible, sometimes it's desirable, even required (why not let
several people do the data entry on an inventory list?). to answer the
question 'how to deal with it?' is really up to your customers. ask them, is
it possible at all? how often could it happen? how severe will the
consequences be? most of them, in my experience, could live with an update
conflict here and there - as long as you can tell them what happened, and
who was competing with them. auditing is here much more important than
locking.
technically speaking, would you really want to let the user opet a
transaction, select the data and place and hold update lock on the data,
then keep the data locked for nobody knows how long (he might go to lunch,
or go home or whatever - you have no control over it), and eventually at
some point in time decide to end the transaction, with or without any
change? first of all, transactions should be short-lived - started as late
as possible, and ended as soon as possible. they eat up resorces on server,
and keep others from accessing data. i can't think of a real-world situation
that would justify such a scenario.
you have come to a rather good practical solution yourself (in your previous
post) - why not use it?
dean
"Marek" <marek@.home.puton.cz> wrote in message
news:eXoRwooIGHA.3144@.TK2MSFTNGP11.phx.gbl...
> Tnx. Dean
> That looks like a reasonable solution
> with minimum overhead on SQL server side.
> And what would be theoretical scenario:
> 1. when two people must not work with 2 different Orderlines of the same
> order
> 2. two people must work not with the same Order at all ?
> Does this require pessimistic lock and an open connection during
> order/orderline update or is there better simpler solution ?
> I have two solutions on my mind:
> 1. Lock the whole Order and child Orderlines with hold lock so nobody
> can update those records.
> 2. Create LockingTable (RecordId, UserId) and hold connection open
> on this record in LockingTable so if program crashes
> SQL server will release lock. Before reading record With 'SELECT (NOLOCK)'
> read
> wheather record is locked and if it is tell the user who locks the record.
> But both solutions require open connection ?
> I'm I missing something ?
>
>
> "Dean" <dvitner@.nospam.gmail.com> wrote in message
> news:uxNdmVoIGHA.1836@.TK2MSFTNGP11.phx.gbl...
>|||Thanks Dean !
I don't have such situation , but I was just thinking aloud.
Currently in my firm there is an old proprietary system which uses
pessimistic
locking and it works fine, so I was thinking how this can be implemented on
SQL server.
Since the whole architecture of ADO.NET does not encourage pessimistic
locking
and there is no business need to support it I will definitely use solution
to that we came up in previous posts. Thanky you again for a thoughtful
posts
that have broadened my "data access" sights !
"Dean" <dvitner@.nospam.gmail.com> wrote in message
news:unvH%23hqIGHA.2708@.tk2msftngp13.phx.gbl...
> hi again,
> as i see it, this whole optimistic vs pessimistic thing is really a
> business problem. in most cases, unless you're dealing with some very
> chaotic organization, it is well known who can do what with what data. it
> is not unusual to have an owner for the document, and only that person is
> permitted to make updates to the specific document. this type of scenario
> (involving update conflicts) is rather unlikely in the real world, imo.
> sometimes it's practically impossible, sometimes it's desirable, even
> required (why not let several people do the data entry on an inventory
> list?). to answer the question 'how to deal with it?' is really up to your
> customers. ask them, is it possible at all? how often could it happen? how
> severe will the consequences be? most of them, in my experience, could
> live with an update conflict here and there - as long as you can tell them
> what happened, and who was competing with them. auditing is here much more
> important than locking.
> technically speaking, would you really want to let the user opet a
> transaction, select the data and place and hold update lock on the data,
> then keep the data locked for nobody knows how long (he might go to lunch,
> or go home or whatever - you have no control over it), and eventually at
> some point in time decide to end the transaction, with or without any
> change? first of all, transactions should be short-lived - started as late
> as possible, and ended as soon as possible. they eat up resorces on
> server, and keep others from accessing data. i can't think of a real-world
> situation that would justify such a scenario.
> you have come to a rather good practical solution yourself (in your
> previous post) - why not use it?
> dean
> "Marek" <marek@.home.puton.cz> wrote in message
> news:eXoRwooIGHA.3144@.TK2MSFTNGP11.phx.gbl...
>

Thursday, March 8, 2012

application rolls and ADO.Net

Wednesday, March 7, 2012

Application Roles in SQL 2005

Friday, February 24, 2012

Application - document locking

sql server tools

Blog Archive

About Me