Re: Pessimistic locking with approles.

From: Mary Chipman (mchip@nomail.please)
Date: 06/13/02 

From: Mary Chipman <mchip@nomail.please>
Date: Thu, 13 Jun 2002 15:20:45 -0400

The "fix" is to disable connection pooling in ADO in the connection
string, and you'd have the same issue whether you used integrated
security or SQLS security, and it has nothing whatsoever to do with
pessimistic locking, either. See:
PRB: SQL Application Role Errors with OLE DB Resource Pooling
(Q229564)
http://support.microsoft.com/support/kb/articles/Q229/5/64.ASP

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

On Thu, 13 Jun 2002 09:00:19 +1200, "Paul Ritchie"
<pritchie@xtraREMOVE.coREMOVE.nz> wrote:

><I posted this in the ADO NG yesterday but received no replies.>
>
>I have rewritten our ISAM based application which uses pessimistic
>record locking. It now uses ADO, server side cursors, application roles,
>and SQL Server. Any of those individually and most combinations thereof
>seem to work fine, but not the combination I'm using!
>
>My problem is this: say you have an ADO recordset open on a table Employee
>with a pessimistic record lock on a connection that is using an AppRole.
>(Pessimistic locking requires a server side cursor.) Before you post
>the changes to Employee, maybe you wish to validate whether one of the
>fields you hope to save to the Employee table is valid, and for this you use
>a SQL query on another Command or recordset object.
>
>To paraphrase another post last year, it seems that "when you have a
>server-side cursor open with results pending & you are trying to create
>another recordset, ADO will automatically open a new connection".
>
>However when it does this, it does not apply the AppRole to the newly
>opened connection which therefore seems to become subject to the rights of
>the original login. Needless to say, these are minimal.
>
>I would be really keen on any suggestions on how to get around this.
>Pessimistic locking is fundamental to the way the application works, and
>pessimistic locking requires a server side cursor.
>
>I understand using AppRoles makes security management extremely simple where
>their use is appropriate, however maybe there is another security
>arrangement that is nearly as good for users of my application?
>
>BTW One suggestion was that I create a db user with a hidden password (the
>same as my application & AppRole hides it now) and attach that user to the
>db_owner database role? Are there any major downsides to that?
>
>It seems that I would be forced to use SQL Server authentication, but apart
>from that it's almost the same as setting an AppRole which is assigned to
>the db_owner role?
>
>TIA,
>
>Paul Ritchie
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

Relevant Pages

  • Re: SQL incorporation with Excel
    ... Excel 2002 VBA by Bullen, Green, Bovey and Rosenberg has a fairly good ... Set a reference to ADO 2.7 in your vb project. ... connection string to find the correct way to connect. ... Our accounting system uses an SQL ...
    (microsoft.public.excel.programming)
  • ISAM & ADO
    ... in the SQL query instead of entering a destination table name use an ISAM name to interact with a seperate DB. ... Additionally, if you would like to create a file based CSV, Excel 2003/2007, Access 2003/2007 database via the ADO connection, that can be done using the ADOX object. ... Some issues I ran into: First: I was sending a CommandType Enum ...
    (comp.databases.ms-access)
  • Re: Error -2147168227 Cannot create new transaction because capacity was exceeded.
    ... Try to run SQL profiler, which ships with SQL Server to see how many ... > object and there is only 1 instance per application of the connection ... Prior to that I've just closed the ADO recordset object. ...
    (microsoft.public.data.ado)
  • Re: ADO Connection.Execute Method leaves Open SQL Process
    ... I am trying to improve a VB6 app that accesses SQL Server data via ADO ... The connection that invokes the Execute method is created, opened, ...
    (microsoft.public.vb.database.ado)
  • Re: Access 2007 ADP (Runtime) and SQL Express 2005
    ... I have removed trusted connection from the connection syntax. ... Do you know of any resources on the internet where I can progress further ... SQL express on a network with the ADP clients connecting to it. ... The ConnectionString's security part simply does not make ...
    (microsoft.public.access.adp.sqlserver)