EF with SQL server application role

Question

I need to use application rule security on sql server. And i want to use Enity Framework Code First.

After a successful login my connection is set to application role. I then create my DbContext using this connection.

But: EF expects a closed connection object. And closing a connection drops the application role.

How can i solve this dilemma?

Answer 1

I managed to get this work with two steps:

Switch connection pooling off, which is mentioned all the time for connections using application roles. As i have a desktop application, this is no problem for me.

Add a handler to DbConnection.StateChanged and activate the application role on every opening of the connection. Without connection pooling, it is not necessary to sp_unsetapprole on closing. So this works for me:

context.Database.Connection.StateChanged += (sender, args) =>
  if (args.CurrentState == ConnectionState.Open) {
    activateAppRole((DbConnection)sender, ...);
  }
}

I guess, if Pooling is vital for someone, she may call sp_unsetapprole on closing the connection in this same handler.