Problem with Entity Framework : "The underlying provider failed on Open"

Question

Hi, 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

Answer 1

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

Answer 2

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"/>