Re: Application Role

From: Mary Chipman (mchip_at_online.microsoft.com)
Date: 12/27/04 

Date: Mon, 27 Dec 2004 09:14:13 -0500

In addition to what Erland said, you are going to have intermittent
problems using application roles with Access unless you turn off
connection pooling in the connection string and test to make sure that
the application role is active before you execute any EXEC statements.
This is all going to add overhead to the app and slow it down somewhat
-- how much depends on the app and number of users. It is generally
recommended NOT to use application roles with Access because it opens
and closes connections under the covers. See
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q229564 for
more info on the connection pooling issue.

--Mary

On Mon, 27 Dec 2004 00:19:03 -0800, "Norbert Meiss"
<NorbertMeiss@discussions.microsoft.com> wrote:

>I cannot reproduce the strange behaviour from my first post - maybe I was
>wrong with my observations. The SET NOCOUNT ON hint is helpful anyway. I
>didn't understand its meaning so far. Thank you.
>
>Norbert Meiss
>
>"Erland Sommarskog" wrote:
>
>> Norbert Meiss (NorbertMeiss@discussions.microsoft.com) writes:
>> > I have an Access 2003 project which uses a "dummy" login for the
>> > standard connection without any rights on objects. After establishing
>> > the ADO connection via VBA code I enable the application role which is
>> > then used to do all database access
>> >
>> > cn.Execute "EXEC sp_setapprole 'App_Role', '" & strPW & "'"
>> >
>> > This works fine unless I assign a rowsource via a code line like this
>> >
>> > ...Form.RecordSource = "Exec GET_" & Str_SubFormName & " " & strWhere
>> >
>> > This works only when I assign the dummy login the right to execute the
>> > stored procedure. It is not enough to assign this right to the Application
>> > role. But this means that the dummy role has access to this table via the
>> > stored procedure which I wanted to avoid. What do I miss, or is this by
>> > design? Any other solutions for this scenario?
>>
>> Since I don't know Access, I will have to guess a little here. But I have
>> a feeling that the RecordSource stored procedure gets executed on a second
>> connection to SQL Server. In such case, that connection will not have the
>> application role set.
>>
>> Why you get this second connection, I don't know. It could be inherent
>> with the Form.RecordSource thing, and thus no way to avoid it. But there
>> is also an issue with ADO. If you fail to get all data from a query, some
>> client libraries will tell you that the connection is busy, and no new
>> query can be submitted. But ADO tries to be a nice guy, and opens a new
>> connection to SQL Server and submits the qurey on that connection. This
>> often causes more problem than it solves. Really to avoid this, I don't
>> know, since I know nothing about your code. But "SET NOCOUNT ON" saves
>> the day in many cases, since the result sets you have not consumed,
>> often are rowcounts from INSERT/UPDATE/DELETE statements.
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>
>> Books Online for SQL Server SP3 at
>> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>>

Relevant Pages

  • Re: Error "SQL Server does not allow remote connections"
    ... The application could not connect to the sql server db. ... network is blocking the connection. ... SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 ... integratedSecurity, SqlConnection owningObject) +737554 ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: SQL Resets
    ... When we turn on connection pooling, it loads up pretty fast to throw the ... I tend to believe it has something to do with the connection pooling. ... Have you had a look at the SQL Server ... Have you tested the development environment with MaxUserPort=64534, ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL Resets
    ... Let's try another network configuration. ... This time we will try to increase the backlog of the Winsock library that SQL Server uses to listen for connections. ... That means that the maximum pending connection sockets you can hold is five. ... If you disable connection pooling and the app ran slower maybe you ...
    (microsoft.public.sqlserver.connect)
  • Re: Connection from remote computer to network SQL Server
    ... There is no firewall on the W2K machine acting as the SQL server. ... I tried making the SQL machine a "trusted" on the router. ... connection works. ... To find the IP address of your computer inside the network, ...
    (microsoft.public.access.adp.sqlserver)
  • ADO.net Orcas Samples Install Problem
    ... An error has occurred while establishing a connection to the server. ... When connecting to SQL Server 2005, this failure may be caused by the ... SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, ... or am I better off with a full SQL Server install. ...
    (microsoft.public.dotnet.framework.adonet)