I am trying to connect to an Azure SQL Server database from an Azure Cloud Services project through entity framework, but am getting an error of "The underlying provider failed on Open."
I repeated the same process of adding entity framework which I took to create my cloud service on a console based application and the console application was able to successfully connect to the Azure database. For some reason this is not the case with my Cloud Services project. After a few hours of trouble shooting, I think that the problem could lie with my connection string. Can somebody with experience on these technologies please help out? I would really appreciate it. I am new to Azure and so far I think it's great but this is my obstacle right now.
<add name="myProjectDBEntities" connectionString="metadata=res://*/myProject1myProjectModel.csdl|res://*/myProject1myProjectModel.ssdl|res://*/myProject1myProjectModel.msl;provider=System.Data.SqlClient;provider connection string="data source=f55bjjg4wt.database.windows.net;initial catalog=myProjectDB;user id={loginID};password={password};MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient"/>
You can set your connectionstring in Azure Cloud Service, and it will work.
Same as this in cloud service:
https://docs.google.com/file/d/0B8TtY9U9VCvqeXRMbkFWM000ZnM/edit
Also, you can find "show connectionstring" on your Azure SQL Server database Dashboard.
Copy and paste to your connectionString part. And remember to set your password.
Hope it will help you.