Configure SQL Server 2005 to allow remote connections _mssql

For example, when you use the SQLCMD utility to connect to SQL Server, you receive the following error message:

Copy Code code as follows:

Sqlcmd: Error: Microsoft SQL Native Client: An error occurred while establishing a connection to the server. When connecting to SQL Server 2005, default settings SQL Server does not allow remote connections This fact may cause failure.

This problem may occur if SQL Server 2005 is not configured to accept remote connections. By default, remote connections are not allowed by SQL Server Edition Express and SQL Server Developer Edition. To configure SQL Server 2005 to allow remote connections, complete the following steps:
Enable remote connections on the instance of SQL Server to which you want to connect from a remote computer.
Open the SQL Server Browser service.
Configure the firewall to allow network traffic associated with SQL Server and SQL Server Browser services.
This article describes how to complete each step of these steps.
To enable remote connections on a SQL Server 2005 instance and open the SQL Server Browser service, use the SQL Server 2005 perimeter Configuration tool. The Perimeter Application Configurator tool is installed when SQL Server 2005 is installed.

Enable remote connections for SQL Server Edition Express or SQL Server 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, Microsoft SQL Server 2005, and configure tools, and then click SQL Server Perimeter Application configurator.
2. On the SQL Server 2005 Perimeter Configuration page, click the perimeter application configurator for services and connections.
3. On the perimeter application configurator for services and Connections page, expand Database engine, click Remote Connections and then connect to local and remote connections, click the appropriate protocol for your environment, and then click Apply.
   
   Note : Please click OK when you receive the following message:
   Changes to connection settings do not take effect until the Database engine service is restarted.
4. On the perimeter application configurator for services and Connections page, expand Database engine, click Services and then stop, wait for the MSSQLServer service to stop, and then click Start to restart the MSSQLServer service.

Enable SQL Server Browser Service

If you are running SQL Server 2005 by using an instance name and you do not use a specific TCP/IP port number in the connection string, you must enable the SQL Server Browser service to allow remote connections. For example, SQL Server Express is installed using the default instance name of the < computer name >\sqlexpress. No matter how many instances of SQL Server 2005 you are running, you only need to enable the SQL Server Browser service once. To enable the SQL Server Browser service, follow these steps.

Important Notes: These steps may increase your security risk. These steps may also cause your computer or network to be more vulnerable to malicious users or malicious software such as viruses. We recommend this process to enable programs to run as designed, or to implement specific program functions. We recommend that you take full account of the risks associated with implementing this process in your particular environment before making these changes. If you choose to implement this procedure, take any appropriate additional steps to protect your system. We recommend that you use this process only if you really need it.

1. Click Start, point to Programs, Microsoft SQL Server 2005, and configure tools, and then click SQL Server Perimeter Application configurator.
2. On the SQL Server 2005 Perimeter Configuration page, click the perimeter application configurator for services and connections.
3. On the perimeter application configurator for services and Connections page, click SQL Server Browser, click the Automatic option in Startup type, and then click Apply.
   
   Note : When you click the Auto option, the SQL Server Browser service is started automatically each time you start Microsoft Windows.
4. Click Start, and then click OK.

Attention: When you run the SQL Server Browser service on your computer, the computer displays the instance name and connection information for each instance of SQL Server that is running on it. This risk can be reduced if the SQL Server Browser service is not enabled and is directly connected to an instance of SQL Server through an assigned TCP port. This article does not discuss how to go directly to an instance of SQL Server through a TCP port. For more information about SQL Server Browser services and connecting to instances of SQL Server, see the following topics in SQL Server Books Online:

• SQL Server Browser Service
• Connecting to the SQL Server database engine
• Client Network Configuration

Creating Exceptions in Windows Firewall

These steps apply to the Windows XP Service Pack 2 (SP2) and Windows Server 2003 version of the firewall that is included. If you are using a different firewall system, please refer to the appropriate firewall documentation for more information.

Running a firewall on a computer running SQL Server 2005 prevents external connections to SQL Server 2005 from being accessed unless SQL Server 2005 and SQL Server Browser services can communicate through firewalls. You must create an exception for each instance of SQL Server 2005 that you want to accept remote connections, and create an exception for the SQL Server Browser service.

When you install a program file for SQL Server 2005, SQL Server 2005 uses an instance ID as part of the path. To create an exception for each instance of SQL Server, you must determine the correct instance ID. To get the instance ID, follow these steps:

1. Click Start, point to Programs, turn to Microsoft SQL Server 2005, and configure tools, and then click SQL Server Configuration Manager.
2. In SQL Server Configuration Manager, click SQL Server Browser Service in the right pane, right-click the instance name in the main window, and then click Properties.
3. On the SQL Server Browser Properties page, click the Advanced tab, navigate to the instance ID in the property list, and then click OK.

To open Windows Firewall, click Start, click Run, and then type Firewall.cpl, and then click OK.

Create an exception for SQL Server 2005 in Windows Firewall

To create an exception for SQL Server 2005 in Windows Firewall, follow these steps:

1. In Windows firewall, click the Exceptions tab, and then click Add Program.
2. In the Add Programs window, click Browse.
3. Click C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe 可执行程序，单击“打开”，然后单击“确定”。
   
   注意：上述路径可能会根据 SQL Server 2005 的安装位置而不同。MSSQL.1 这个占位符代表的是您在前面过程的步骤 3 中获得的实例 ID。
4. Repeat steps 1 through 3 for each instance of SQL Server 2005 that you want to create an exception for.

Create an exception for the SQL Server Browser service in Windows firewall

To create an exception for the SQL Server Browser service in Windows Firewall, follow these steps:

1. In Windows firewall, click the Exceptions tab, and then click Add Program.
2. In the Add Programs window, click Browse.
3. Click C:\Program Files\Microsoft SQL Server\90\shared\sqlbrowser.exe executable program, click Open, and then click OK.
   
   Note : The above path may vary depending on the installation location of SQL Server 2005.