How can I detect condition that causes exception b

Question

I had no luck with this question so I've produced this simple-as-possible-test-case to demonstrate the problem.

In the code below, is it possible to detect that the connection is unusable before trying to use it?

    SqlConnection c = new SqlConnection(myConnString);

    c.Open();  // creates pool

    setAppRole(c);  // OK

    c.Close(); // returns connection to pool

    c = new SqlConnection(myConnString); // gets connection from pool

    c.Open(); // ok... but wait for it...

    // ??? How to detect KABOOM before it happens?

    setAppRole(c); // KABOOM

The KABOOM manifests as a 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.

...plus an exception in code.

setAppRole is a simple method to set an application role on the connection. It is similar to this...

static void setAppRole(SqlConnection conn) {

    using (IDbCommand cmd = conn.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "exec sp_setapprole ";
            cmd.CommandText += string.Format("@rolename='{0}'",myUser);
            cmd.CommandText += string.Format(",@password='{0}'",myPassword);
            cmd.ExecuteNonQuery();
        }
    }

In the real code an attempt is made to use sp_unsetapprole prior to closing the connection but it cannot always be guaranteed (inherited buggy multithreaded app). In any case it still seems reasonable to expect to be able to detect the kaboom before causing it.

Answer 1

I've also posted this in response to your previous question. When calling sp_setapprole, you should call sp_unsetapprole when you are done, and the solution I proposed there will help you:

Detecting unusable pooled SqlConnections

---

It would seem that you are calling sp_setapprole but not calling sp_unsetapprole and then letting the connection just be returned to the pool.

I would suggest using a structure (or a class, if you have to use this across methods) with an implementation of IDisposable which will take care of this for you:

public struct ConnectionManager : IDisposable
{
    // The backing for the connection.
    private SqlConnection connection;

    // The connection.
    public SqlConnection Connection { get { return connection; } }

    public void Dispose()
    {
        // If there is no connection, get out.
        if (connection == null)
        {
            // Get out.
            return;
        }

        // Make sure connection is cleaned up.
        using (SqlConnection c = connection)
        {
            // See (1).  Create the command for sp_unsetapprole
            // and then execute.
            using (SqlCommand command = ...)
            {
                // Execute the command.
                command.ExecuteNonQuery();
            }
        }
    }

    public ConnectionManager Release()
    {
        // Create a copy to return.
        ConnectionManager retVal = this;

        // Set the connection to null.
        retVal.connection = null;

        // Return the copy.
        return retVal;        
    }

    public static ConnectionManager Create()
    {
        // Create the return value, use a using statement.
        using (ConnectionManager cm = new ConnectionManager())
        {
            // Create the connection and assign here.
            // See (2).
            cm.connection = ...

            // Create the command to call sp_setapprole here.
            using (SqlCommand command = ...)
            {
                // Execute the command.
                command.ExecuteNonQuery();

                // Return the connection, but call release
                // so the connection is still live on return.
                return cm.Release();
            }
        }
    }
}

1. You will create the SqlCommand that corresponds to calling the sp_setapprole stored procedure. You can generate the cookie and store it in a private member variable as well.
2. This is where you create your connection.

The client code then looks like this:

using (ConnectionManager cm = ConnectionManager.Create())
{
    // Get the SqlConnection for use.
    // No need for a using statement, when Dispose is
    // called on the connection manager, the connection will be
    // closed.
    SqlConnection connection = cm.Connection;

    // Use connection appropriately.
}

Answer 2

@edg: You say in a comment, "...it is only then it hits the actual server and encounters a security prob as described in the msg quote".

This points to the source of your problem: You are encountering a security problem, and this appear unavoidable because the calling code assumes another identity than was used to open the connection. This naturally makes a security log entry.

Since the identity change is by design, perhaps the solution is to filter the securty log. The event viewer has a Filter Current Log action that can filter by keyword or eventid.

+tom