Entity Framework The underlying provider failed on Open
In my case, I was using Sql CE, and my connection string was set up with an absolute path instead inf the |DataDirectory| variable. Changed this and it began working on the server.
Obviously, it worked fine on the development machine.
I was getting the same problem and after doing debugging i saw that i am creating the new instance of DB Entity on every action and making new instance of Db Entity means openning new connection with db.
Below is the code :
Private tmpConnection As New DbModel.DbEntities
so by calling the variable again and again its creating new instance of DbEntites and opening new connection to db.
so i write a small function for this and that solved my problem. Now no more error.
Private tmpConnection As DbModel.DbEntities
Public Function dbCon() As DbModel.DbEntities
If tmpConnection IsNot Nothing Then
If tmpConnection.Connection.State = ConnectionState.Closed Then
Try
tmpConnection.Connection.Open()
Return tmpConnection
Catch ex As Exception
My.Response.Redirect("dberror.aspx")
End Try
Else
Return tmpConnection
End If
Else
tmpConnection = New DbModel.DbEntities
Try
tmpConnection.Connection.Open()
Return tmpConnection
Catch ex As Exception
My.Response.Redirect("dberror.aspx")
End Try
End If
Return Nothing
End Function
and last thing in your connectionstring please add "Connect Timeout=30;"
thats working so perfect for me
You should create a sql account for your web server to access the aspnetdb database. It is currently using integrated authentication (tries to logon with the identity the web server is using to run the application).
The example below uses integrated auth. I would use SQL auth though.
http://msdn.microsoft.com/en-us/library/ff649314.aspx
Using new instance of DB Entity on every action should not physically create connection to your SQL server. Entity Framework will use connection pool created for your (process, app domain, connection string) as configured in your connection string to avoid creating new connections.
This issue is very environmental and tweaking parameters (in your conn string) like below should resolve the problem-
Min Pool Size=1;
Max Pool Size=100; // default
Connect Timeout=15; // in seconds
Pooling=true;