Problems connecting to a live DB in an MVC 4 web app using EF5.0

I had some real problems this week deploying my latest site live. Everything was runnig fine locally, or so i thought. connecting to a local sql2012 DB through entity framework 5.0

I was running on dev through iis express on port :41171

When i deployed to live the web server would spin and spin and then eventually produce an asp error saying [Win32Exception (0x80004005): The system cannot find the file specified]

[SqlException (0x80131904): 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)]
...
My first instincts were that SQL server was not set up right at the hosting providers end, I was using the correct connection string after all. But after some back and forth from the hosting provider (1and1) I came to realisation that the SQL instance (SQL server 2012) was probably set up right.
I finally removed this section from the web.config that entity framework adds in


<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
...
...
<entityframework>
<defaultconnectionfactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">
<parameters>
<parameter value="Data Source=.; Integrated Security=True; MultipleActiveResultSets=True" />
</parameters>
</defaultConnectionFactory>
</entityFramework>

What this magic does? [sic]
From what i gather this is telling entity framework to auto-magically connect to a database by convention if the connection string doesn't work. I managed to ascertain that it was my code was actually trying to connect to a local sqlexpress DB. So i took it out.

After removing that section I got this error message (again after ages of trying to connect)

Server Error in '/' Application.
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)

[SqlException (0x80131904): 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)]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5296071
...
...
AND now dev was also broken...

At this point you may be directed here by google/bing/duckduck et al. to https://blogs.msdn.com/b/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx
its not the problem you are looking for... No you dont need to allow UDP port 1434 or anything like that.

My local connection string was
<add name="applicationName" connectionString="server=localhost; database=localdbname;Integrated Security = true;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />
my connection string on live was
<add name="applicationName" connectionString="Server=db123456789.db.1and1.com,1433;Database=123456789;User Id=mydbuser;Password=mydbuserpassword;" providerName="System.Data.SqlClient" />
all as provided by the host.

Why was it not connecting?? i almost put that config section back but then after debugging i found that inside the the application context that was driving entity framework the connection string was not there, again it was trying to use sqlexpress??

then i stumbled upon it, my context was configured thus.


public class theappContext : DbContext
{
public theappContext() : base("databasename")
{
}
...

and in the application_start in global.asax
Database.SetInitializer<theappcontext>(null);
It turns out you need the connection strings name to be the same as the string in base("databasename"). In my instance i needed to set it to databsename not applicationName as i had previously.

So finally these are my connection strings

On dev either:
<add name="databasename" connectionString="server=localhost; database=localdbname;Integrated Security = true;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />
<add name="databasename" connectionString="server=localhost; database=localdbname;User Id=mydbuser;Password=mydbuserpassword" providerName="System.Data.SqlClient" />
On live:
<add name="databasename" connectionString="Server=db123456789.db.1and1.com,1433;Database=123456789;User Id=mydbuser;Password=mydbuserpassword;" providerName="System.Data.SqlClient" />

And thats what fixed it, simply changing the name of the connection string. So entity framework was being super cleaver and making up for bad connection strings locally, which meant that when deploying live I had no hope. Why all this auto-magic?