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

Question

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?

I don't think it's got anything to do with WCF – tom redfern Oct 25 '16 at 11:04 — tom redfern, Oct 25 '16 at 11:04

score 1 · Answer 1 · edited Oct 27 '16 at 13:48

I got the same issue with EF. At beginning, the exception was thrown once a day, after 10 times/day and recently 48 times/day. Of course, we cannot reproduce this "The underlying provider failed on open". I did everything, be sure we don't leak connections, always use

using (var context = new DataContext()) {
    List<Billing> result = (from item in context.Billing.AsNoTracking()
                            select item).ToList();
    return result;
}

For us, the solution was to force the opening of the connection.

public DataContext() : base("your connectionstring")               
    **this.Database.Connection.Open();**
    this.Configuration.LazyLoadingEnabled = false;
    this.Configuration.ProxyCreationEnabled = false;
}

Hope it works for you, too!

Welcome to SO and thanks for your reply! I just do it the way it was proposed in here: http://stackoverflow.com/questions/2475008/mssql-error-the-underlying-provider-failed-on-open?answertab=votes#tab-top. This works fine and the first answer provided me that link. It's just in a way as your first code block. — rbr94, Oct 27 '16 at 13:06

score 0 · Accepted Answer · edited May 23 '17 at 12:19

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?

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

2 Answers2