How can I detect condition that causes exception before it happens?

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

In short, it doesn't look like you can in any simple way.

My first thought was to run this SQL:

SELECT CASE WHEN USER = 'MyAppRole' THEN 1 ELSE 0 END

This works if you use SQL Server Management Studio, but fails when you run it from C# code. The trouble is the error you are getting is not occuring when the call to sp_setapprole is made, it is actually occuring when connection pooling calls sp_reset_connection. Connection pooling calls this when you first use a connection and there is no way to get in before it.

So I guess you have four options:

1. Turn connection pooling off by adding "Pooling=false;" to your connection string.
2. Use some other way to connect to SQL Server. There are lower level APIs than ADO.Net, but frankly it is probably not worth the trouble.
3. As casperOne says you could fix your code to close the connection correctly.
4. Catch the exception and reset the connection pool. I'm not sure what this will do to other open connections though. Example code below:

class Program
{
    static void Main(string[] args)
    {
        SqlConnection conn = new SqlConnection("Server=(local);Database=Test;UID=Scrap;PWD=password;");

        setAppRole(conn);
        conn.Close();

        setAppRole(conn);
        conn.Close();
    }

    static void setAppRole(SqlConnection conn) 
    {
        for (int i = 0; i < 2; i++)
        {
            conn.Open();
            try
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "exec sp_setapprole ";
                    cmd.CommandText += string.Format("@rolename='{0}'", "MyAppRole");
                    cmd.CommandText += string.Format(",@password='{0}'", "password1");
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                if (i == 0 && ex.Number == 0)
                {
                    conn.Close();
                    SqlConnection.ClearPool(conn);
                    continue;
                }
                else
                {
                    throw;
                }
            }
            return;
        }
    }
}

Answer 2

it is actually occuring when connection pooling calls sp_reset_connection. Connection pooling calls this when you first use a connection and there is no way to get in before it.

Based on Martin Brown's answer, you could try adding "Connection Reset=False" to the connection string as a way to "get in before" sp_reset_connection. (See "Working with “soiled” connections" for an explanation of many of the drawbacks of this setting.)

Your problem is a known issue with connection pooling. The suggested work-around is to disable connection pooling... if this is a desktop app it may be worth considering holding a few connections open instead (also explained in the article linked above).

These days (SQL 2005+) the recommendation (under Application Roles and Connection Pooling) is to "take advantage of the new security mechanisms that you can use instead of application roles", like EXECUTE AS.

Answer 3

Try moving sp_unsetapprole (is it really the name of the sproc? Probably, sp_dropapprole is the correct one?) to setAppRole() and execute it prior to adding an app role.

Answer 4

I'm not sure about your problem, but I think you would avoid it if you created new connection objects instead of reusing them. So instead of doing

c.Open();
blabla;
c.Close();
c.Open(); 
kaboom...

You would do the following:

using (new SqlConnection ...)
{
  c.Open();
  blabla;
}

using (new SqlConnection ... )
{
  c.Open();
  no kaboom?
}

(Please forgive the pseudocode... The keyboard on my eeepc is impossible to use...)

Answer 5

Isn't there a way to clear a pool of all connections. SqlPools.Clear or something.

You could simply try and catch the exception and create a new connection, that should force the pool to create a complete new connection.

Answer 6

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 7

You could check c.State (ConnectionState object), which should be one of the following:

System.Data.ConnectionState.Broken
System.Data.ConnectionState.Closed
System.Data.ConnectionState.Connecting
System.Data.ConnectionState.Executing
System.Data.ConnectionState.Fetching
System.Data.ConnectionState.Open

Answer 8

@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