Understanding SQL Server TCP ports

Introduction

There are considerations to be made before making changes to your firewall.  You will need to determine if SQL is using a Dynamic port of a Static port, it may also be prudent to check if the SQL Browser service is running as this service also requires an exclusion setting within the firewall.  

This topic explains how to identify the SQL Ports in use by the IRIS Software.  

 

SQL Configuration Manager

It is also possible to use SQL Configuration manager to identify or set the TCP ports SQL Server will communicate on.

  1. Click Start, Programs, Microsoft SQL Server 2008, Configuration Tools, SQL Server Configuration Manager.

  2. Click SQL Server Network Configuration.

  1. Click Protocols for MSSQLSERVER

  1. Then double-click TCP/IP.

The following screen displays:

  1. Select the IP Addresses tab:

  1. Scroll to the very bottom of the window.

  2. The IPALL section will now be visible. From here it will be possible to determine if the instance you have clicked on is using a Dynamic or Static port.

 

For a Dynamic Port

 

For a Static Port

Once it is determined which TCP Port SQL Server is using it is now possible to correctly configure the windows firewall or installed 3rd party firewall product.

 

Identifying the relevant port number using the Registry Editor

There is no distinction between default and named instances. An instance is assigned a number based on the order it was installed. We first need to locate the registry key for the instance, which looks like:

 

For SQL Server 2005

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.#

# is the number assigned to the instance. The instance name is stored as the default value for this registry key. For a default instance, it is MSSQLSERVER.

Once the registry key for the instance is found, we know the TCP/IP registry key is

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.#\MSSQLServer\SuperSocketNetLib\TCP\IPAll

 

For SQL Server 2008

Default instance

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\TCP\IPAll

Named instance

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.(InstanceName)\MSSQLServer\SuperSocketNetLib\TCP\IPAll