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.

Connecting to an instance of Microsoft SQL Server 2005 database be it the Express, Developer or the Enterprise editions for accessing and updating data or simply for authentication may cause an error while connecting to the server. This is irrespective of whether the connection is from an SQL Server local computer or any other remote computer. It can be caused by SQLCMD utility or any such program used for connecting to SQL Server. This may be caused by the default settings of the SQL server which does not enable remote connections or has not been configured to accept remote and local connections. To turn on the local and remote connections settings, the SQL server needs to be configured accordingly. Here is the step by step process of allowing the incoming connections on the instance of database server.

Step 1: Click on the Start button and proceed to Programs. Select Microsoft SQL Server 2005 and then click on Configuration Tools. You now need to run the SQL Server Surface Area Configuration. On this page select the Surface Area Configuration for Services and Connections to open the relevant page. Expand the Database Engine tree and select Remote Connections. If there is no remote connection or you are trying to connect and authenticate after the installation, select the local connection. Now choose the appropriate protocol based on the remote or local environments. Use both TCP/IP and named pipes to ensure maximum compatibility. Now apply the settings and click OK once you receive the message stating “Changes to Connection Settings will not take effect until you restart the Database Engine service.” Now expand the Database Engine on the Surface Area Configuration for Services and Connections” page and select “Service. Click the Stop button to halt your service and wait until the MSSQLSERVER service stops, before you restart the MSSQLSERVER service.

Step 2: The next step involves the enabling of the SQL Server Browser service to enable remote as well as local connections. This is when the SQL Server 2005 is being run on an instance name and there is no particular TCP/IP port number used in the connection string. For this you need to go to Programs or All Programs, select Microsoft SQL Server 2005, followed by Configuration Tools. Run the SQL Server Surface Area Configuration by selecting the option. You are now redirected to the “SQL Server 2005 Surface Area Configuration” page wherein you need to click Surface Area Configuration for Services and Connections. On this page, you now need to select the SQL Server Browser. Change the startup type to Automatic to ensure that the browser service starts each time you switch on the system. Click on the Apply button and then press Start to enable the service. Press OK

Step 3: The final step involves the creation of an exception in Windows firewall included in Windows XP SP2 (Service Pack 2), Windows Server 2003 and Windows Vista. This enables the remote computer to access the SQL server. If a third party firewall is used then exception needs to be created to allow remote connections to SQL Server and the Browser Service. For this click on Start button and select Run. Type firewall.cpl or firewall.cpl in Start Search box (uin case of Windows Vista). Now select “Allow a program” through Windows Firewall link on left tasks pane. In “Windows Firewall”, select the Exceptions tab, and then click Add Program followed by the browse button. Select the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe. This may vary based on the location of the SQL Server 2005 installation. Click Ok and repeat the steps for each instance of the SQL Server. For SQL Server Browser service, use the C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe executable program,

In addition to this SQL Server 2005 uses an instance ID as part of the path when you install its program files. To obtain the instance ID Click the Start button, then go to Programs and select Microsoft SQL Server 2005. Click on Configuration Tools and then run the SQL Server Configuration Manager. On the right pane, select the SQL Server Browser service and right click on the instance name. Select the properties option to display the “SQL Server Browser Properties” page. Click on the Advanced tab to locate the instance ID in the property list.