SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

After adding a ton of new features in this week’s build I was totally pumped up to get my bits onto the test server.  I published the web application hit my browser and BLAMO!:

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)

Lame!

As a background, I am using Visual Studio 2010 LCTP with SQL Server Express 2008.  The client is written in ASP.NET with the MVC Framework.

The Hunt Begins

I assumed that there must have been some kind of error in my connection string.  I chased that down and there was absolutely nothing different.  In fact, I don’t even publish my web.config on new builds when I’m deploying.

No problem with the configuration strings, the web.config is unmodified.  I logged into the SQL Server, and sure enough it was at the unexpected shutdown prompt.  I checked with our network admins and they (no so) graciously recycled the VMs last night.  So it was some kind of trouble with that, right?

I go in and had to manually start the SQL Browser service.  Still haven’t figured out why it keeps resetting to disabled.

That out of the way, I hit the web browser again and get the same error.

Bing me some Google…

Here’s what I already knew to be true:

• Firewall was not blocking and appropriate ports were open.
• SQL Server was configured to accept remote connections.
• The instance name was right in my web.config file.
• I could ping the SQL Server from the IIS Server and vice versa.

I was running out of things I knew how to do, so I resorted to Binging and Googling until I found something new to try.

Here are some pages I worked through to find settings and ensure things were configured as expected:

• http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx
• http://aquesthosting.com/sqlerror26
• http://fellowdeveloper.blogspot.com/2009/03/sql-network-interfaces-error-26-error.html
• http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/

…and of course the MSDN docs on prepping an SQL 2008 box for remote access.  None of these lists helped me out.

Finally a Solution

Over my lunch hour a light came on inside my head. 

Shoot!

Remember those new features I added?  Yeah, well one of them had a LINQ to SQL file in it that was using a hard-coded connection string.

I went back to the browser and looked at the error more closely.  This let me trace down the code where the error was happening. 

I would like to make clear that this wasn’t my first choice as the code was executing locally and previous builds worked fine in this environment.  “Not looking at the error” is a strategy I might have to revisit in subsequent debug sessions. Or not.

I found two instances where, rather than passing in the connection string from my web.config file, I was using the default constructor which was trying to access a database that didn’t exist on the test server.

Lesson learned: stick to best practices and use a connection string when initializing data contexts!

Hope this helps someone else.