"The underlying provider failed on Open" with WCF, Entity Framework, and SQL Server

c# entity-framework sql-server visual-studio wcf

Question

Up until I attempt to bring in data using Entity Framework, I am successfully running tests via the WCF Test Client.

I grabbed the example code from this article, which is doing something similar, to make sure I'm not doing anything foolish: http://www.codeproject.com/KB/WCF/WCFandEF.aspx

...and when I execute it, I get the same issue at a related location:

var productEntity = (from p in context.ProductEntities 
                     where p.ProductID == id
                     select p).FirstOrDefault();

The mistake is

The underlying provider failed on Open.

The identical connection string allows me to access the database without issue from a "regular application," therefore it seems to be a problem only with the WCF test client.

The underlying provider failed on Open. usually signals a connection string issue, but I'm quite sure it's not in this situation based on searches here and on Google.

As a result, I now anticipate a permissions issue.

Windows 7 and SQL Server are both running alongside Visual Studio 2010.

Any assistance or protective head gear would be much appreciated as I have been pounding my head since yesterday.

Added connection string after editing

<add name="NorthwindEntities" 
     connectionString="metadata=res://*/Northwind.csdl|res://*/Northwind.ssdl|res://*/Northwind.msl;provider=System.Data.SqlClient;provider
connection string=&quot;Data Source=localhost;Initial Catalog=Northwind;User ID=sa;MultipleActiveResultSets=True&quot;" 
     providerName="System.Data.EntityClient" />
1
3
11/18/2011 4:41:27 PM

Accepted Answer

Your connection string is definitely at fault if you see this error.

It's a good idea to start a new (dummy) project, add an Entity Framework Data Model to it, choose "Generate Model from Database," choose the necessary connection, and then click "Test Connection" to make sure it works.
Check the box next to "Save entity conection settings in App.Config as:".
then on your website or app. You may copy and paste the Connection String from Config into your own project.

5
11/17/2011 1:07:07 PM

Popular Answer

I really experienced the same issue for two days, and yes, I discovered the answer., Alhamdulillah.

In my case, my Entity Service Connection was configured with Windows Authentication. I then proceeded to the web service's IIS 7 Application Pool Advanced Settings. I put up my existing user name and password and changed the identity from "ApplicationPoolIdentity" to "Network Service."

Even if I may subsequently encounter additional problems, I would rate myself a Well Done:-)!.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow