One reason of “The underlying provider failed on Open” Entity Framework exception in Sharepoint

When you use Entity Framework data model in Sharepoint (e.g. in custom web part with server object model) you may face with the following problem: when try to query database the following exception is thrown:

The underlying provider failed on Open

If you will search for solution for this problem most of suggestions in forums will say that you need to grant access for account of IIS application pool to the database. In Sharepoint however it is only half of solution. The problem is that in Sharepoint impersonation is used by default, which mean that if you just grant access to app pool account it still may not work, because actual code will run under account of currently authenticated user (check Thread.CurrentPrincipal property in order to know exact account). As in most cases we don’t want to explicitly grant database access for all users of our web application the simplest solution will be grant access to account of application pool as it is suggested and then switch current context user to account of application pool. The simplest way to do it in Sharepoint context is to use SPSecurity.RunWithElevatedPrivileges:

   1: SPSecurity.RunWithElevatedPrivileges(

   2:     () =>

   3:     {

   4:         // query database via EF

   5:     }

   6: );

In this example we don’t need to reopen SPSite under SPSecurity.RunWithElevatedPrivileges as context user switch will happen anyway. Also note that SPSecurity.RunWithElevatedPrivileges has effect only when code runs in context of Sharepoint web app, but not e.g. in console applications – in last case connection will be done with account of the user under which current console application is running.

After that mentioned error should disappear.