rbr94 rbr94 - 1 month ago 34
C# Question

MSSQL Error 'The underlying provider failed on Open' using WCF & EntityFramework

I use the EntityFramework 6 to make database queries:

public class Entities : DbContext {
public Entities ();

public DbSet<Analysis> Analysis { get; set; }
}

public class TableController {
public function List<DataSet> GetDataSets () {

var query = "SELECT ... FROM ... WHERE ...";

var queryReturnType = typeof(Analysis);

var result = this.Entities.Database.SqlQuery(queryReturnType, query);

foreach (var elem in result) {
...
}
}
}


I use all of this in a self-hosting WCF-service, which allows multi-threading:

[ServiceBehavior(InstanceContextMode = InstanceContextMode.Single, UseSynchronizationContext = false, ConcurrencyMode = ConcurrencyMode.Multiple)]
public class RestService : IRestService {
...
}


The query works fine for 90%-95% of all requests, but sometimes I receive an exception from the EntityFramework at the line where I try to iterate over the results of the query (
foreach
):


Error with underlying provider with open

at System.Data.Entity.Core.EntityClient.EntityConnection.Open()

at System.Data.Entity.Core.Objects.ObjectContext.EnsureConnection(Boolean shouldMonitorTransactions)

at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func'1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)

at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass65'1.b__63()

at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func'1 operation)

at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryReliably[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters)

at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQuery[TElement](String commandText, ExecutionOptions executionOptions, Object[] parameters)

at System.Data.Entity.Internal.InternalContext.<>c__DisplayClass14'1.b__13()

at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()


The service is hosted on a Windows Server 2012 R2 and when I execute the service on a local Windows 10 machine, there error does not occure.

What may cause the error?

Answer

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.)

Please read this article: MSSQL Error 'The underlying provider failed on Open'

Entity Framework The underlying provider failed on Open

Error: The underlying provider failed on Open. How to resolve it?