To configure SQL Server 2005 to allow remote connections, complete all the following
steps:
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.
|
|
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:
|
- Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration
Tools, and then click SQL Server Surface Area Configuration.
- 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.
- 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
|
|
If you are running SQL Server 2005 by using an instance name and you are not using
a specific TCP/IP port number in your connection string, you must enable the SQL
Server Browser service to allow for remote connections. For example, SQL Server
2005 Express is installed with a default instance name of Computer Name\SQLEXPRESS.
You are only required to enable the SQL Server Browser service one time, regardless
of how many instances of SQL Server 2005 you are running. To enable the SQL Server
Browser service, follow these steps.
|
|
Important These steps may increase your security risk. These steps
may also make your computer or your network more vulnerable to attack by malicious
users or by malicious software such as viruses. We recommend the process that this
article describes to enable programs to operate as they are designed to, or to implement
specific program capabilities. Before you make these changes, we recommend that
you evaluate the risks that are associated with implementing this process in your
particular environment. If you choose to implement this process, take any appropriate
additional steps to help protect your system. We recommend that you use this process
only if you really require this process.
|
- Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration
Tools, and then click SQL Server Surface Area Configuration.
- On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration
for Services and Connections.
- On the Surface Area Configuration for Services and Connections page, click SQL Server
Browser, click Automatic for Startup type, and then click Apply.
- Click Start, and then click OK.
|
|
Note When you run the SQL Server Browser service on a computer,
the computer displays the instance names and the connection information for each
instance of SQL Server that is running on the computer. This risk can be reduced
by not enabling the SQL Server Browser service and by connecting to the instance
of SQL Server directly through an assigned TCP port. Connecting directly to an instance
of SQL Server through a TCP port is beyond the scope of this article. For more information
about the SQL Server Browser server and connecting to an instance 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
|
Create exceptions in Windows Firewall
|
|
These steps apply to the version of Windows Firewall that is included in Windows
XP Service Pack 2 (SP2) and in Windows Server 2003. If you are using a different
firewall system, see your firewall documentation for more information.
|
|
If you are running a firewall on the computer that is running SQL Server 2005, external
connections to SQL Server 2005 will be blocked unless SQL Server 2005 and the SQL
Server Browser service can communicate through the firewall. You must create an
exception for each instance of SQL Server 2005 that you want to accept remote connections
and an exception for the SQL Server Browser service.
|
|
SQL Server 2005 uses an instance ID as part of the path when you install its program
files. To create an exception for each instance of SQL Server, you must identify
the correct instance ID. To obtain an instance ID, follow these steps:
|
- Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration
Tools, and then click SQL Server Configuration Manager.
- In SQL Server Configuration Manager, click the SQL Server Browser service in the
right pane, right-click the instance name in the main window, and then click Properties.
- On the SQL Server Browser Properties page, click the Advanced tab, locate the instance
ID in the property list, 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:
|
- In Windows Firewall, click the Exceptions tab, and then click Add Program.
- In the Add a Program window, click Browse.
- Click the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe
executable program, click Open, and then click OK.
- Repeat steps 1 through 3 for each instance of SQL Server 2005 that needs an exception.
|
|
Note The path may be different depending on where SQL Server 2005
is installed. MSSQL.1 is a placeholder for the instance ID that you obtained in
step 3 of the previous procedure.
|
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:
|
- In Windows Firewall, click the Exceptions tab, and then click Add Program.
- In the Add a Program window, click Browse.
- Click the C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe executable
program, click Open, and then click OK.
|
|
Note The path may be different depending on where SQL Server 2005
is installed.
|
