Entity Framework 4 and SQL Server App Roles - How to Work Together?

Question

I’m researching EF4 for a new in-house application development project using .NET v4, EF4, & SQL Server 2008 R2. To date, our small dev team has done very little .NET development and only demonstration EF applications. Our current applications use DB app-roles for security and that's worked out well for us.

From reading and some basic experimentation, my understanding is:

• EF can open and close DB connections as needed. However it is possible to manually open and close an EntityConnection for use by the EF ObjectContext.

• SQL Server app-role security requires running sp_setapprole on DB connections to set the application role context. sp_unsetapprole can be used to revert a connection to its original context.

• By default, DB connections are pooled. Using sp_setapprole with connection pooling can be problematic if the connections are not restored to their original context before being returned to the pool.

If all the above is correct then the obvious way to use EF4 with app-roles is to manually open & close the EntityConnection, being sure to execute sp_setapprole after opening and sp_unsetapprole before closing.

Is there a better way? I'm mostly concerned about accidentally closing the connection without first calling sp_unsetapprole. Seems like an error that may not be noticed immediately.

Answer 1

You can just add "Pooling=false;" to your store connection in the app.config (Provider Connection String).
If you don't actually need pooling, this seems to be the simplest solution.