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.
This article describes how to complete each of these steps.
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.
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:
- 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, 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.
- 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.
Note: When you click the Automatic option, the SQL Server Browser
service starts automatically every time that you start Microsoft Windows.
- 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 2005 service and select
SQL Sercer Browser 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.
- Make sure TCP/IP is enabled.
To open Windows Firewall, click Start, click Run, 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:
- 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.
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.
- Repeat steps 1 through 3 for each instance of SQL Server 2005 that needs an exception.
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.
Note: This is a "FAST PUBLISH" article created directly from within the Microsoft
support organization. The information contained herein is provided as-is in response
to emerging issues. As a result of the speed in making it available, the materials
may include typographical errors and may be revised at any time without notice.
See Terms of Use
Ports Used By SQL Server
To work with a firewall, the instance of the Database Engine must listen on the
network address that the firewall is configured to forward.
A TCP/IP network address for SQL Server Database Engine consists of two parts:
- An IP address associated with one or more network cards in a computer, and a TCP
port address specific to an instance of SQL Server.
- Default instances of the Database Engine use TCP port 1433 by default.
- Named instances, however, dynamically assign an unused TCP port number the first
time the instance is started.
- The named instance can also dynamically change its TCP port address on a later startup
if the original TCP port number is being used by another application.
- SQL Server only dynamically changes to an unused TCP port if the port it is currently
listening on was itself dynamically selected; if a statically assigned port is in
use by another application, SQL Server displays an error and continues to listen
on other ports.
- It is unlikely, however, that another application would use 1433 since that port
is a well-known registered address for the SQL Server Database Engine.
Ports Used By the Database Engine
The following table lists the ports that are frequently used by the Database Engine.
|
Scenario
|
Port
|
Comments
|
|
SQL Server default instance running over TCP
|
TCP port 1433
|
This is the most common port allowed through the firewall. It applies to routine
connections to the default installation of the Database Engine, or a named instance
that is the only instance running on the computer. (Named instances have special
considerations0.
|
|
SQL Server named instances in the default configuration
|
The TCP port is a dynamic port determined at the time the Database Engine starts.
|
UDP port 1434 might be required for the SQL Server Browser Service when you are
using named instances.
|
|
SQL Server named instances when they are configured to use a fixed port
|
The port number configured by the administrator.
|
|
|
Dedicated Admin Connection
|
TCP port 1434 for the default instance. Other ports are used for named instances.
Check the error log for the port number.
|
By default, remote connections to the Dedicated Administrator Connection (DAC) are
not enabled. To enable remote DAC, use the Surface Area Configuration facet. For
more information, see
Understanding Surface Area Configuration.
|
|
SQL Server Browser service
|
UDP port 1434
|
The SQL Server Browser service listens for incoming connections to a named instance
and provides the client the TCP port number that corresponds to that named instance.
Normally the SQL Server Browser service is started whenever named instances of the
Database Engine are used. The SQL Server Browser service does not have to be started
if the client is configured to connect to the specific port of the named instance.
|
|
SQL Server instance running over an HTTP endpoint.
|
Can be specified when an HTTP endpoint is created. The default is TCP port 80 for
CLEAR_PORT traffic and 443 for SSL_PORT traffic.
|
Used for an HTTP connection through a URL.
|
|
SQL Server default instance running over an HTTPS endpoint.
|
TCP port 443
|
Used for an HTTPS connection through a URL. HTTPS is an HTTP connection that uses
secure sockets layer (SSL).
|
|
Service Broker
|
TCP port 4022. To verify the port used, execute the following query:
SELECT name, protocol_desc, port, state_desc
FROM sys.tcp_endpoints
WHERE type_desc = 'SERVICE_BROKER'
|
There is no default port for SQL Server Service Broker, but this is the conventional
configuration used in Books Online examples.
|
|
Database Mirroring
|
Administrator chosen port. To determine the port, execute the following query:
SELECT name, protocol_desc, port, state_desc FROM sys.tcp_endpoints
WHERE type_desc = 'DATABASE_MIRRORING'
|
There is no default port for Database mirroring however Books online examples use
TCP port 7022. It is very important to avoid interrupting an in-use mirroring endpoint,
especially in high-safety mode with automatic failover. Your firewall configuration
must avoid breaking quorum. For more information, see
Specifying a Server Network Address (Database Mirroring).
|
|
Replication
|
Replication connections to SQL Server use the typical regular Database Engine ports
(TCP port 1433 for the default instance, etc.)
Web synchronization and FTP/UNC access for replication snapshot require additional
ports to be opened on the firewall. To transfer initial data and schema from one
location to another, replication can use FTP (TCP port 21), or sync over HTTP (TCP
port 80) or File and Print Sharing (TCP port 137,138, or 139).
|
For sync over HTTP, replication uses the IIS endpoint (ports for which are configurable
but is port 80 by default), but the IIS process connects to the backend SQL Server
through the standard ports (1433 for the default instance.
During Web synchronization using FTP, the FTP transfer is between IIS and the SQL
Server publisher, not between subscriber and IIS.
For more information, see Configuring
Microsoft Internet Security and Acceleration Server for Microsoft SQL Server 2000
Replication over the Internet.
|
|
Transact-SQL debugger
|
TCP port 135
The IPsec exception might also be required.
|
If using Visual Studio, on the Visual Studio host computer, you must also add
Devenv.exe to the Exceptions list and open TCP port 135.
If using Management Studio, on the Management Studio host computer, you must also
add ssms.exe to the Exceptions list and open TCP port 135. For
more information, see
Configuring the Transact-SQL Debugger.
|