Sql Server application roles with Entity Framework Code First Migrations

By Mirek on 2/6/2015 (tags: Application Role, Entity Framework, SQL Server, categories: architecture, code)

A time ago I have been writing about the Sql Server application roles, what are the benefits of using them and what are the limitations when we want to incorporate the Entity Framework to use application role. Today I will show you how to workaround those limitations with new Entity Framework facility introduced in version 6 of the framework.

I am specifically talking about database interceptors introduced in EF. With use of connection interceptor we can catch the moment when the database connection is opened and call the sp_setapprole as well as we can inject the sp_unsetapprole call when the connection is being closed or disposed.

All we need to do is to implement a proper connection interceptor

public class EFDBConnectionApplicationRoleInterception : IDbConnectionInterceptor

    private string _sqlServerApplicationRoleName;

    private string _password;

    private string _dbname;

    private byte[] _cookie;

    public EFDBConnectionApplicationRoleInterception() { }

    public EFDBConnectionApplicationRoleInterception(string sqlAppRoleName, string password, string dbname)

        _sqlServerApplicationRoleName = sqlAppRoleName;

        _password = password;

        _dbname = dbname;

    public void Opened(DbConnection connection, DbConnectionInterceptionContext interceptionContext)

        if (connection.State != ConnectionState.Open) return;

        if (!connection.Database.Equals(_dbname)) return;

        ActivateApplicationRole(connection, this._sqlServerApplicationRoleName, _password);

    public void Closing(DbConnection connection, DbConnectionInterceptionContext interceptionContext)

        if (connection.State != ConnectionState.Open) return;

        if (!connection.Database.Equals(_dbname)) return;

        DeActivateApplicationRole(connection, _cookie);

    public void Disposing(DbConnection connection, DbConnectionInterceptionContext interceptionContext)

        if (connection.State != ConnectionState.Open) return;

        if (!connection.Database.Equals(_dbname)) return;

        DeActivateApplicationRole(connection, _cookie);

    public virtual void ActivateApplicationRole(DbConnection dbConn, string appRoleName, string password)

        if (dbConn == null)

            throw new ArgumentNullException("DbConnection");

        if (ConnectionState.Open != dbConn.State)

            throw new InvalidOperationException("DBConnection must be opened before activating application role");

        if (string.IsNullOrWhiteSpace(appRoleName))

            throw new ArgumentNullException("appRoleName");

        if (password == null)

            throw new ArgumentNullException("password");

        using (DbCommand cmd = dbConn.CreateCommand())

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = "sp_setapprole";

            cmd.Parameters.Add(new SqlParameter("@rolename", appRoleName));

            cmd.Parameters.Add(new SqlParameter("@password", password));

            cmd.Parameters.Add(new SqlParameter("@fCreateCookie", SqlDbType.Bit) { Value = true });

            SqlParameter cookie = new SqlParameter("@cookie", System.Data.SqlDbType.Binary, 50) { Direction = System.Data.ParameterDirection.InputOutput };

            cmd.Parameters.Add(cookie);

            cmd.ExecuteNonQuery();

            if (cookie.Value == null)

                throw new InvalidOperationException("Failed to set application role.");

            _cookie = (byte[])cookie.Value;

    public virtual void DeActivateApplicationRole(DbConnection dbConn, byte[] cookie)

        using (DbCommand cmd = dbConn.CreateCommand())

            cmd.CommandText = "sp_unsetapprole";

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@cookie", SqlDbType.VarBinary, 50) { Value = cookie });

            cmd.ExecuteNonQuery();

    #region Not used interceptions

    public void BeganTransaction(DbConnection connection, BeginTransactionInterceptionContext interceptionContext) { }

    public void BeginningTransaction(DbConnection connection, BeginTransactionInterceptionContext interceptionContext) { }

    public void Closed(DbConnection connection, DbConnectionInterceptionContext interceptionContext) { }

    public void ConnectionStringGetting(DbConnection connection, DbConnectionInterceptionContext<string> interceptionContext) { }

    public void ConnectionStringGot(DbConnection connection, DbConnectionInterceptionContext<string> interceptionContext) { }

    public void ConnectionStringSet(DbConnection connection, DbConnectionPropertyInterceptionContext<string> interceptionContext) { }

    public void ConnectionStringSetting(DbConnection connection, DbConnectionPropertyInterceptionContext<string> interceptionContext) { }

    public void ConnectionTimeoutGetting(DbConnection connection, DbConnectionInterceptionContext<int> interceptionContext) { }

    public void ConnectionTimeoutGot(DbConnection connection, DbConnectionInterceptionContext<int> interceptionContext) { }

    public void DataSourceGetting(DbConnection connection, DbConnectionInterceptionContext<string> interceptionContext) { }

    public void DataSourceGot(DbConnection connection, DbConnectionInterceptionContext<string> interceptionContext) { }

    public void DatabaseGetting(DbConnection connection, DbConnectionInterceptionContext<string> interceptionContext) { }

    public void DatabaseGot(DbConnection connection, DbConnectionInterceptionContext<string> interceptionContext) { }

    public void Disposed(DbConnection connection, DbConnectionInterceptionContext interceptionContext) { }

    public void EnlistedTransaction(DbConnection connection, EnlistTransactionInterceptionContext interceptionContext) { }

    public void EnlistingTransaction(DbConnection connection, EnlistTransactionInterceptionContext interceptionContext) { }

    public void Opening(DbConnection connection, DbConnectionInterceptionContext interceptionContext) { }

    public void ServerVersionGetting(DbConnection connection, DbConnectionInterceptionContext<string> interceptionContext) { }

    public void ServerVersionGot(DbConnection connection, DbConnectionInterceptionContext<string> interceptionContext) { }

    public void StateGetting(DbConnection connection, DbConnectionInterceptionContext<ConnectionState> interceptionContext) { }

    public void StateGot(DbConnection connection, DbConnectionInterceptionContext<ConnectionState> interceptionContext) { }

    #endregion

And then register it before we use our database context

DbInterception.Add(new EFDBConnectionApplicationRoleInterception("TestAppRole", "secure_password", "TestAppDB"));

This is all fine. If we have an application role created on our database this should do the work and make the EF to use the application role in every connection.

Problem comes when we are using automatic database migrations and we want them to create the database for us. This is because we cannot set the application role on a connection until the role is created in the database and we cannot create the role in the database until the database exists. So we have two cases we need to handle

When the database does not exist: we must let the migrations create the database first, then we must create the application role on it and finally we can set the role on current connection
When the database already exists: we can set the application role on the connection first then letting the migrations doing any update or so.

This is how it could be implemented. Put it somewhere in your application start, before you use the database context for the first time.

   1: Database.SetInitializer(new MigrateDatabaseToLatestVersion<AppContext, Configuration>(true));

2:

   3: using (var ctx = new AppContext())

   4: {

   5:     //create database without application role set

   6:     if (!ctx.Database.Exists())

   7:     {

   8:         ctx.Database.Initialize(false);

   9:         ctx.Database.ExecuteSqlCommand("CREATE APPLICATION ROLE TestAppRole WITH PASSWORD = 'secure_password', DEFAULT_SCHEMA = [dbo]");

  10:         ctx.Database.ExecuteSqlCommand("GRANT CONTROL ON DATABASE::TestAppDB TO TestAppRole");

  11:     }

12:

  13:     //update database with application role set

  14:     DbInterception.Add(new EFDBConnectionApplicationRoleInterception("TestAppRole", "secure_password", "TestAppDB"));

  15:     ctx.Database.Initialize(false);

  16: }

Short description:

Line 1: Setting the MigrateToLatestVersion initializer and tell it to use database connection embedded in DbContext

Line 2: Call migrations to create and initialize database if it does not exist

Line 9: Create application role TestAppRole on database

Line 10: Granting a control permission on database to the application role

Line 14: Introducing a connection interceptor so from this moment every database connection is held under the application role

Line 15: Here we initialize and or update the database which already exists

As you probably noticed the EFDBConnectionApplicationRoleInterception also requires the database name to be provided. This is because the migration initializer connects to the master database in some circumstances, which does not have a application role defined. The interceptor simply do not call sp_setapprole and sp_unsetapprole then. That’s it.

Sources: https://hlucianojr.wordpress.com/2014/09/11/how-to-implement-application-role-with-entity-framework-6-1/

Dipna

4/17/2015 4:04 AM

This is exactly what i was looking for. But, How do you get the interface IDbConnectionInterceptor? I couldnt find which namespace it is part of.

perplexed

4/17/2015 4:58 AM

I did the same as you have mentioned, using IDbInterceptor. But the Opened or closing methods doesnt get called when i create a new instance of the context and execute a query. I dont understand how that would be called, if it hasnt been invoked.

Anonymous

12/6/2017 7:49 PM

If a transaction fails while in the ApplicationRole, The DeActivateApplicationRole function fails with the exception: ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.