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

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 2

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 3

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 4

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 5

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 6

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