Connection pooling and changed password issue

[Guest]

Hi
I have a problem I suspect is to do with connection pooling. If I connect
as username/password, and the dispose of the connection am I correct in
thinking the next time I connect to the same datasource with
username/password then the connection pool will be used and the same
connection can be used at less overhead. This is fine, however if I change
the password to password2 I can connect as username/password and
username/password2. I can only assume this is because the pool is still
holding a valid connection as username/password.

Where I am confused is that on occasion I have logged in as
username/password, chnaged the password and immediately tried to login again
as username/password and it immediately fails. If I then login as
username/password2 I can connect and then if I connect as username/password
it will connect again after previously failing. Eventually the old password
will fail if I leave it long enough, again I assume this is because the
connection has been idle for the specified time and is cleared out of the
pool.

As I understand there is no way at the minute to clear one particular
connection out of the pool so is there any way round this issue?
Thanks in advance
Siobhan

 

[William \(Bill\) Vaughn]

If the connection string changes, a new pool is created--it's that simple
(almost). A pool lives until it's empty or the application ends. Unused
connections in a pool time out after 4-8 minutes. If you're changing
credentials, you should get a connection that matches your connection string
(using the supplied credentials).
If this is a Windows forms application you don't typically gain much from a
connection pool as there are so few connections. If building an ASP
application you don't typically collect user credentials--you usually use
the ASPNET account.
hth


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Guest]

Hi - so a previously made connection with the old credentials will still be
retrieved from the pool until it times out? And there is no way to get rid
of this pooled connection other than its timeout?

It is an ASP.Net application and credential do change as each user has their
own database login (the system is intranet). Each user requires their own
login for purposes of audit and we also assign users to roles to restrict
access to data and certain functionality depending on their role.
Thanks
Siobhan

 

[William \(Bill\) Vaughn]

Nope. There is no "clear the pool" or "drop the pool" until ADO.NET 2.0.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[William \(Bill\) Vaughn]

If you need to track users do so, but don't do it with the connection
string. Use an application-based credentials and then use the user-provided
credentials to track their usage within the application. It's far easier to
code and manage. The administrative load caused by lots of logins is far
more than any DBA would want to take on.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________