Connection Pooling, REVERT & "Connection Reset" in .NET 2.0 SP1+

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.sqlconnectionstringbuilder.connectionreset.aspx
.

Relevant Pages

  • Re: Concurrent database access in SQL 2005 Mobile
    ... Please stick to having an "dummy" connection in Open State. ... SQL CE/Mobile has a background thread that flushes the changes ... they run for long hours and we have not seen memory leaks so far. ... then opens his work forms and it's in ...
    (microsoft.public.sqlserver.ce)
  • Re: Concurrent database access in SQL 2005 Mobile
    ... Please stick to having an "dummy" connection in Open State. ... So it is a kind of asynchronous commit which happens every 10 ... I am not sure of this claim as we have many applications built on SQL CE ... then opens his work forms and it's in ...
    (microsoft.public.sqlserver.ce)
  • Access 2003 adp/proxy security - A substitute for SYSTEM_USER()
    ... We need a way for SQL to know the nt username that initiated the proxy ... Many stored procedures and views use a UDF that also depends on ... SyUserLogin and use HostIDand HostName() to find the right row. ... We've played with the Connection dialog settings, ...
    (microsoft.public.sqlserver.connect)
  • Access 2003 adp/proxy security - A substitute for SYSTEM_USER()
    ... We need a way for SQL to know the nt username that initiated the proxy ... Many stored procedures and views use a UDF that also depends on ... SyUserLogin and use HostIDand HostName() to find the right row. ... We've played with the Connection dialog settings, ...
    (microsoft.public.access.security)
  • RE: Logging in in background
    ... wold take all kinds of modifictions as I'd need to be checking that each SQL ... my database and all have connection strings associated with them. ... I suspect that there is also an issue on the SQL Server side as I keep ...
    (microsoft.public.access.modulesdaovba)