SQL Exception: "Impersonate Session Security Context" cannot be called in this batch because a simultaneous batch has called it

Question

When opening a connection to SQL Server 2005 from our web app, we occasionally see this error:

"Impersonate Session Security Context" cannot be called in this batch because a simultaneous batch has called it.

We use MARS and connection pooling.

The exception originates from the following piece of code:

protected SqlConnection Open()
{
    SqlConnection connection = new SqlConnection();
    connection.ConnectionString = m_ConnectionString;
    if (connection != null)
    {
        try
        {
            connection.Open();
            if (m_ExecuteAsUserName != null)
            {
                string sql = Format("EXECUTE AS LOGIN = {0};", m_ExecuteAsUserName);
                ExecuteCommand(connection, sql);
            }
        }
        catch (Exception exception)
        {
            connection.Close();
            connection = null;
        }
    }
    return connection;
}

I found an MS Connect article which suggests that the error is caused when a previous command has not yet terminated before the EXECUTE AS LOGIN command is sent. Yet how can this be if the connection has only just been opened?

Could this be something to do with connection pooling interacting strangely with MARS?

UPDATE: For the short-term we have implemented a workaround by clearing out the connection pool whenever this happens, to get rid of the bad connection, as it otherwise keeps getting handed back to various users. (This now happens a 5-10 times a day with only a small number of simultaneous users, so it is fairly annoying.) But if anyone has any further ideas, we are still looking out for a real solution...

Answer 1

I would say it's MARS rather then pooling

From "Using Multiple Active Result Sets (MARS)"

• Applications can have multiple default result sets open and can interleave reading from them.
• Applications can execute other statements (for example, INSERT, UPDATE, DELETE, and stored procedure calls) while default result sets are open.

Connection pooling in it's basic form means the connection open/close overhead is minimised, but any connection (until MARS) has one thing going on at any one time. Pooling has been around for some time and just works out of the box.

MARS (I've not used it BTW) introduces overlapping "stuff" going on for any single connection. So it's probably MARS rather than connection pooling is the bigger culprit of the 2.

From "Extending Database Impersonation by Using EXECUTE AS"

When impersonating a principal by using the EXECUTE AS LOGIN statement, or within a server-scoped module by using the EXECUTE AS clause, the scope of the impersonation is server-wide.

This may explain why MARS is causing it: the same principal in 2 session both running EXECUTE AS. There may be something in that article of use, or try this:

IF ORIGINAL_LOGIN() = SUSER_SNAME() EXECUTE AS LOGIN = {0};

On reflection and after reading for this answer, I've not convinced that trying to change execution context for each session (MARS) in one connections is a good idea...

Answer 2

Have you tried to use a revert at the end of your sql statement?

http://msdn.microsoft.com/en-us/library/ms178632.aspx

I always do this to just make sure the current context is back to normal.

Answer 3

Don't blame connection pooling - MARS is quite notorious for wreaking a havoc. It's not entirely it's blame but it's kind of half and half. The key thing to remember is that MARS is designed, and only works with "normal" DB use (meaning, regular CRUD stuff no admin batches). Any commands that have a wide effect on DB engine can trip MARS even if it's just one connection and single threaded (like running a setup batch to create tables or a nested transaction).

Having said that, one can easily just blame MARS, but it works perfecly fine for normal CRUD scenarios which are like 99% (and things with low efficiencey like ORM-s and LINQ depend on it for life). Meaning that it's important for people to learn that if they want to hack SQL through a connection they can't use MARS. For example I had a setup code that was creating whole DB from scratch, beceuse it's very convenient for deployment, but it was sharing connection sting with web service it was deploying - oops :-) Took me a few days of digging to learn my lesson. So I just maintain the separation of concerns (which is always good) and problems went away.