title: Entity Framework 4 and SQL Server App Roles - How to Work Together?
body: <p>I’m researching EF4 for a new in-house application development project using .NET v4, EF4, &amp; 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. </p>
  
  <p>From reading and some basic experimentation, my understanding is:</p>
  
  <ul>
  <li><p>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.</p></li>
  <li><p>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.</p></li>
  <li><p>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.</p></li>
  </ul>
  
  <p>If all the above is correct then the obvious way to use EF4 with app-roles is to manually open &amp; close the EntityConnection, being sure to execute sp_setapprole after opening and sp_unsetapprole before closing.</p>
  
  <p><strong>Is there a better way?</strong> I'm mostly concerned about accidentally closing the connection without first calling sp_unsetapprole. Seems like an error that may not be noticed immediately.</p>