How to Configure SQL Server 2005 to Allow Remote Connections

About the Author
Shafqat Tanzeel has been teaching IT related courses for the last ten years.
Presently he is working as a lecturer in Department of Computer Science at the Sir Syed University of Engineering and Technology, Karachi, in Pakistan.
His main field of interest is distributed databases.

When you try to connect to an instance of Microsoft SQL Server 2005 from a remote computer, you may receive an error message. This problem may occur when you use any program to connect to SQL Server. For example, you receive the following error message when you use the SQLCMD utility to connect to SQL Server:

Sqlcmd: Error: Microsoft SQL Native Client: 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.

This problem may occur when SQL Server 2005 is not configured to accept remote connections. By default, SQL Server 2005 Express Edition and SQL Server 2005 Developer Edition do not allow remote connections. To configure SQL Server 2005 to allow remote connections, complete all the following steps:

• Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.
• Turn on the SQL Server Browser service.
• Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.

To enable remote connections on the instance of SQL Server 2005 and to turn on the SQL Server Browser service, use the SQL Server 2005 Surface Area Configuration tool. The Surface Area Configuration tool is installed when you install SQL Server 2005.

SQL Server 2005 Surface Area Configuration tool
Enable remote connections for SQL Server 2005 Express or SQL Server 2005 Developer Edition
You must enable remote connections for each instance of SQL Server 2005 that you want to connect to from a remote computer. To do this, follow these steps:

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.


3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.


Note Click OK when you receive the following message: Changes to Connection Settings will not take effect until you restart the Database Engine service.

4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

Enable the SQL Server Browser Service Provides SQL Server connection information to client computers. To enable the SQL Server Browser service, follow these steps:

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, click SQL Server Browser, click Automatic for Startup type, and then click Apply.


Note When you click the Automatic option, the SQL Server Browser service starts automatically every time that you start Microsoft Windows.


4. Click Start, and then click OK.

Windows Firewall – Turn off Firewall
To open Windows Firewall, click Start, click Run, type firewall.cpl, and then click OK.

Now let us execute some queries on the remote instance, specifically on the database bank. You can issue distributed queries referring to linked server name by using the following syntax. [linked_server_name].[catalog].[schema].[object_name]
select * from [ISI_BAAP_OF_ALL\SHAFQAT].bank.dbo.account