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 considerations. See Dynamic Ports later in this topic.) |
SQL Server named instances in the default configuration | The TCP port is a dynamic port determined at the time the Database Engine starts. | See the discussion below in the section Dynamic Ports. 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. | See the discussion below in the section Dynamic Ports. |
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 See Special Considerations for 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. |
For step by step instructions to configure the Windows Firewall for the Database Engine, see How to: Configure a Windows Firewall for Database Engine Access.
Dynamic Ports
By default, named instances (including SQL Server Express) use dynamic ports. That means that every time that the Database Engine starts, it identifies an available port and uses that port number. If the named instance is the only instance of the Database Engine installed, it will probably use TCP port 1433. If other instances of the Database Engine are installed, it will probably use a different TCP port. Because the port selected might change every time that the Database Engine is started, it is difficult to configure the firewall to enable access to the correct port number. Therefore, if a firewall is used, we recommend reconfiguring the Database Engine to use the same port number every time. This is called a fixed port or a static port. For more information, see Configuring a Fixed Port.
An alternative to configuring a named instance to listen on a fixed port is to create an exception in the firewall for a SQL Server program such as sqlservr.exe (for the Database Engine). This can be convenient, but the port number will not appear in the Local Port column of the Inbound Rules page when you are using the Windows Firewall with Advanced Security MMC snap-in. This can make it more difficult to audit which ports are open. Another consideration is that a service pack or cumulative update can change the path to the SQL Server executable which will invalidate the firewall rule.
No comments:
Post a Comment