An error occurred while establishing a connection to SQL Server related to a network or a specific instance

Question

I have a simple mvc website (using VS internet template) on azurewebsites that talks to an Azure SQL database in the same data center. The database at this time is just the built-in SimpleMembership proxy provider. I have already switched from the default MSD file App_Data to Azure SQL. It works fine, but sometimes after a while it gives:

An error occurred with the network or a specific instance while establishing a connection to SQL Server. The server was not found or was unavailable. Verify that the instance name is correct and SQL Server is configured for a remote connection. (provider: SQL Network Interfaces, error: 26 - Server / instance localization error Indicated)

with a long message:

Error creating SQLExpress database file automatically:

The connection string indicates the local instance of Sql Server Express using the database location in the App_Data application directory. The provider tried to automatically create the service database application because the provider determined that the database does not exist. The following configuration requirements are required to successfully check for database applications and automatically create the application services database:

But I don't have a connection string using SQL Server Express!

Restarting the site does not immediately remove the error.

Without changing anything and restarting the website after 15 minutes, you will receive:

502 - The web server received an invalid response, 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 (acting as a gateway or proxy) contacted the upstream content server, it received an incorrect response from the content server.

I used to be able to restore a website back to a work order by simply republishing it from VS. But in the last hour, I tried and tried, and I cannot get the website to work again.

What is the problem with SQL Server Express?

The 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> 

To be sure, I copied and pasted sabl6h4---.database.windows.net into SSMS and connected using the login test, and it opened the database without any problems.

---

Every other day . I created a new azurewebsite and published the same project and the same web.config. It works great without any problems. A few minutes later, the same error occurred!

---

Three hours later . I went for lunch, returned, clicked the refresh button of the browser, and the website went up again. I am convinced that someone is cheating with the settings at the back end.

Answer 1

Add the <clear/> element as the first element below the connectionstrings element to ensure that the rows are not inherited. Also, I assume that your Azure SQL DB is configured with the correct firewall settings allowing your IP address, where is the ASP.net code deployed?

Answer 2

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