Avoid impersonation in the middle tier???

[C Newby]

In the MSDN article ".NET Data Access Architecture Guide", the following
statement is made in reference to connection pooling:

---
Avoid impersonation in the middle tier. Windows authentication requires a
Windows account for database access. Although it might seem logical to use
impersonation in the middle tier, avoid doing so because it defeats
connection pooling and has a severe impact on application scalability.

To address this problem, consider impersonating a limited number of Windows
accounts (rather than the authenticated principal) with each account
representing a particular role.
---

Perhaps I am unclear as to what is meant by "authenticated principal", but
it seems that the article is saying not to use integrated security, a means
by which (as I understand it) on can automatically pass a user's credentials
down through each application layer.

If this *is* the case, how exactly does this undermine the purpose of
connection pooling and the pursuit of scalability?

I think i'm misunderstanding something, but what?

TIA//

 

[C Newby]

Thanks David...that helps i think. I'm still a little unclear though...

To clarify, when connecting to SQL Server, using the pair "Integrated
Security=SSPI" indicates a "trusted" connection?

The reason i ask is that in reading about connection pooling with SQL 2000,
it seemed that the most straightforward implementation was to use the exact
same connection string for all connections intended for the pool, the shared
connection string containing value-pairs indicateing how it (SQL) should set
up and maintain the pool.

So then, in the case of using Integrated Security, am i right in assuming
that SQL will actually end up either making new connections for each set of
credentials, or it will end up raising an error...being that, as you said,
if a connection is opened as "trusted" for user X, the connection cannot be
reused for user Y?

I apreciate your comments on scalability as well...in my particular case, I
think we will end up with a mix.

TIA//

 

[Carl Prothman [MVP]]

In the MSDN article ".NET Data Access Architecture Guide", the following
statement is made in reference to connection pooling:
---
Avoid impersonation in the middle tier. Windows authentication requires a
Windows account for database access. Although it might seem logical to use
impersonation in the middle tier, avoid doing so because it defeats
connection pooling and has a severe impact on application scalability.

Your best bet is to put your middle-tier components into a COM+ package,
then set the COM+ Package's Identity to a known user account. Then when
your components use a trusted connection, it will use the COM+ Package's Identity.
Connection pooling is maximized since there is only one user account being used by
the middle tier components.

For more info, see the Connection Pooling section for the corresponding
..NET Data Provider that you are using.
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconadonetconnections.asp

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com

 

[David Browne]

Thanks David...that helps i think. I'm still a little unclear though...

To clarify, when connecting to SQL Server, using the pair "Integrated
Security=SSPI" indicates a "trusted" connection?
Yes.


The reason i ask is that in reading about connection pooling with SQL 2000,
it seemed that the most straightforward implementation was to use the exact
same connection string for all connections intended for the pool, the shared
connection string containing value-pairs indicateing how it (SQL) should set
up and maintain the pool.

Integrated security is an exception to this rule. The presence of
"Integrated Security=SSPI" in your connection string must either supress
pooling entirely or key the pooling on the thread's current identity. I'm
not really sure which.

So then, in the case of using Integrated Security, am i right in assuming
that SQL will actually end up either making new connections for each set of
credentials, or it will end up raising an error...being that, as you said,
if a connection is opened as "trusted" for user X, the connection cannot be
reused for user Y?

Yes, One or the other, I'm not really sure which.

David

 

[C Newby]

Thanks very much David...it's much more clear to me now.