How to prevent an impersonated principal from resetting the connection?

Question

I'm building an application to connect to a database that uses row level security.

I'm connecting using EF Core and through research I've found that as long as the user running the application on the server has permission to impersonate a user it execute queries as that user using Execute as login = [user login].

I have a method that I call after my database context factory initiates a new context.

public static void ChangeUser(String sUser, DbContext dbContext)
{
    System.Data.Common.DbConnection dbCon = dbContext.Database.GetDbConnection();
    if (dbCon.State != ConnectionState.Open)
        dbCon.Open();
    string userInDb;
    using (var cmd = dbCon.CreateCommand())
    {
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.Add(new SqlParameter("user", sUser));
        cmd.CommandText = "SELECT SUSER_NAME()";
        userInDb = (string) cmd.ExecuteScalar();
    }
    if (userInDb.ToLower() == sUser.ToLower()) return;
    using (var cmd = dbCon.CreateCommand())
    {
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.Add(new SqlParameter("user", sUser));
        cmd.CommandText = "EXECUTE AS LOGIN = @user;";
        cmd.ExecuteNonQuery();
    }
}

This works for the first few calls but as eventually I get the error: ERROR|Microsoft.AspNetCore.Components.Server.Circuits.CircuitHost|Unhandled exception in circuit 'n1Y3qPEm60j0xxOFc6O83IrFu3jHD7EQI0_zPnO_2RQ'. Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot continue the execution because the session is in the kill state. A severe error occurred on the current command. The results, if any, should be discarded. and looking at the logs on the SQL server I get more information with the 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.

How would I go about properly impersonating a user using EF core where it will not cause a "kill state" for the connection?

I assumed that this was because the Execute as was being called twice so I've tried to add a check before trying to impersonate a user.

Answer 1

The WITH COOKIE INTO ... form of EXECUTE AS can be used to avoid the "Cannot continue the execution because the session is in the kill state" error, and is recommended to prevent the user from opening a security hole by REVERTing themselves.

Official EXECUTE AS docs

Taking it further, here are utilities I use to make doing this simple and predictable, and some usage examples.

THERE BE DRAGONS HERE: Be extra careful and thoughtful with code that moves the user across security boundaries. Take extra care with where the user argument comes from that you pass into these ExecuteAs... extension methods. If the user or an adversary is able to manipulate that string they can abuse these ExecuteAs extension methods in two ways:

1. Obviously they can provide a different, elevated user, and then whatever SQL gets executed will be under the privileges of a more powerful user than you may have intended.
2. They can inject arbitrary SQL because that form of EXECUTE AS can only be ran in an adhoc context. The username cannot be parameterized.

Final note, you can EXECUTE AS LOGIN ... or EXECUTE AS USER .... These extension methods do the latter.

// Usage examples

async void Main()
{
    // These two will work because the user from the connection string can SELECT anything.
    Groups.ToList();
    PaymentRecords.ToList();

    //using (var executeAsScope = await (DbContext.Database.GetDbConnection() as SqlConnection).ExecuteAsUserAsync("morePrivilegedUser'; PRINT 'do something malicious'; REVERT; EXECUTE AS USER = 'lessPrivilegedUser"))
    //using (var executeAsScope = await (DbContext.Database.GetDbConnection() as SqlConnection).ExecuteAsUserAsync("morePrivilegedUser"))
    using (var executeAsScope = await (DbContext.Database.GetDbConnection() as SqlConnection).ExecuteAsUserAsync("lessPrivilegedUser"))
    {
        // This will succeed because "lessPrivilegedUser" can SELECT the Groups table.
        Groups.ToList();
        // This will throw an exception because "lessPrivilegedUser" does not have permission to SELECT the PaymentRecords table.
        PaymentRecords.ToList();
        // This will fail because @cookie does not exist.
        Database.ExecuteSqlRaw("REVERT WITH COOKIE = @cookie;");
    }

    // This would succeed because we've exited the "lessPrivilegedUser" execute as scope and we are back to the user determined by the connection string.
    PaymentRecords.ToList();

    // Similarly you can work with transactions using the other extension method
    using (var sqlTransaction = await DbContext.Database.GetDbConnection().BeginTransactionAsync() as SqlTransaction)
    using (var dbContextTransaction = await DbContext.Database.UseTransactionAsync(sqlTransaction))
    using (var executeAsScope = await sqlTransaction.ExecuteAsUserAsync("lessPrivilegedUser"))
    {
        // This will succeed because "lessPrivilegedUser" can SELECT the Groups table.
        Groups.ToList();
        // This will fail because "lessPrivilegedUser" does not have permission to SELECT the PaymentRecords table.
        PaymentRecords.ToList();
        // This will fail because @cookie does not exist.
        Database.ExecuteSqlRaw("REVERT WITH COOKIE = @cookie;");
    }
}

// Utilities

public static class ExecuteAsExtensions
{
    public static async Task<IDisposable> ExecuteAsUserAsync(this SqlConnection connection, string user)
    {
        byte[] cookie;
        using (var cmd = connection.CreateCommand())
        {
            cmd.CommandText = $@"DECLARE @cookie VARBINARY(100); EXECUTE AS USER = '{user}' WITH COOKIE INTO @cookie; SELECT @cookie; SET @cookie = 0;";
            await connection.OpenAsync();
            cookie = (byte[])await cmd.ExecuteScalarAsync();
        }

        return new ExecuteAsScope(cookie, connection, transaction: null);
    }

    public static async Task<IDisposable> ExecuteAsUserAsync(this SqlTransaction transaction, string user)
    {
        byte[] cookie;
        using (var cmd = transaction.Connection.CreateCommand())
        {
            cmd.Transaction = transaction;
            cmd.CommandText = $@"DECLARE @cookie VARBINARY(100); EXECUTE AS USER = '{user}'  WITH COOKIE INTO @cookie; SELECT @cookie; SET @cookie = 0;";
            cookie = (byte[])await cmd.ExecuteScalarAsync();
        }

        return new ExecuteAsScope(cookie, transaction.Connection, transaction);
    }
}

public class ExecuteAsScope : IDisposable
{

    private byte[] cookie;
    private SqlConnection connection;
    private SqlTransaction transaction;

    public ExecuteAsScope(byte[] cookie, SqlConnection connection, SqlTransaction transaction)
    {
        this.cookie = cookie;
        this.connection = connection;
        this.transaction = transaction;
    }

    #region IDisposable Support
    private bool disposedValue = false;
    protected virtual void Dispose(bool disposing)
    {
        if (!disposedValue)
        {
            if (disposing)
            {
                using (var cmd = connection.CreateCommand())
                {
                    cmd.Transaction = transaction;
                    cmd.CommandText = $@"REVERT WITH COOKIE = @cookie";
                    cmd.Parameters.AddWithValue("@cookie", cookie);
                    cmd.ExecuteNonQuery();
                }
                transaction = null;
                connection = null;
            }

            disposedValue = true;
        }
    }

    public void Dispose()
    {
        Dispose(true);
    }
    #endregion
}