C# – Entity Framework The underlying provider failed on Open

centity-framework

Below is my connection string:

connectionString="metadata=res://*/EDMX.Test.csdl|res://*/EDMX.Test.ssdl|res://*/EDMX.Test.msl;provider=System.Data.SqlClient;provider
connection string="Data Source=home_computer;Initial
Catalog=db_Test;Persist Security Info=True;User
ID=testUser;Password=$1234;MultipleActiveResultSets=True""

Here is the code where the program stuck:

EDMX.TestingEntity context = new EDMX.TestingEntity();

var query = from t in context.User
            where t.UserName == _userName
            select t;

After running the above code, I check the variable query and found an exception

The underlying provider failed on Open.

I've checked:

  1. Connection between server and computer is normal
  2. I can login to the database with username testuser and with password $1234
  3. I have checked the security settings in database (SQL Server) that permission has been granted to testUser

Why does this exception happen? I'm using .net 4.5


Added:

I tried again, look at the inner exception and it was:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

I know it might be a network problem but I've turned off the firewall of server and also my computer and tried again but still not success..


Just now Copied the connectionstring to a program to test this connection and it was functioning well..


I just rollback all changes and test again and it worked

Best Answer

Seems like a connection issue. You can use the Data link properties to find if the connection is fine. Do the following:

  1. Create a blank notepad and rename it to "X.UDL"
  2. Double click to open it
  3. Under connections tab choose the server name/enter the name use the correct credentials and DB
  4. Click OK to save it.

Now open the file in Notepad and compare the connection string properties.