An error has occurred…

I recently got bitten by a rather annoying and hard to track down error. I was in the process of creating a fairly simple ASP.NET 2.0 application which accesses a SQL Server 2005 database. It worked at home but not in the office…

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)

After doing a few Google searches, it turns out this can be caused by lots of things, and there are quite a few similar error messages for many different reasons. The most often cited one is due to SQL Server 2005 not accepting remote connections by default (which personally I think is ridiculous, it’s a server right, the whole reason for having servers is to allow access by remote clients! But I digress..)

So I fired up the Surface Area Tool 2005 and checked that remote connections were allowed. No problems there.

Another oft cited reason for this error is that a Specific Protocol is not enabled. So I opened up SQL Server Configuration Manager and checked the Client Protocols. All fine.

Of course! It’s probably the firewall. Checked it and in desperation temporarily turned it off. Still no good.

Checked the connection string worked in isolation. Once again nothing wrong there.

After a great deal of hair-pulling and searching I finally found the problem. The ASP.NET role provider was trying to access the role table via a connection string named “LocalSQLServer” defined in the machine.config, and this was pointing to “.\SQLEXPRESS” which was not installed!

The simple fix is to override in your web.config: