Entity Framework : "The underlying provider failed on Open"

When I try to insert a record, I get this error : The underlying provider failed on Open. This error occurs only with IIS and not with VWD 2008's webserver. In the EventViewer I get this Application Error : Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed. [CLIENT: ]

<add name="ASPNETDBEntities"
     connectionString="
         metadata=res://*/Models.FriendList.csdl|res://*/Models.FriendList.ssdl|res://*/Models.FriendList.msl;
         provider=System.Data.SqlClient;开发者_开发问答
         provider connection string=&quot;
         Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;
         Integrated Security=True;
         Connect Timeout=30;
         User Instance=True;
         MultipleActiveResultSets=True&quot;"
     providerName="System.Data.EntityClient" />

I am using aspnetdb.mdf file, and not any external database. I have searched enough for this, but no use.

Everything works fine with VWD webserver

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

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

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;

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.

Add the below attribute in the Connection string which you have given.

<add 
     name="ASPNETDBEntities" 
     connectionString="metadata=res://*/Models.FriendList.csdl|
                       res://*Models.FriendList.ssdl|res://*/Models.FriendList.msl;
                       provider=System.Data.SqlClient;provider connection string=&quot;
                       Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;
                       Integrated Security=True;Connect Timeout=30;
                       User Instance=True;
                       MultipleActiveResultSets=True&quot;" 
     ProviderName="System.Data.EntityClient" 
     User Instance="False"/>

Maybe it's too late to answer but i had the same problem.

I added scope timeout (5 minutes). I let an example.

Dim varIntervalo As New TimeSpan(0, 5, 0)
Using varTransaccion As New TransactionScope(TransactionScopeOption.Required, varIntervalo)
    For vari As Integer = 2 To varMatrizDatos.GetUpperBound(0)
        Dim varWhateverAs New TABLE
        varWhatever.ID_TABLE = something
        varWhatever.DESC_TABLE = something else
        varWhatever.DATE_TABLE = CDate(Now.Date)

        varEntidades.AddToTABLESet(varWhatever)
        varEntidades.SaveChanges()
    Next
    varTransaccion.Complete()
End Using

Maybe this code could be improve.

Additionally, this exception is thrown when the table you're going to manipulate is deleted or doesn't exist

This exception seems to be thrown when there's anything which might cause your connection string to be invalid. Invalid credentials were the cause for me.

I was having a similar problem and I am also using IIS. I opened a command window and typed in iisreset. Problem solved.

This problem comes when Database added in ServerExplorer as Windows authentication. When you use sql authentication at the time of database added in ServerExplorer the problem resolves. If still using Windows authentication and avoid this exception provide user id and password of database in connectionstring in webconfig file.

This problem arises only when we host the service in IIS