I'm wondering if there's an event I could handle where the event fires just before the SqlConnection closes ie: OnBeforeClose ? What I want to achieve is - to call sp_unsetapprole in SQL Server so that when the connection is closed and returned back to the connection poll - so it has no approle attached to it.
At first I thought I could handle the StateChange event - but I believe it's already too late.
The current workaround for me is to manually call sp_unsetapprole on the SqlConnection caller.
Much appreciated for your attention + time !
YS.
When the event fires is way too late to run any sort of SQL batches. I recommend you follow the MSDN recommendation on the subject:
After a SQL Server application role has been activated by calling the
sp_setapprolesystem stored procedure, the security context of that connection cannot be reset. However, if pooling is enabled, the connection is returned to the pool, and an error occurs when the pooled connection is reused.
When such recommendations are put forward, trying to go against them is usually a bad idea. You can give up app roles, there are better alternatives:
Application Role Alternatives
Application roles depend on the security of a password, which presents a potential security vulnerability. Passwords may be exposed by being embedded in application code or saved on disk. You may want to consider the following alternatives:
Use context switching with the EXECUTE AS statement with its NO REVERT and WITH COOKIE clauses. You can create a user account in a database that is not mapped to a login. You then assign permissions to this account. Using EXECUTE AS with a login-less user is more secure because it is permission-based, not password-based. For more information, see Customizing Permissions with Impersonation in SQL Server.
Sign stored procedures with certificates, granting only permission to execute the procedures. For more information, see Signing Stored Procedures in SQL Server.