1
votes

I have an MVC 3 app using Entity Framework that was happily hosted in Azure and talking to SQL Azure Database 2 days ago. I then had a minor schema change to the database and so dropped and re-created the database via SSMS and also published the application again as I had added the default MVC app login role controls to the site - since then I cannot get my app to connect to the database and get the following error logged in an Elmah xml file:

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)" source=".Net SqlClient Data Provider" detail="System.Data.EntityException

I can connect to the SQL Azure Database using SSMS and when I run my application in the Azure emulator connected to the SQL Azure Database everything runs perfectly but once published it refuses to connect. Any help would be massively appreciated!

2
I've realised that the problem is that when I publish my application it doesn't seem to be picking up the settings from the web.release.config file but it's using the ones in web.config. I don't understand why this is as I'm building the solution in 'Release' configuration - any ideas?JayneT

2 Answers

1
votes

A SQL Azure connection string (using EF Model First / Database First) typically lookes like this:

<connectionStrings>    
    <add name="SampleEntities" connectionString="metadata=res://*/Models.SampleEntities.csdl|res://*/Models.SampleEntities.ssdl|res://*/Models.SampleEntities.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=tcp:abcdefg.database.windows.net,1433;Database=asdf;User ID=manager@abcdefg;Password=XXXXXX;Trusted_Connection=False;MultipleActiveResultSets=True;Encrypt=True;&quot;" providerName="System.Data.EntityClient" />
</connectionStrings>

A SQL Azure Connection string (using Code First) typically looks like this:

<connectionStrings> 
   <add name="SampleContext" connectionString="Data Source=tcp:abcdefg.database.windows.net,1433;Database=asdf;User ID=manager@abcdefg;Password=XXXXXX;Trusted_Connection=False;MultipleActiveResultSets=True;Encrypt=True" providerName="System.Data.SqlClient" />   
</connectionStrings>

The second connection string will also work without Entity Framework.

0
votes

I believe you've published a config to Azure that does not have a correct SQL Azure server name, but rather some local/on-premise SQL server. Could this be it? Can you remote into the instance and validate correct .config and SQL Azure server?

From the error it appears that your app is not using TCP/IP to connect to your SQL Azure, but rather Named Pipes, which usually implies that the SQL Azure server name is not likely a valid TCP/IP host.