Friday, October 7, 2011

Starting an EXE process from ASP.NET server hangs on Windows Server

When you start an executable EXE or BAT/CMD file via Process.Start in Windows Server and .NET 2.0-4.0 you will get an error "The application failed to initialize properly (0xC0000142). Click on OK to terminate the application".
Actual reason is pretty weird for me but a workaround is to use WMI Win32_Process instead of .NET Process.Start to execute a process. See RunWMI here as an example.

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))
  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.

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 });

    _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 });
  /// 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)