SQL Server Application Roles

[Jason Barnett]

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.

 

[Mary Chipman [MSFT]]

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