Sql Server application roles with Entity Framework Code First Migrations

By Mirek on (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

And then register it before we use our database context

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

  1. 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
  2. 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.

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/