MSSQL Error 'The underlying provider failed on

Question

I was using an .mdf for connecting to a database and entityClient. Now I want to change the connection string so that there will be no .mdf file.

Is the following connectionString correct?

<connectionStrings>
   <!--<add name="conString" connectionString="metadata=res://*/conString.csdl|res://*/conString.ssdl|res://*/conString.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQL2008;AttachDbFilename=|DataDirectory|\NData.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />-->
   <add name="conString" connectionString="metadata=res://*/conString.csdl|res://*/conString.ssdl|res://*/conString.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQL2008;Initial Catalog=NData;Integrated Security=True;Connect Timeout=30;User Instance=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

Because I always get the error:

The underlying provider failed on Open

Answer 1

In my case I had a mismatch between the connection string name I was registering in the context's constructor vs the name in my web.config. Simple mistake caused by copy and paste :D

    public DataContext()
        : base(nameOrConnectionString: "ConnStringName")
    {
        Database.SetInitializer<DataContext>(null);
    }

Answer 2

A common mistake that I did because I was moving application from once pc to another and none of the above worked was that I forgot to copy the connection string to both App.Config and Web.Config!

Answer 3

i have the same error i found thats when i change my connectionString to new Data Source i forget to change the Username and passowrd for the new database

Answer 4

in my case server address have been changed by server admin so i had to change connection string to new server address

Answer 5

I have also had this error happen if the SQL Server Instance name isn't specified and the SQL host has multiple SQL instances installed. Here's a couple examples to clarify:

The connection string below results in the exception "The underlying provider failed on Open" with no inner exception in a .NET WebForms app:

 connectionString="metadata=res://*/Entities.csdl|res://*/Entities.ssdl|res://*/Entities.msl;provider=System.Data.SqlClient;provider connection string="Data Source=localhost;Initial Catalog=Entities;User ID=user;Password=password;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient"

The following connection string executes as expected in a .net WebForms app where the SQL environment has multiple instances. Rare I know, but I have a few different SQL instances on my dev box to accommodate different projects:

 connectionString="metadata=res://*/Entities.csdl|res://*/Entities.ssdl|res://*/Entities.msl;provider=System.Data.SqlClient;provider connection string="Data Source=localhost\SQLSERVER2014;Initial Catalog=Entities;User ID=user;Password=password;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient"

Answer 6

context.Connection.Open() didn't help solving my problem so I tried enabling "Allow Remote Clients" in DTC config, no more error.

In windows 7 you can open the DTC config by running dcomcnfg, Component Services -> Computers -> My Computer -> Distributed Transaction Coordinator -> Right click to Local DTC -> Security.