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

Question

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

Do you have "Drop Database" as Initializer enabled and MS SQL Management Studio open at the same time? That would cause such an exception because you cant drop the database on that occasion, and EF fails out. — user604613
– user604613, Commented Nov 17, 2011 at 11:39
@UrbanEsc - I didn't really understand what you're saying, so I closed sql server studio and tried again, with no success. Thanks though. — Positonic
– Positonic, Commented Nov 17, 2011 at 12:26
The connection string doesn't have password for the sa user. — Ladislav Mrnka
– Ladislav Mrnka, Commented Nov 17, 2011 at 14:52

Svarog · Accepted Answer · 2011-11-17 13:07:07Z

5

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

answered Nov 17, 2011 at 13:07

Svarog

2,21816 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

teynon Over a year ago

Note that The underlying provider failed on Open may also be because of authorization issues. The inner exception will state if that is the case. One example where this may occur is when UAC is on and the data file is stored in the Program Files directory.

satyrFrost · Accepted Answer · 2012-08-14 04:50:05Z

1

Have you tried inserting the database password into the connection string? It worked for me.

Reference: http://stack247.wordpress.com/2011/03/02/entity-framework-exception-the-underlying-provider-failed-on-open/

answered Aug 14, 2012 at 4:50

satyrFrost

41310 silver badges17 bronze badges

Comments

Muhammedh · Accepted Answer · 2014-02-12 07:25:17Z

1

Well I had the same error for days... actualy for two days and Yes I found the solution Alhamdulillah..

As for me... I had Windows Authentication set on my Entity Service Connection.. So What I did was I went to IIS 7 Application Pool Advanced Settings for the web service... Changed the Identity from 'ApplicationPoolIdentity' to 'Network Service' and I setup my current user name and password as well.

I might face some other issues later on, still I would say a Good Start :-)!

answered Feb 12, 2014 at 7:25

Muhammedh

5041 gold badge11 silver badges28 bronze badges

Comments

Hasan Batuhan Kurt · Accepted Answer · 2019-05-17 14:04:24Z

0

I was remove "integrated security=True;" solved this way.

answered May 17, 2019 at 14:04

Hasan Batuhan Kurt

2813 silver badges8 bronze badges

2 Comments

Edgar H Over a year ago

I do not see integrated security=True; anywhere in the question. Could you elaborate and make your answer clear and easier to understand?

Hasan Batuhan Kurt Over a year ago

You should delete interated secutriy and add User ID and Password. This my connection string : <connectionStrings><add name="PettyDBEntities" connectionString="metadata=res://*/PettyModel.csdl|res://*/PettyModel.ssdl|res://*/PettyModel.msl;provider=System.Data.SqlClient;provider connection string="data source=WIN-COO1ETVI1E6;initial catalog=PettyDB; integrated security=True; User ID=admin;Password=password;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" /></connectionStrings></configuration>

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related