Re: Role activation
From: Mary Chipman (mchip@nomail.please)
Date: 03/29/03
- Next message: Mary Chipman: "Re: ms access sql server security"
- Previous message: Narayana Vyas Kondreddi: "Re: SQL authenticated push subscriber's password appears in profiler"
- In reply to: Darko Grujic: "Re: Role activation"
- Next in thread: Darko Grujic: "Re: Role activation"
- Reply: Darko Grujic: "Re: Role activation"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: Mary Chipman <mchip@nomail.please> Date: Sat, 29 Mar 2003 13:09:02 -0500
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;Trusted
>_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.
>>
>>.
>>
- Next message: Mary Chipman: "Re: ms access sql server security"
- Previous message: Narayana Vyas Kondreddi: "Re: SQL authenticated push subscriber's password appears in profiler"
- In reply to: Darko Grujic: "Re: Role activation"
- Next in thread: Darko Grujic: "Re: Role activation"
- Reply: Darko Grujic: "Re: Role activation"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
