Re: Role activation

From: Darko Grujic (darkogrujic@hotmail.com)
Date: 03/30/03 

From: "Darko Grujic" <darkogrujic@hotmail.com>
Date: Sat, 29 Mar 2003 17:30:00 -0800

If I understand right I should be checking if the approle
has been activated using the connection that attempted to
activate that role. In other words if using for example
the Excel VBA I open connection and then check the user
using that connection. Any idea what that VBA query would
look like.

Darko

>-----Original Message-----
>The application role is connection-specific. What happens
is that when
>a connection is returned to the pool it can be reused
when the
>security credentials and string are identical. If one of
these
>connections has had an approle activated prior to being
returned to
>the pool, and then it gets reactivated, you get an error.
On the other
>hand, OLE DB can open additional connections under the
covers, so you
>may activate the approle on one of these connections, but
another
>connection has been opened that does not have the approle
activated
>(as appears to be the case). The connection pooling
article I cirected
>you to shows you how to turn off pooling in the
connection string so
>that connections don't get re-used from the pool. This is
a bad idea
>if your app needs to scale--however, if you don't have a
lot of
>concurrent users it can work out ok.
>
>-- Mary
>MCW Technologies
>http://www.mcwtech.com
>
>On Fri, 28 Mar 2003 23:20:52 -0800, "Darko Grujic"
><darkogrujic@hotmail.com> wrote:
>
>>Thanks for your reply. Yes I'm quite positive that it
>>doesn't activate:
>>
>>I do check it with SELECT USER from Query Analyzer. It
>>returns approle in the first case and dbo in VBA case.
>>I believe it doesn't matter where the SELECT USER is
>>issued from. Or maybe it does, so it returns approle
when
>>SELECT USER is issued from there but doesn't get that
info
>>when Excel has tried the activation. I'll have to check.
>>I'm not sure I understand connection pooling. Maybe
that's
>>the answer.
>>
>>Darko
>>
>>>-----Original Message-----
>>>Are you sure? How are you verifying that it didn't get
>>activated on
>>>that connection? You can select the USER or USER_NAME
to
>>verify--it
>>>should change to the role name. Bear in mind that
>>approles are
>>>connection-specific, and unless you turn off connection
>>pooling in
>>>your connection string, it might not be active. see
this
>>link for more
>>>info:
>>>http://support.microsoft.com/default.aspx?scid=KB;EN-
>>US;Q229564
>>>
>>>-- Mary
>>>MCW Technologies
>>>http://www.mcwtech.com
>>>
>>>On Thu, 27 Mar 2003 03:59:53 -0800, "Darko Grujic"
>>><darkogrujic@hotmail.com> wrote:
>>>
>>>>I can activate an application role using Query
Analyzer
>>>>with EXEC
>>sp_setapprole "RoleName", "RolePassword"
>>>>but not using VBA from within Excel or from within
>>another
>>>>application:
>>>>
>>>> Dim oConnection As ADODB.Connection
>>>>' Create the connection object and connect.
>>>>Set oConnection = CreateObject("ADODB.Connection")
>>>>oConnection.Provider = "sqloledb"
>>>>oConnection.Open "Server=SERVERNAME;Database=pubs;Trust
ed
>>_C
>>>>onnection=yes"
>>>>
>>>>' Activate the application role. There is no error
>>>>handling for this sample.
>>>>oConnection.Execute "EXEC
>>>>sp_setapprole 'AccAppRole', 'ABC'"
>>>>
>>>>This runs with no errors, all libraries exist, but the
>>>>role does not get activated.
>>>
>>>.
>>>
>
>.
>

Relevant Pages

  • Re: Connection Pool Issue
    ... Max pool size 10 seems low... ... My first thought would be that connection poolling is actually disabled on A ... Server A and Server B both are running Windows 2000 Server sp4, ... all the usual places for enabling connection pooling. ...
    (microsoft.public.dotnet.framework.aspnet.webservices)
  • Re: Role activation
    ... I do check it with SELECT USER from Query Analyzer. ... returns approle in the first case and dbo in VBA case. ... I'm not sure I understand connection pooling. ...
    (microsoft.public.sqlserver.security)
  • Re: "General Network Error" and Connection Pooling
    ... connection is cleared but any remaining connections are not. ... getting exceptions until the pool is cleared. ... > However, if i disable connection pooling, I get no error. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: connection pooling
    ... the architecture that determines whether you need connection pooling. ... it's often suggested that you build a separate pool for each ... >>> A web server has a finite capacity. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: pooled connection myth
    ... Each time a connection is closed it is gone. ... You are making a rather large assumption: that the connection object you ... obtain from the pool is an instance provided by the driver you ... This is connection pooling. ...
    (comp.lang.java.programmer)