MSSQL Error 'The underlying provider failed on Open'

I found the problem was that I had the server path within the connection string in one of these variants:

SERVER\SQLEXPRESS
SERVER

When really I should have:

.\SQLEXPRESS

For some reason I got the error whenever it had difficulty locating the instance of SQL.

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.

I had this error and found a few solutions:

Looking at your connection string, it looks valid. I found this blog post, the problem here is that they were using Integrated Security. If you are running on IIS, your IIS user needs access to the database.

If you are using Entity Framework with Transactions, Entity Framework automatically opens and closes a connection with each database call. So when using transactions, you are attempting to spread a transaction out over multiple connections. This elevates to MSDTC.

(See this reference for more information.)

Changing my code to the following fixed it:

using (DatabaseEntities context = new DatabaseEntities())
{
    context.Connection.Open();
    // the rest
}

You should see innerException to see what the inner cause of throwing of error is.

In my case, the original error was:

Unable to open the physical file "D:\Projects2\xCU\xCU\App_Data\xCUData_log.ldf". Operating system error 5: "5(Access is denied.)". An attempt to attach an auto-named database for file D:\Projects2\xCU\xCU\App_Data\xCUData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

which solved by giving full permission to current user for accessing related mdf and ldf files using files' properties.