This error kept me busy all morning and part of the afternoon:

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)

This error seems to be affecting more developers - according to some forums and blogs - but none of them had a solution.

After trying some workarounds I finally found one that worked:

The SQL port - 1433 -  needs to be included as part of Data Source on the connection string:

...Data Source=mysqlserver\instance1,1433;...

That did it for me.

Note that the error was happening when connecting to a SQL Server 2000 from a .NET 2.0 web application.

I'm not sure the real cause of this problem or why it was happening in the first place.

I also posted my workaround on the following forums:
http://blogs.msdn.com/sql_protocols/archive/2007/03/31/named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server.aspx?CommentPosted=true#commentmessage
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2475976&SiteID=1&mode=1