Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Ports that DBAs Need to Know

By Yusuf Anis,

This list is about essential TCP/UDP port numbers that an administrator running SQL Server / Cluster requires to know when configuring the firewall or otherwise. It includes those used under OS, server-based programs & their subcomponents. Certainly do let me know for any corrections that I may have missed out.

Service

Type

Port#

Comments

Default Instance

TCP

1433

Official socket allocated by IANA to Ms for SQL Server, this can be changed to anything above 1024.

Named Instance

TCP

XXXXX

As & what you assign in the dynamic port range. There have been changes since Win 6, see below for the available range.

DAC Default Instance

TCP

1434

Remote connections through DAC are disabled unless turned on manually. For named instance ports other than 1434 are used.

SQL Browser / SQL Server Resolution Protocol

UDP

1434

Used by an application level protocol SSRP on top of which the browser service runs.

It helps when connecting to non-hidden instances named instances. In such cases TCP port is dynamic (unless specified) & determined when the Database Engine starts. It’s not needed if all connections contain the port#. When uninstalling SQL 9.0 from a machine running SQL 8.0 check the existence of registry key IsListenerActive, because if it exists SSRP will fail to listen.

Denali Browser does not support sending information about SQL 8.0 instances.

Refer à http://msdn.microsoft.com/en-us/library/cc219750(v=PROT.10).aspx

DTS / SSIS

3882

Be cautious a malformed request to port 3882/tcp can cause DOS.

When communicating with remote SSIS port 135 is used & if it’s a SSIS package is running against a database server you need 1433 or as specified. Uses msdts1 protocol for service type msdts1.

SSAS

TCP

2393, 2394, 2725

OLAP Services 7.0 used TCP ports 2393 & 2394. Though Ms has reserved UDP ports 2393 & 2394 those are not used by OLAP Services. Analysis Services uses TCP port 2725. For backward compatibility, Analysis Services uses TCP ports 2393 & 2394 when connected with an OLAP Services 7.0 client.

SSAS

TCP

2383

Standard port for the default instance of Analysis Services. User configurable;

Browser SSAS

TCP

2382

Client connection requests for a named instance of Analysis Services that do not specify a port number are directed to SQL Server Browser.

RDP

TCP

3389

Providing the remote desktop to a client or VDI keep your eyes open because the default encryption certificate (RSA pk stored in mstlsapi.dll), is there with widows base install. A Man-in-the-Middle (MITM) attack can intercept the exchange of RDP encryption information. Check here for safety ribbons secure RDP using Transport Layer Security http://technet.microsoft.com/en-us/library/cc782610%28WS.10%29.aspx

For 6.0 Network Level Authentication offers much stronger protection http://blogs.technet.com/askperf/archive/2008/02/16/ws2008-network-level-authentication-and-encryption.aspx

Dynamic Port Range

To comply with Internet Assigned Numbers Authority recommendations, Ms has increased the dynamic client port range for outgoing connections. Since the release of Win 6.0 new default port range is 49152 – 65535 which was earlier 1025 through 5000.

Service Broker

User configurable; there is no default port. BOL conventional configuration uses TCP 4022.

SSL

TCP

443

When used with HTTP forming HTTPS, it provides an encrypted communication channel.

HTTP endpoint

Used when connecting through a url, this is user configurable; this can be customized while creating an endpoint. Port 80 for CLEAR_PORT traffic & 443 for SSL_PORT traffic.

HTTPS endpoint

TCP

443

Default instance running over an HTTPS endpoint, used for a connection through url which used SSL.

iSCSI

3260, 860

SQL Agent File Copy

135

Agent to copy backup files to the shared folder on the standby server.

137, 138, 139, 445

File copy on UNC shares.

SQL Debugger

TCP

135

Exception for IPsec traffic might also require you to set an inbound rule for 500 & 4500 if IPsec is used for network communication.

After opening port 135 include the applications Visual Studio à Devenv.exe / Management Studio à ssms.exe.

Database Mirroring

User configurable; there is no default port. While setting multiple instances be cautious to not to break the quorum. BOL conventional configuration uses TCP 7022.

Replication

TCP

1433

For push transactional replication a working 1433 between distributor & subscriber is all you need, however in pull subscriptions few other ports are needed; when you launch an initialization of a subscriber SQL uses the windows default port 445 for mapped drives to copy down scripts.

FTP (21) can be used initially to transfer schema & data over the internet; it can also use HTTP (80) or File & Print Sharing (ports 137,138, 139).

You can put merge replication to use WEB synchronization using port 80 or encrypted 443. Replication uses the IIS endpoint, when syncing over HTTP (80 by default but configurable), however IIS process connects to SQL Server through standard ports.

Keep in mind when synchronizing over the Web using FTP, there is no transfer between subscriber & IIS, it’s all amid IIS & the publisher.

Cluster Service

UDP

3343

Cluster services control & manage the cluster database. Like the Heartbeat process - Cluster network driver (Clusnet.sys) performs intra-node communication between each node of the cluster by periodically exchanging sequenced, unicast/multicast UDP datagrams in the cluster. This determines whether all nodes are running correctly & network links are healthy. Generally this does not happens over the public network.

There are cases when the range of random available IP ports that the cluster service uses to initiate communication through RPCs is less than 100 ports & connection to the Cluster Admin fails (refer to 154596(http://support.microsoft.com/kb/154596/ ) ).

RPC – 135 / Cluster Network Drv – 3343 / SMB – 445 / NetBIOS – 139 / RPC 5000-5099 / 8011-8031

  • 135 (RPC endpoint mapper/DCOM), (RPC endpoint mapper over UDP).
  • For nodes running multiple services, ports 5000-5099 (or more) may be needed for remote RPC connectivity. If closed, error 1721 might occur when you connect to a remote cluster.
                 Cluster service requires at least 100 ports for communication through RPC. Count of ports available may get too low when other services like DNS, WINS, Ms SQL Server service & others are using some of the necessary ports.
  • Ports 8011-8031 must be open for internode RPC connectivity or the cluster log will indicate that a SPONSOR is not available. Again these errors occur because there are not enough ports available for RPC communication between a node trying to join the cluster & a node that can sponsor the new node.

Cluster Admin

UDP

137

Random Ports

UDP

Check dynamic port range

RPC

TCP

135

Filestream

139 & 445.

SSIS

TCP

135

DCOM

WMI

TCP

135

Used by SSCM, it runs over DCOM (aka Network OLE) when accessing remote data. After initial connection DCOM randomly assigns a port for further communication where some tools may require a TCP port > 1024 (aka TCP high port) opened on the remote host.

IPsec traffic

UDP

500 & 4500

Should be set to allow ISAKMP traffic to be forwarded for both inbound & outbound filters.

MsDTC

RPC

Since NT 4, MSDTC has been performing as the transaction coordinator for components with COM & .NET architectures. Connected resources can be databases, message queues or file systems which may be distributed. Messages are sent on TCP 135 while the responses are on a dynamically assigned port.

In the end you may like to check these for further reading & detailed references for the above.

How to capture network traffic with Network Monitor KB 148942

Basics of Reading TCP/IP Traces KB 169292

Service overview & network port requirements for Windows Server system KB 832017

How to configure RPC dynamic port allocation to work with firewalls KB 154596

TCP Ports Needed for Communication to SQL Server through a Firewall KB 287932

Additionally check this for the loose vines you might don’t know that you have.

SELECT name, protocol_desc, port, state_desc, type_desc FROM sys.tcp_endpoints

Total article views: 8334 | Views in the last 30 days: 24
 
Related Articles
FORUM

Network-related or instance-specific error - Connection Medium Problem

Network-related or instance-specific error - Connection Medium Problem

FORUM

SERVICE ACCOUNT PASSWORD CHANGE ON CLUSTER INSTANCE

URGENT HELP= SERVICE ACCOUNT PASSWORD CHANGE ON CLUSTER INSTANCE

FORUM

named instance connection problem

named instance connection problem

ARTICLE

Combining AlwaysOn Groups With Failover Cluster Instances

This article discusses failover cluster instances and AlwaysOn groups

FORUM

connecting to an instance on a cluster

How do I reference an instance on a cluster

Tags
administration    
networking    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones