The underlying provider failed on Open - EF 6.1, MSSQL 2008 r2

Question

when Our WinApp is idle for while around 8-10 minutes ,and try to do something like get list of customers, we got this error "The underlying provider failed on Open"

i use EF6.1 & MSSQL 2008 r2 Here Is my connection string :

<add 
name="MISSystemEntities" 
connectionString="metadata=res://*/DB_Model.csdl|res://*/DB_Model.ssdl|res://*/DB_Model.msl;
              provider=System.Data.SqlClient;
              provider connection string=&quot;Data Source=10.10.0.3\r2;Initial Catalog=MISSystem;User ID=user;Password=pass;MultipleActiveResultSets=True&quot;" 
providerName="System.Data.EntityClient"
/>

Sql Option Auto Close Is Off

Distributed Transaction Coordinator Is Active for Clients "actually We don't use it"

have ping to server without any time aout at the same time that error occurs

test with domain administrator rights and still have problem

also We Test Connection time out and query execute time out with random values:(

Answer 1

Its seems related the connection issue.

Seems like a connection issue. You can use the Data link properties to find if the connection is fine. Do the following,    
Create a blank notepad and rename it to "X.UDL" Double click open it, under connections tab choose the server name/enter the name use the correct credentials and DB OK to save it.    
Now open the file in Notepad and check, compare the connection string properties with this..

Refer link:- Entity Framework The underlying provider failed on Open

The underlying provider failed on open entity framework

Please read the link

Here is same issue explain to resolve by Microsoft team member.

http://blogs.msdn.com/b/dataaccesstechnologies/archive/2012/08/09/error-quot-the-underlying-provider-failed-on-open-quot-in-entity-framework-application.aspx

Answer 2

my problem (issue) was SQL server Connection pooling :D the pooling store the last request time and when u idle for while around 5-10 min and then when it receive request it'll answer the request ( !??! ) but after that the pooling terminate your connection and your next request throw the exception :(

for now i set timer to each 30s send a request to DB !

Answer 3

i was using entity framework 6 with wcf web service with "integrated security=true" in the wcf web.config. the error message was similar. what was happening, iis was using the application pool credentials to connect to the database. once i specified a user account in the connection string, it worked like a charm...

hope this helps.