This article describes the TCP/IP port numbers that Microsoft SQL Server requires to communicate over a firewall. The default SQL Server port is 1433, and client ports are assigned a random value between 1024 and 5000.
For detailed information about the port numbers that SQL Server requires, see the "More information" section of this article.
SQL Server is a Winsock application that communicates over TCP/IP by using the sockets network library. SQL Server listens for incoming connections on a particular port. The default port for SQL Server is 1433. The port doesn't need to be 1433, but 1433 is the official Internet Assigned Number Authority (IANA) socket number for SQL Server.
A client application communicates to SQL Server using the client-side network library Dbmssocn.dll (or Dbnetlib.dll for SQL Server 2000) and any client using Microsoft Data Access Components (MDAC) 2.6.
When the client establishes a TCP/IP connection, a three-way handshake takes place. The client opens a source port and sends traffic to a destination port, which is 1433 by default. The client source port in use is random, but is greater than 1024. By default, when an application requests a socket from the system for an outbound call, a port number between the values of 1024 and 5000 is supplied. For more information, see the following Microsoft Web site:
The server (in this case, SQL Server) then communicates to the client by sending traffic from 1433 back to the port that the client established.
The best way to observe this behavior is to trace a client-to-server communication by using Microsoft Network Monitor or a network sniffer tool. To configure the firewall, you must allow traffic from *ANY* to 1433, and from 1433 to *ANY*, where *ANY* is a port greater than 1024.
In addition to using Microsoft Network Monitor, you can also use the TCP/IP Netstat utility to illustrate this behavior. Issuing the netstat -an
command from an MS-DOS command window produces the following results showing three established connections to SQL Server. This example uses 126.96.36.199 as the IP address of SQL Server and 188.8.131.52 as the client IP address. The ports opened by the client are 1746, 1748, and 1750 respectively.
Proto Local Address Foreign Address State
TCP 184.108.40.206:1433 0.0.0.0:0 LISTENING
TCP 220.127.116.11:1433 18.104.22.168:1746 ESTABLISHED
TCP 22.214.171.124:1433 126.96.36.199:1748 ESTABLISHED
TCP 188.8.131.52:1433 184.108.40.206:1750 ESTABLISHED
The firewall software should allow this dynamic allocation to occur through the use of rules. If it does, you can configure the connection from 1433 to *ANY* as "established". This will dynamically open the response port after a syn followed by a syn/ack by way of a statefull packet inspection.
There is no way to limit the number of source TCP ports used
for a SQL Server client to connect; this would defeat the purpose of having the
client allocate a new, unused dynamic port. This is a TCP/IP standard that is
defined for Winsock applications; this is not a limitation of SQL Server client
In addition, a named instance of SQL Server 2000 will
use a dynamic destination port by default. This port should be changed to a
fixed port prior to configuring the firewall. The SQL Server Network Utility
should be used to configure the destination port. See SQL Server Books Online
for information on how to use the SQL Server Network Utility.
Otherwise, the client computer would need to open a random UDP port and the
server UDP port 1434 will be used to send the instance name, and if the
instance is clustered, the version of the SQL instance, the TCP port number
that the instance is listening on, and the named pipe that the instance is
using. However, if the goal is to minimize the number of ports open on the
firewall, a static port number should be chosen for the default instance and
any named instance. The client computers would need to be configured to connect
to a particular ServerName or ServerName instance and specific port
For additional information, click the following
article numbers to view the articles in the Microsoft Knowledge Base:
HOW TO: Set Up SQL Server with Proxy Server
How to Capture Network Traffic with Network Monitor
The Basics of Reading TCP/IP Traces
HOWTO: Use ADO to Connect to SQL Server That Is Behind a Firewall