CODEWITHSUNDEEP
c#sql-serversqlconnection
YS.
YS.

Reputation: 1828

SqlConnection On Before Close event?

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.

Upvotes: 3

Views: 1295

Answers (2)

Stephen McDaniel
Stephen McDaniel

Reputation: 2978

I know this is late but I came across this answer and wanted to throw another option out there. We had a very similar requirement for our application. We needed to call a custom stored procedure every time a connection is opened and again when it is closed.

We were able to handle this with the help of the EF Provider Wrapper Toolkit (also seems to be on Nuget now). That basically lets you inject your own logic into various ADO.NET objects - so at the very lowest level of database access. We then made our own custom DbConnection class that any code in your app will end up using. It was actually pretty easy and has given us a lot of nice 'hooks' into the lowest level of database access that has come in handy a lot.

Note that we were using Entity Framework and the library is called EF Provider Wrapper Toolkit but it works fine with any code using ADO.NET to access a database.

Here is some sample code for our custom DbConnection class that shows the kinds of things you can accomplish:

/// <summary>
/// Custom implementation of a wrapper to <see cref="DbConnection"/>.
/// Allows custom behavior at the connection level.
/// </summary>
internal class CustomDbConnection : DbConnectionWrapper
{
    /// <summary>
    /// Opens a database connection with the settings specified by 
    /// the <see cref="P:System.Data.Common.DbConnection.ConnectionString"/>.
    /// </summary>
    public override void Open()
    {
        base.Open();

        //After the connection has been opened, use this spot to do any initialization type logic on/with the connection

    }

    /// <summary>
    /// Closes the connection to the database. This is the preferred method of closing any open connection.
    /// </summary>
    /// <exception cref="T:System.Data.Common.DbException">
    /// The connection-level error that occurred while opening the connection.
    /// </exception>
    public override void Close()
    {
        //Before closing, we do some cleanup with the connection to make sure we leave it clean
        //   for the next person that might get it....

        CleanupConnection();

        base.Close();
    }

    /// <summary>
    /// Cleans up the connection so the next person that gets it doesn't inherit our junk.
    /// </summary>
    private void CleanupConnection()
    {
        //Create the ADO.NET command that will call our stored procedure
        var cmd = CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "p_cleanup_connection";

        //Run the SP
        cmd.ExecuteNonQuery();
    }
}

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294427

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_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.

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.

Upvotes: 2

Related Questions