A network-releated or instance-specific error occurred while establishing a connection to SQL Server

Question

I have a simple mvc web site (using the VS internet template) on azurewebsites talking to a SQL Azure database in the same data center. The database at this time is just to do the built-in SimpleMembership Provider. I have already switched from the default App_Data mdf file to Azure SQL. It works fine, but sometimes after a while, it would give:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

with a long message:

SQLExpress database file auto-creation error:

The connection string specifies a local Sql Server Express instance using a database location within the application's App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:

But I do not have a connection string using SQL Server Express!

Restarting the web site immediately does not remove the error.

Without changing anything and restarting the web site 15 minutes later give:

502 - Web server received an invalid response while acting as a gateway or proxy server.

There is a problem with the page you are looking for, and it cannot be displayed. When the Web server (while acting as a gateway or proxy) contacted the upstream content server, it received an invalid response from the content server.

Previously, I could restore the website back to working order by just re-publshing from VS. But for the last hour I have tried and tried and I cannot get the web site to work again.

What is the problem about SQL Server Express really about?

My connection string section contains:

<connectionStrings>
  <add name="DefaultConnection" connectionString="Server=tcp:sabl6h4---.database.windows.net,1433;Database=MVC;User ID=test@sabl6h4---;Password=----;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;" providerName="System.Data.SqlClient" />
  <add name="Entities" connectionString="metadata=res://*/Data.Model1.csdl|res://*/Data.Model1.ssdl|res://*/Data.Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=sabl6h4---.database.windows.net;initial catalog=MVC;user id=test;password=----;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
 </connectionStrings>

Just to be sure, I copied and pasted sabl6h4---.database.windows.net into SSMS and connected using login test and it opened the database without problem.

---

One day later: I created a new azurewebsite and published the exact same project and same web.config. It works fine without problems. After some minutes, the exact same error occurred!

---

Three more hours later: Went for lunch, came back, hit the browser Refresh button, and the web site is up again. I am convinced that someone is fooling around with the settings at the back end.

Answer 1

Add a <clear/> element as the first element under the connectionstrings element to ensure no strings are being inherited. Also, I assume your Azure SQL DB is set up with proper firewall settings allowing your IP where the ASP.net code is deployed?

Answer 2

I also had this error. But in my case I did not notice that the connection string was being overwritten by the publish wizard. I fixed the problem by going to the Settings tab in the Publish wizard and unchecking "Use this connection string at runtime (update destination web.config)"