Connection to the database failed. Check that connection string is correct and DbContext constructor

asp.net-mvc c# entity-framework sql-server

Question

An error occurred accessing the database. This usually means that the connection to the database failed. Check that the connection string is correct and that the appropriate DbContext constructor is being used to specify it or find it in the application's config file.

I swear I tried everything! Going through this tutorial and struggling to get connection to the database.

My table in SQL Server 2012 is called, "tblEmployee" and the database is called "Sample"

This is the Employee.cs class

[Table("tblEmployee")]
public class Employee
{
    public int EmployeeID { get; set; }
    public string Name { get; set; }
    public string City { get; set; }
    public string Gender { get; set; }
}

This is the employee context model class

public class EmployeeContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }
}

This is the EmployeeControler

public class EmployeeController : Controller
{
    public ActionResult Details()
    {
        EmployeeContext employeeContext = new EmployeeContext();
        Employee employee = employeeContext.Employees.Single(emp => emp.EmployeeID == 2);
        return View(employee);
    }
}

This is the web.config file

<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
    <parameters>
        <parameter value="v11.0" />
    </parameters>
   </defaultConnectionFactory>
    <providers>
    <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
</entityFramework>

And this is the connection string

<connectionStrings>
    <add name="EmployeeContext" connectionString="Data Source=ADMIN-PC;Initial Catalog=Sample;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False" providerName="System.Data.SqlClient" />
</connectionStrings>

Nothing special on the actual View

@{
ViewBag.Title = "Employee Details";
}

<h2>Employee Details</h2>

<table style="font-family:Arial">
    <tr>
        <td>EmployeeID:</td>
        <td>@Model.EmployeeID</td>
    </tr>
    <tr>
        <td>Name:</td>
        <td>@Model.Name</td>
    </tr>
    <tr>
        <td>Gender:</td>
        <td>@Model.Gender</td>
    </tr>
    <tr>
        <td>City:</td>
        <td>@Model.City</td>
    </tr>
</table>

This is the stack trace

at System.Data.Entity.ModelConfiguration.Utilities.DbProviderServicesExtensions.GetProviderManifestTokenChecked(DbProviderServices providerServices, DbConnection connection)
at System.Data.Entity.DbModelBuilder.Build(DbConnection providerConnection)
at System.Data.Entity.Internal.LazyInternalContext.CreateModel(LazyInternalContext internalContext)
at System.Data.Entity.Internal.RetryLazy`2.GetValue(TInput input)
at System.Data.Entity.Internal.LazyInternalContext.InitializeContext()
at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType)
at System.Data.Entity.Internal.Linq.InternalSet`1.Initialize()
at System.Data.Entity.Internal.Linq.InternalSet`1.get_InternalContext()
at System.Data.Entity.Infrastructure.DbQuery`1.System.Linq.IQueryable.get_Provider()
at System.Linq.Queryable.Single[TSource](IQueryable`1 source, Expression`1 predicate)
at MVCDemo.Controllers.EmployeeController.Details() in c:\Users\Admin\Documents\Visual Studio 2013\Projects\MVCDemo\MVCDemo\Controllers\EmployeeController.cs:line 19
at lambda_method(Closure , ControllerBase , Object[] )
at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass37.<>c__DisplayClass39.<BeginInvokeActionMethodWithFilters>b__33()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass4f<InvokeActionMethodFilterAsynchronously>b__49()
1
6
3/26/2014 3:38:27 AM

Accepted Answer

Soooo after quite a bit of struggle with this whole thing, this is what worked for me:

-1) Uninstalled SQL Server

-2) Reinstalled SQL Server and set a new Instance(INST01)

^^I don't think any of the above actually did anything.

-3) Reconnected to the new server with Visual Studio by going to the SQL Server Object Explorer and locating the new instance.

-4) Went back to SQL Server Management Studio and recreated my simple database.

-5) Under SQL Server Object Explorer I copied the connection string from the database properties.

-6) I changed the Web properties for the Project from Local IIS to IIS Express.

-7) Uninstalled EntityFramework 6

-8) Deleted any reference to Entity Framework in web.config file(this way the code for EF 5 would be inserted upon installation)

-9) Installed EntityFramework 5.0.0 from the Package Manager Console.

-10) Ran the application and it worked.

I'm not 100% sure what the issue was but I think it has something to do with Local IIS and IIS Express. I think changing the EF framework version was necessary to make sure the syntax for calling the table was consistent.

Keep in mind that before all of this, I could establish a connection to the database by simply going into the SQL Server Object Explorer. I could even pull up the table in Visual Studio and see the Data.

Hope this helps someone :)

1
3/26/2014 6:36:03 PM

Popular Answer

Cause: when you were using IIS, your App Pool user was probably 'ApplicationPoolIdentity'. When connecting to network resources, that will connect as local system (so if in a domain, that would be the domain\computer$ account). Presumably that account does not have access to the SQL DB.

Sort of fix: Your change to IIS Express 'fixed' this, connection was made as current user. Not good if you're planning to ever deploy to IIS though.

Better fix: Change your IIS App Pool user to a true Windows user account, which has access to the SQL DB.

I have just gone through exactly same issue, and can confirm above fixes worked for me.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow