c# - SqlConnection On Before Close 事件？

Question

我想知道在 SqlConnection 关闭之前触发事件的地方是否有我可以处理的事件，即:OnBeforeClose？我想要实现的是 - 在 SQL Server 中调用 sp_unsetapprole 以便在关闭连接并返回到连接轮询时 - 因此它没有附加任何 approle。

起初我以为我可以处理 StateChange 事件 - 但我认为已经太晚了。

我目前的解决方法是在 SqlConnection 调用方上手动调用 sp_unsetapprole。

非常感谢您的关注和时间!

是的。

Answer 1

当事件触发时运行任何类型的 SQL 批处理都太迟了。我建议你关注 MSDN recommendation on the subject :

After a SQL Server application role has been activated by calling the sp_setapprole system 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.

当提出此类建议时，试图反对它们通常不是一个好主意。您可以放弃应用角色，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.