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.

Answer 5

Always check for Inner Exception if any. In my case Inner Exception turned out to be really helpful in figuring out the issue.

My site was working fine in Dev Environment. But after i deployed to production, it started giving out this exception, but the Inner Exception was saying that Login failed for the particular user.
So i figured out it was something to do with the connection itself. Hence tried logging in using SSMS and even that failed.

Eventually figured out that exception showed up for the simple reason that the SQL server had only Windows Authentication enabled and SQL Authentication was failing which was what i was using for Authentication.

In short, changing Authentication to Mixed(SQL and Windows), fixed the issue for me. :)

Answer 6

Please check the following things first.

While generating the Edmx you would have given a name to you connection string. that gets into the app config of the project with the Entity.

Have you copied the same connection string to your main Config file. Also the Name should be same as which you have given while generating the EDMX file.

Answer 7

My client reported this error. I found that he was messing with *.ldf files. He copied *ldf file on one database and renamed it to match a second database (which I asked him to place in a folder).

I replicated the same scenario, and got this same error in my development system. Error got fixed after deleting the *ldf file(s).

Answer 8

I saw this error when a colleague was trying to connect to a database that was protected behind a VPN. The user had unknownling switched to a wireless network that did not have VPN access. One way to test this scenario is to see if you can establish a connection in another means, such as SSMS, and see if that fails as well.

Answer 9

For me when that usually starts happening, I have to remote desktop into the service and at the minimum restart IIS. It usually starts popping up right after I deploy code. On a few rare occasions I have had to restart the SQL services and IIS. I wrote a batch script to take a param (1 or 2) and have it setup to either do a restart of IIS ( i.e. 1), or go full nuclear (i.e. 2).

Answer 10

I get this exception often while running on my development machine, especially after I make a code change, rebuild the code, then execute an associated web page(s). However, the problem goes away for me if I bump up the CommandTimeout parameter to 120 seconds or more (e.g., set context.Database.CommandTimeout = 120 before the LINQ statement). While this was originally asked 3 years ago, it may help someone looking for an answer. My theory is VisualStudio takes time to convert the built binary libraries to machine code, and times out when attempting to connect to SQL Server following that just-in-time compile.

Answer 11

In my case, I resolved the error by adding connection password in the connection string.

While setting up the EF model, I had selected the option to exclude sensitive data from connection string. So, the password was not included initially.

Answer 12

I got this problem while continuing execution of a unit test that calls a method that is using parallel processing.I know there are parts of EF that are not thread-safe, so I am wondering if it is a conflict where the connection is being open and closed out of sync with the operations.

My stack trace showed this:

     at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
   at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
   at System.Threading.Tasks.Task.Wait()
   at System.Threading.Tasks.Parallel.ForWorker[TLocal](Int32 fromInclusive, Int32 toExclusive, ParallelOptions parallelOptions, Action`1 body, Action`2 bodyWithState, Func`4 bodyWithLocal, Func`1 localInit, Action`1 localFinally)
   at System.Threading.Tasks.Parallel.ForEachWorker[TSource,TLocal](IList`1 list, ParallelOptions parallelOptions, Action`1 body, Action`2 bodyWithState, Action`3 bodyWithStateAndIndex, Func`4 bodyWithStateAndLocal, Func`5 bodyWithEverything, Func`1 localInit, Action`1 localFinally)
   at System.Threading.Tasks.Parallel.ForEachWorker[TSource,TLocal](IEnumerable`1 source, ParallelOptions parallelOptions, Action`1 body, Action`2 bodyWithState, Action`3 bodyWithStateAndIndex, Func`4 bodyWithStateAndLocal, Func`5 bodyWithEverything, Func`1 localInit, Action`1 localFinally)
   at System.Threading.Tasks.Parallel.ForEach[TSource](IEnumerable`1 source, Action`1 body)

So that's the clue I followed. When I went back to a single-thread foreach instead of Parallel.ForEach the issue went away.

Joey

Answer 13

I had this error and it was caused by a typo in the connection string in App.config.

Answer 14

I was facing the same error today, what I was doing wrong was that I was not adding Password tag in the connection string. As soon as I added the Password tag with correct password the error went away. Hope it helps someone.

Answer 15

I faced the same issue. Though in my case I was trying to connect my desktop application to a remote db. So for me, all the above didn't work. I solve this problem by just adding the port (as 128.02.39.29:3315) and it magically works! The reason why I didn't bother to add the port in the first place is because I used same approach (without the port) in another desktop app and it worked. So I hope this might help someone as well.

Answer 16

This solution is for someone facing this issue while deploying a windows application,

I know this is late, but this maybe useful for someone in future, In my scenario, purely this is a connection string issue I developed a windows application using entity framework (DB First approach) and I published it, the code was worked fine on my machine, but it's not worked on the client machine

Reason for this issue:-

I updated the client machine connection string in App.config file, but this is wrong, if that is a windows application, then it will not read the connection string from App.config (For deployed machines), it will read from .exe.config file,

So after deployment, we need to change the connection string in "AppicationName".exe.config file

Answer 17

Try this- Open the command prompt as administrator and type this netsh Winsock reset

Restart your system and try again.

Answer 18

If you are using a local .mdf file, probably a sync software such Dropbox attempted to sync two log files (.ldf) in two different computers you can delete the log files from the bin Directory and make sure the .mdf properties->Copy to Output Directory ->Copy if newer that will copy the selected DB file and it's log to the bin Directory. !Alert- if your DB file has only changed in the bin Directory all the changes ill be discarded!

Answer 19

open SQL Server Configuration Manager then click on sql server services a list will be displayed from the list right click sql server and click on start

Answer 20

ERROR : An exception of type 'System.Data.Entity.Core.EntityException' occurred in EntityFramework.SqlServer.dll but was not handled in user code Additional information: The underlying provider failed on Open.

SOLUTION:

• Add in Model:

   [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   [Key] 
  

• Namespace:

  using System.ComponentModel.DataAnnotations;
  using System.ComponentModel.DataAnnotations.Schema;
  

• Example:

  namespace MvcApplication1.Models
  {
    [Table("tblEmployee")]
    public class Employee
    {
      [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
      [Key]
      public int EmplyeeID { get; set; }         
      public string Name { get; set; }
      public string Gender { get; set; }
      public string City { get; set; }      
    }
  }