使用VS2012 MVC4连接SQL Server2012遇EntityException异常求助

Hey there, let's work through this connection issue you're hitting. That EntityException with "The underlying provider failed on Open" almost always boils down to problems with how your app is trying to reach SQL Server—let's break down the most common fixes step by step:

1. Double-Check Your Connection String #

First, head to your web.config file and look at the <connectionStrings> section. This is the #1 culprit for this error.

Common Mistakes to Fix: #

• Server Name Mismatch: Make sure the Data Source value matches exactly what you use to connect in SQL Server Management Studio 2012. For local instances, this might be (local), localhost, .\SQLEXPRESS, or your machine name followed by the instance (e.g., DESKTOP-ABC123\MSSQLSERVER).
• Database Name Typos: Verify Initial Catalog is the exact name of your target database in SSMS.
• Authentication Issues:
  • For Windows Auth (most common for local dev): Ensure you have Integrated Security=True; in the string.
  • For SQL Server Auth: Double-check User ID and Password are correct, and the account has access to the database.

Example valid connection string (Windows Auth):

<connectionStrings>
  <add name="EmployeeContext" 
       connectionString="Data Source=(local);Initial Catalog=YourEmployeeDB;Integrated Security=True;" 
       providerName="System.Data.SqlClient" />
</connectionStrings>

Also, confirm your EmployeeContext class is using the correct connection string name:

public class EmployeeContext : DbContext
{
    // Match the name attribute from web.config
    public EmployeeContext() : base("EmployeeContext") { }

    public DbSet<Employee> Employees { get; set; }
}

2. Ensure SQL Server Service is Running #

If SQL Server isn't running, your app can't connect at all:

• Press Win + R, type services.msc, and hit Enter.
• Look for SQL Server (MSSQLSERVER) (or your specific instance name if you installed a named instance).
• If the status isn't "Running", right-click it and select Start.

3. Verify Database Permissions #

• Windows Auth: The user account you're running Visual Studio with needs permission to access the database. In SSMS, expand your database → Security → Users, and make sure your Windows account is added with at least db_datareader and db_datawriter roles.
• SQL Auth: In SSMS, check that the SQL account you're using has access to the database, and the password hasn't expired or been changed.

4. Test the Connection Outside Entity Framework #

To rule out EF-specific issues, add a quick test in your Details action to directly test the connection string:

public ActionResult Details(int id)
{
    // Add this temporary test code
    string connString = System.Configuration.ConfigurationManager.ConnectionStrings["EmployeeContext"].ConnectionString;
    using (var sqlConn = new System.Data.SqlClient.SqlConnection(connString))
    {
        try
        {
            sqlConn.Open();
            // If this runs, the connection works—issue is elsewhere in EF setup
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            // This will give you a more specific error message (e.g., login failed, server not found)
            return Content($"Connection Error: {ex.Message}");
        }
    }

    // Your original code
    EmployeeContext employeeContext = new EmployeeContext();
    Employee employee = employeeContext.Employees.Single(x => x.EmployeeId == id);
    return View(employee);
}

This will give you a precise error message that's more helpful than EF's generic exception.

5. Confirm the Database Exists #

Quick sanity check: Open SSMS, connect to your server, and make sure the database name in your connection string actually exists (no typos, no accidental deletion).

内容的提问来源于stack exchange，提问作者Deepali