I've developed an ASP.NET application to work with SQL Server 2005, using an
Application Role. All seemed to work fine at first, until I started hitting
the database more than once.

I follow the approach: Create connection, execute sp_setapprole, query data,
and close connection. However, it seems that connection pooling prevents me
from executing sp_setapprole again, even though the SqlConnection object was
closed and disposed.

I cannot assume that the connection pool will return a connection that has
executed sp_setapprole, can I? So, how can I tell if the connection has
already executed the stored procedure? I've temporarily turned pooling off,
but I'm worried about the drawbacks.

In short; can someone point out the best way to setup an ASP.NET application
to use an Application Role. Any documentation links or advice is much
appreciated.

This topic discusses the issues with application roles and provides
some alternatives which you may find more suitable for an asp.net
application: http://msdn.microsoft.com/en-us/library/bb669062.aspx.
HTH,

--Mary

On Mon, 8 Feb 2010 11:09:01 -0800, Jason Barnett
<JasonBarnett (AT) discussions (DOT) microsoft.com> wrote: