Cannot connect to SQL Server Express 2008 from MVC application

Question

My brand new Server 2008 R2 Server will not accept connections from my MVC3 application.

I have been trying to get this working for 3 days. It just will not connect via the browser. However I can log on via SQL Server Mgmt Studio with the user credentials, also as administrator.

I have done the following:

• Setup remote connections to be accepted (even though IIS and SqlExpress are running on the same box)
• Enabled named pipes/tcp/shared memory
• Disabled Dynamic Ports for TCP configuration (my making it blank)
• Specified port for TCP configuration
• Disabled windows firewall

I realize this is a generic error but it just won't connect.

Connection string:

add name="Main.ConnectionString" 
   connectionString="Data Source=.\sqlexpress;Initial Catalog=BJ;User Id=Jameson;Password=pass!;"

I get the following error (YSOD):

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)]

How can this be fixed?

Answer 1

A few troublehshooting things I'd check:

• is your code explicitly using the Main.ConnectionString? Double check this -- I've seen instances where a LINQ To SQL had a hardcoded connection string in the .dbml. I realize you didn't mention LINQ To SQL, but this could go for EF as well.

• search your code for any remnant of a hard-coded connection string.

• redeploy your project with a new build

• does your dev workstation connect OK with the same connection string?