Hello,

I have a WCF service that has reference to business logic (bll.dll) that use entity framework to interact database(sql azure)

Whenever i call any restful service it gives 

The server encountered an error processing the request. The exception message is 'The underlying provider failed on Open.'. See server logs for more details. The exception stack trace is at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation...

Same bll used by an asp.net application it just fine. 

Connection strings 

  <add name="SqlConnection" connectionString="Data Source=xxxx.database.windows.net;Initial Catalog=xxxCloudDB;Integrated Security=False;User ID=xxx;Password=xxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False" />

 

    <add name="xxxxEntities" connectionString="metadata=res://*/Data.DataModel.csdl|res://*/Data.DataModel.ssdl|res://*/Data.DataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=xxxx.database.windows.net;Initial Catalog=xxxxCloudDB;Integrated Security=False;User ID=xxxx;Password=xxxx;Encrypt=True;TrustServerCertificate=False&quot;" providerName="System.Data.EntityClient"/>

 

Any idea?

Thanks for your help!

 

"The underlying provider failed to open" normally means wrong (or even missing) connection string. By saying what you have a WCF service, referencing BLL and "ASP.NET Application" referencing BLL, where/how do you host the WCF Service? Are you sure the WCF Service gets the given connection strings?

What you could do to debug your code is to set "Debug"=>"Excetptions (CTRL + D,E)" and check the "Thrown" checkbox for "Common Language Runtime Exceptions". Extremely useful option (Thrown) for debugging application code. Once you set that, your visual stuio will break at the point the connection is being made (or the code that is requiring that connection). There you will be able to see the actual connection string that is being used. This will help you hunt the problem down.

• Marked as answer by MingXu-MSFTMicrosoft employee, Moderator Sunday, December 04, 2011 2:54 PM
• Unmarked as answer by Jae0072 Monday, December 05, 2011 8:09 AM

Thanks for your reply. WCF service hosted on IIS (that is working on Windows 2008 Server Web Edition). When i point the connection string to local database on  windows server just fine , when i set 

  <add name="SqlConnection" connectionString="Data Source=xxxx.database.windows.net;Initial Catalog=xxxCloudDB;Integrated Security=False;User ID=xxx;Password=xxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False" />

it throws an error. This connection also used by another application in project (asp.net application) that works without problem

 

I will try to test wcf service on local but scenario might change because it is currently hosted and running on IIS

You should add a firewall rule to your sql azure database to make this connection successful. Please try to find out the IP through which this Service is getting connected to SQL Azure DB and add that IP to Firewall rules of Your DB in Azure Management portal. It should work fine after these settings.

If you are facing any issue in figuring out the IP of your requesting server, Try to install any tool that connects to SQL Azure like Migration wizard and try to connect to your DB from your Service Requesting Server. If there is any problem with firewall rule setting it will show you the exact Ip that you need to add in the firewall rules of management portal.

I am prettymuch sure that this is because of connection issue with your SQL Azure DB.

 

Please mark as answer if its useful

 

please note that asp.net application access same sql azure database from same windows webserver that host wcf and asp.net applications.

• Edited by Jae0072 Monday, December 05, 2011 8:10 AM

Hi,

If you’re using IIS hosting, check if the service application’s application pool identity is the same as the ASP.NET application’s application pool identity. If they’re different, this could be caused by application pool identity unable to access your proxy server.

 

Best Regards,

Ming Xu.

Hi,

I will mark the reply as an answer. If you find it no help, please feel free to unmark it and follow up.

Thanks.

 

Best Regards,

Ming Xu.

Hi,

I figured out the problem that was entity connection string parameter that is automatically generated by framework. That is MultipleActiveResultSets . i got rid of that and that solved the problem

 

Thanks for help guys!

Jae

 

 

 

• Marked as answer by Jae0072 Monday, December 05, 2011 8:08 AM
• Edited by Jae0072 Monday, December 05, 2011 8:12 AM

As of June 2011 MARS is supported by SQL Azure. So your issue must have been something else!

Seems like you are right but i could not see anything except EF dbml conn string. I built many test restful services in wcf that has reference bll that interacts sql azure works like a rock now! Maybe firewall on sql azure could be a reason as well but i dont know which one

Follow below points to resolve your issue:

1) Confirm your web.config/app.config
2) Open your Distributed Transaction Coordinator
3) Allow checked Inbound & Allow Outbound options

Here is a great link The underlying provider failed on open who will guide step by step to resolve your issue.