EntityException was unhandled the underlying provider failed to open

Question

I am using VisualStudio2012 for the development. I have created a class library EMPDAL where I am using Employees table of NorthWnd database to connect with Entity Framework and I tested the connection while configuring the database. I have written below code to get employees details.

public class EmplooyeeData
{
    public static List<Employee> GetEmployees( int EmployeeId)
    {
        using (DbEntities dbContext = new DbEntities())
        {
            return dbContext.Employees.Where(x => x.EmployeeID == EmployeeId).ToList();
        }
    }
}

I created a console application to use this EMPDAL so I had given the reference to my EMPDAL and using below code to retrieve employee details

static void Main(string[] args)
{
   List<EmpDAL.Employee> emp = new List<EmpDAL.Employee>();
   emp = EmpDAL.EmplooyeeData.GetEmployees(1);
}

But when client code calls GetEmployees(1) and debugger goes in to the EMPDAL.EmpDataGetEmployess method then in return statement it throws the exception ![Exception is shown below]

The underlying provider failed on Open.

AppConfig I have used same in my class library and client application.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="DbEntities" connectionString="metadata=res://*/EmployeeModel.csdl|res://*/EmployeeModel.ssdl|res://*/EmployeeModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=localhost;initial catalog=NorthWnd;user id=sa;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  </entityFramework>
</configuration>

probably the connection string in the config needs to be copied from your DAL to your console application, what is the inner exception ? — Yuliam Chandra, Jul 27 '14 at 05:43
I have added my Appconfig detail in my post and also I am using same appconfig for my client applicaiton. — Raman, Jul 27 '14 at 10:27
The inner exception is "The underlying provider failed on Open." — Raman, Jul 27 '14 at 10:38
try to add `Integrated security=True;`, after `MultipleActiveResultSets=True;` — Yuliam Chandra, Jul 27 '14 at 10:48
Thanks Yuliam..I worked for me. what exactly purpose of this statement. — Raman, Jul 27 '14 at 11:05
check [this answer](http://stackoverflow.com/questions/1229691/difference-between-integrated-security-true-and-integrated-security-sspi) — Yuliam Chandra, Jul 27 '14 at 11:10

Stu1986C · Answer 1 · 2014-07-27T17:40:17.900

You've made mention of at least two tiers to your application (class library and a console application). N-tier applications require you to have the connection string for your EF database to be declared in the web.config / app.config of all tiers of your application that Entity Framework is referenced in.

Ensure you have the connection string for your database listed in your console applications app.config as well as the class library's app.config.

As per the above comments from @Yuliam, it is worth ensuring you have the following in your connection strings:

Integrated security=True;

What this does is When it is set to True, .Net tries to open the connection with the current user event if you specify a user and password. Set the Integrated Security to False if you want to open the connection string as a specific user.

I have added my Appconfig detail in my post and also I am using same appconfig for my client applicaiton. — Raman, Jul 27 '14 at 10:25

EntityException was unhandled the underlying provider failed to open

1 Answers1