Cannot connect to SQL Server Express 2008 from MVC application

3
votes

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?

2
sql-serverasp.net-mvc-3sql-server-express
What is the name of your instance SQLEXPRESS? and what does your connection string look like? (secretive bits changed of course) – Tahbaza

2 Answers

2
votes

If you are using SQL authentication your connection string should look like this:

Data Source=myServerAddressIP\SQLEXPRESS;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

and if you are using Windows Authentication:

Data Source=myServerAddressIP\SQLEXPRESS;Initial Catalog=myDataBase;Integrated Security=SSPI;

Also make sure a firewall is not blocking connections.

For more info checkout the following site. And if you still have troubles configuring your SQL Server don't hesitate to ask your question on https://serverfault.com/

2
votes

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?