Hi,

My company is building a multi-tenant internet-facing web app in ASP.NET
 / SQL 2005.  We can't therefore use NT authentication, and must use
our own web forms authentication, but still need to use SQL's "Database
User"-based security so that we can encapsulate security in stored procs
etc by checking user_id() - we create these DB users (for each web end
user) "WITHOUT LOGIN" and multiplex through a single proxy account.
Legacy reasons mean we can't design around this requirement.

We have used Enterprise Library to standardize DB access, and
implemented a new Database Provider to implement the security
requirement above while still allowing scalability through connection
pooling:  when the Connection's open event is called, we connect to SQL
Server using a proxy account and call "EXECUTE AS" the desired db user,
changing the connection context. We store the 'cookie' returned from
this call against the connection SPID so that on subsequent Connection
Open (which gives us a connection from the pool which might have already
been set to another user's context) we check the SPID to see if there's
already cookie, use REVERT WITH COOKIE to revert to proxy account,
before calling "sp_reset_connection" which resets the connection and we
can set to new user context.

Why do we try to REVERT on connection Open?  We would rather REVERT just
before connection close but we cannot control when this occurs
(datareader consumers of the connection, which we cannot control, may
close the connection automatically) or hook into an event (statechanged
only tells us AFTER the connection is closed - cannot reopen!)

This all required us to use the "Connection Reset=false" connection
string setting, which ensures that sp_reset_connection (SQL internal SP)
is not called automatically because we must REVERT BEFORE this proc is
called otherwise the connection is dropped by SQL server because the
reverting context is different to original proxy login.

This slightly complicated (but quite compact and elegant) solution works
fine on .NET 2.0 RTM.  BUT it seems that from .NET 2.0 SP1 onwards, the
"Connection Reset" connection string API has been deprecated! This means
the connection pooling mechanism ignores the setting and tries to
execute "sp_reset_connection" on each connection open as a different
context than the original login and SQL server duly kills the connection
(and crashes the app).

In short, I've run out of options for REVERTing the connection just
before it's closed.  The connection statechange event is too late and
binding to 3rd party controls means they may use datareaders which
automatically close the connection before I can intervene.

The only relevant Microsoft documentation I have found (on App Roles and
connection pooling) states:

".. if pooling is enabled, the connection is returned to the pool, and
an error occurs when the pooled connection is reused. If you are using
SQL Server application roles, we recommend that you disable connection
pooling for your application in the connection string."

GRRR! How does Microsoft expect us to build scalable applications with
the features specifically designed for it (connection pooling, EXECUTE
AS + REVERT) if these features no longer work together?

Hosting this app without connection pooling is unacceptable, and I don't
want to have to rewrite the entire security infrastructure.  It seems my
only option is to use compile my own version of Enterprise Library,
implementing my own Connection object which can intercept the Close
method calls - this is neither elegant, nor maintainable, and I'm hoping
that someone has a better idea?  If there _is_ a way to grab an event on
the connection just before it closes, that would be perfect...

Anyone?

Many thanks for your time, and any help you can give...

Simon

RIP "Connection Reset":

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstri
ngbuilder.connectionreset.aspx