Problem
I was using entityClient and an.mdf to connect to a database. Now I’d like to alter the connection string such that no.mdf file is created.
Is the connectionString in the example below correct?
<connectionStrings>
<!--<add name="conString" connectionString="metadata=res://*/conString.csdl|res://*/conString.ssdl|res://*/conString.msl;provider=System.Data.SqlClient;provider connection string="Data Source=.\SQL2008;AttachDbFilename=|DataDirectory|\NData.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True;MultipleActiveResultSets=True"" 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="Data Source=.\SQL2008;Initial Catalog=NData;Integrated Security=True;Connect Timeout=30;User Instance=True;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient" />
I usually receive the following error:
Asked by senzacionale
Solution #1
I encountered this problem and came up with the following solutions:
Your connection string appears to be legitimate. I came across this blog article; the issue is that they were employing Integrated Security. If you’re using IIS, you’ll need to give your IIS user access to the database.
With each database call, Entity Framework automatically starts and closes a connection if you’re using Transactions with Entity Framework. When you use transactions, you’re trying to spread a transaction across several connections. This progresses to MSDTC.
(For more details, see this link.)
It was fixed by changing my code to the following:
using (DatabaseEntities context = new DatabaseEntities())
{
context.Connection.Open();
// the rest
}
Answered by Christian Payne
Solution #2
context.Connection. When Open() failed to solve my problem, I tried activating “Allow Remote Clients” in DTC config, and the error disappeared.
Running dcomcnfg, Component Services -> Computers -> My Computer -> Distributed Transaction Coordinator -> Right click to Local DTC -> Security in Windows 7 will open the DTC configuration.
Answered by kerem
Solution #3
You should look at innerException to discover what is causing the error to be thrown.
The initial blunder in my instance was:
This was solved by giving the current user complete permission to view associated mdf and ldf files via file properties.
Answered by Majid
Solution #4
I discovered the issue was that I had the server path in one of these connection string variants:
SERVER\SQLEXPRESS
SERVER
When I should have done the following instead:
.\SQLEXPRESS
I got the error for some reason whenever it couldn’t find the SQL instance.
Answered by dooburt
Solution #5
This is a very common problem. This is a problem that I, too, have had to deal with. It worked well on the development machine, which was set up with Windows authentication:
<add name="ShoppingCartAdminEntities" connectionString="metadata=res://*/ShoppingCartAPIModel.csdl|res://*/ShoppingCartAPIModel.ssdl|res://*/ShoppingCartAPIModel.msl;provider=System.Data.SqlClient;provider connection string="data source=.\SQlExpress;initial catalog=ShoppingCartAdmin;Integrated Security=True;multipleactiveresultsets=True;application name=EntityFramework"" providerName="System.Data.EntityClient" />
I get the following problem when hosted in IIS with the same configuration:
It was resolved by switching the connection. In the configuration file, there is a string that says:
<add name="MyEntities" connectionString="metadata=res://*/ShoppingCartAPIModel.csdl|res://*/ShoppingCartAPIModel.ssdl|res://*/ShoppingCartAPIModel.msl;provider=System.Data.SqlClient;provider connection string="data source=MACHINE_Name\SQlExpress;initial catalog=ShoppingCartAdmin;persist security info=True;user id=sa;password=notmyrealpassword;multipleactiveresultsets=True;application name=EntityFramework"" providerName="System.Data.EntityClient" />
The following are some examples of common errors:
Answered by JaiSankarN
Post is based on https://stackoverflow.com/questions/2475008/mssql-error-the-underlying-provider-failed-on-open