When I attempt to set an application role on a SqlConnection with sp_setapprole I sometimes get the following error in the Windows event log...
The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. This scenario is not supported. See "Impersonation Overview" in Books Online.)
... and a matching exception is thrown in my application.
These are pooled connections, and there was a time when connection pooling was incompatible with app roles - in fact the old advice from Microsoft was to disable connection pooling (!!) but with the introduction of sp_unsetapprole it is now (in theory) possible to clean a connection before returning it to the pool.
I believe these errors occur when (for reasons unknown) sp_unsetapprole is not run on the connection before it is closed and returned to the connection pool. sp_approle is then doomed to fail when this connection is returned from the pool.
I can catch and handle this exception but I would much prefer to detect the impending failure and avoid the exception (and messages in the event log) altogether.
Is it possible to detect the problem without causing the exception?
Thoughts or advice welcome.
Nope, it's not possible.