Entity Framework The underlying provider failed on Open

Question

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

Answer 1

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.

Answer 2

1. Search "Component Services" in Programs and Files
2. Go to Services
3. Find "Distributed Transaction Coordinator" Service
4. Right click and Restart the Service

You've just done a restart of the service and the code should run without errors

Answer 3

We had connection string in web.config with Data Source=localhost, and there was this error (MSSQL was on the same machine). Changing it to actual `DOMAIN\MACHINE' helped, somewhy.

Answer 4

Possible solution is described in this Code Project tip:

As folks mentioned IIS user network service user credentials while trying to log in sql server. So just change the Application pool settings in your IIS:

1. Open Internet Information Service Manager
2. Click on Application Pools in left navigation tree.
3. Select your version Pool. In my case, I am using ASP .Net v4.0. If you dont have this version, select DefaultAppPool.
4. Right click on step 3, and select advanced settings.
5. Select Identity in properties window and click the button to change the value.
6. Select Local System in Built-in accounts combo box and click ok. That's it. Now run your application. Everything works well.