Saturday, September 10, 2011

sp_SetAppRole and ADO.NET connection pooling

sys.sp_SetAppRole provides an ability to impersonate a SQL session with specified role. But if you forget to call a sys.sp_UnsetAppRole method you will get a SqlException “A severe error occurred on the current command. The results, if any, should be discarded.”

The problem is that when you upgrade a connection object with impersonation info and close it, the ADO.NET returns it to a connection pool. When the new connection is requested the client gets the connection which has been impersonated before – the security breach.
A correct way to manage app roles is provided below (the wrapper class is in the details section).

using (var connection = new SqlConnection(_connectionString))
{
  connection.Open();
  using (connection.AppRoleScope(RoleName, RolePassword))
  {
    // connection is impersonated to a given RoleName
  }
}

An alternative way is to set “Pooling=False” in a connection string. ADO.NET connection pool will not be used and hence the problem is gone. The drawback is that on my tests this solution was three times slower then the clean up connection way.

Details:
public static class SqlConnectionExtensions
{
  internal class AppRoleScopeWrapper : IDisposable
  {
    private readonly SqlConnection _connection;
    private byte[] _cookie;

    public AppRoleScopeWrapper(SqlConnection connection, string roleName, string password)
    {
    if (connection == null)
      throw new ArgumentNullException("connection");
    if (connection.State != ConnectionState.Open)
      throw new InvalidOperationException("Connection must be opened before setting application role");

    if (string.IsNullOrWhiteSpace(roleName))
      throw new ArgumentNullException("roleName");
    if (password == null)
      throw new ArgumentNullException("password");
   _connection = connection;

    Impersonate(roleName, password);
  }
  private void Impersonate(string roleName, string password)
  {
    var com = new SqlCommand("sys.sp_SetAppRole", _connection) { CommandType = CommandType.StoredProcedure };

    com.Parameters.Add(new SqlParameter("@roleName", SqlDbType.NVarChar) { Value = roleName });
    com.Parameters.Add(new SqlParameter("@password", SqlDbType.NVarChar) { Value = password });
    com.Parameters.Add(new SqlParameter("@fCreateCookie", SqlDbType.Bit) { Value = true });
    com.Parameters.Add(new SqlParameter("@cookie", SqlDbType.VarBinary, 50) { Direction = ParameterDirection.InputOutput });
    com.ExecuteNonQuery();

    _cookie = (byte[])com.Parameters["@cookie"].Value;
    if (_cookie == null)
      throw new InvalidOperationException("Can't impersonate user="+roleName);
  }
  public void Dispose()
  {
    var com = new SqlCommand("sys.sp_UnsetAppRole", _connection) { CommandType = CommandType.StoredProcedure };
    com.Parameters.Add(new SqlParameter("@cookie", SqlDbType.VarBinary, 50) { Value = _cookie });
    com.ExecuteNonQuery();
  }
  /// 
  /// Provides impersonation scope over a given opened connection.
  /// 
  public static IDisposable AppRoleScope(this SqlConnection connection, string roleName, string password)
  {
    return new AppRoleScopeWrapper(connection,roleName,password);
  }
}
See also SQL Server Connection Pooling (ADO.NET)

1 comment: