If you’re using SQL Server 2005, you probably have encountered this error at least once. When people see this error, they may not know where to start searching. This post is a recap of every possible cause to this problem (that I know of).
The error message you get is :
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 connection. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
The possibles causes are:
Server accessibility and availability:
Depending on your connection type, make sure that TCP/IP or Named Pipes are enabled in the Surface Area Configuration tool.
Also make sure that TCP/IP or Named Pipes are enabled in the SQL Server Configuration Manager.
Make sure that you can ping the server from the client.
Make sure that your SQL Server is started (obvious but still needs to be on the list).
You should also double check your connection string to be sure that there is not a typo in the server name or instance name.
You also should try both Server\Instance and Server\\Instance in your connection string. This is because some applications convert \\ to \.
You may also try to add the SQL port to the connection string (ex: Data Source=mysqlserver\myinstance,1433).
If you are using SQL Server Express:
If your SQL Server is behind a Firewall:
Check that there is an exception in your firewall configuration to let the traffic through port 1433 (for SQL Server) and 1434 (for an UDP packet sent to get information on the SQL Instance like the protocol, the TCP port to use, etc).
If your SQL Instance is on a cluster:
UDP packets are dropped in some cases when dealing with an instance on a cluster. This is a known issue and I don’t know if Microsoft will do something about it. For more details and workarounds, you can go here
if you’re trying to connect to your SQL Server with an ASP.NET application:
Make sure that the version of the .NET Framework loaded in IIS on the server for this specific Web Site is the right one (I experienced this error when I deployed an application built with the version 2.0 Framework on a web server with 1.1 loaded by default).
The process account of the ASP.NET application must have rights to connect to the SQL Server database.
This list may not be complete. So if you know another cause or element that needs to be checked for this particular scenario, drop me a line at email@example.com and I’ll update this post.