Application Roles and Connection Pooling
One would have thought this to be a well known fact but since one of our developers stumbled upon it yesterday I thought it might be useful to someone else.
As soon as a pooled connection with enabled application role gets reused an exception gets thrown and its description is not really helpful if you're not aware of the problem: General Network Error. This happens because the security context of the connection doesn't get properly reset when it is closed. To work around the problem you can either avoid using application roles or disable connection pooling (by adding Pooling=False to the connection string). You can find more details in this knowledge base article.
It might be worth mentioning that the above is completely accurate only for SQL Server 2000. SQL Server 2005 comes with a new stored procedure sp_unsetapprole which can be used to reset the security context. I haven't tried it but by calling it before closing the connection you should be able to make application roles work together with connection pooling.
