Im exploring EF4 for a new proprietary application development project using .NET v4, EF4, and SQL Server 2008 R2. To date, our small development team has done very little .NET development and only demo EF applications. Our current applications use DB applications for security, and it worked well for us.
From reading and some basic experiments, I understand:
EF can open and close DB connections as needed. However, you can manually open and close the EntityConnection for use in the EF information object.
For application security for SQL Server, sp_setapprole must be run on DB connections to establish the context of the application role. sp_unsetapprole can be used to return the connection to its original context.
By default, DB connections are merged. Using sp_setapprole with a connection pool can be problematic if the connection does not return to its original context before returning to the pool.
If all of the above is true, then the obvious way to use EF4 with application roles is to manually open and close the EntityConnection, while sp_setapprole after opening and sp_unsetapprole before closing are mandatory.
Is there a better way? I am mostly concerned about accidentally closing the connection without first calling sp_unsetapprole. It seems like an error that can be noticed immediately.