Search code examples
c#sql-servervisual-studiowcfentity-framework

WCF & Entity Framework & SQL Server - "The underlying provider failed on Open"

I am successfully running tests through the the WCF Test Client, until I try to pull in data with Entity Framework.

To make sure I'm not doing anything stupid, I downloaded the sample code from this tutorial, which is doing something similar: http://www.codeproject.com/KB/WCF/WCFandEF.aspx

...and when I run it, I get the same error in a similar place:

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

The error is

The underlying provider failed on Open.

I can open the database fine from a "normal application" with the same connection string, it seems to be specific accessing the DB from the WCF test client.

Research here and on Google for "The underlying provider failed on Open." usual indicates that it's a connection string problem, but I'm pretty sure it's not in this case.

So now I expect it's some sort of permissions problem.

I am using SQL Server and Windows 7, with visual studio 2010.

I have been banging my head since yesterday, so any help or protective head gear appreciated.

Edited to include connection string

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

  • This error means 100% a problem in your connection string.

    A good way to create a surely working connection string is to create a new(dummy) project, add an Entity Framework Data Model to it, select "Generate Model from Database", select your required connection, and click "Test Conntection" to be sure it works.
    Make sure ""Save entity conection settings in App.Config as:..." is ticked.
    Than, in you web/app.Config, you have a Connection String you can copy+paste to your own project