Everything You Always Wanted To Know About Port Configuration But Were Afraid To Ask 

The port that SQL Server listens to is divided into two cases, depending on the Instance type:

Default Instance - In this case, SQL Server listens by default to port 1433.  As this port is very well known and commonly used, it is subject to many attacks.

Named Instance - In this case, SQL Server listens by default to a dynamic port.

What is a dynamic port?  A dynamic port is a port allocated to SQL Server dynamically, meaning that the port is not fixed.  The dynamic port allocation process goes like this:

1.     Only a Named Instance can use dynamic ports.

2.     When the Instance appears for the first time, the port appears to be configured as 0.  This means that the SQL Instance is requesting that the operating system allocate it an available port.

3.     Once the operating system allocates a port for the Instance, it starts listening to that port.

4.     The allocated port is entered in the registry.

5.     Each time that the Instance appears, it will try to listen to the port listed in the registry.

6.     If another program is using the port, the SQL Server Instance will ask for another port.

So how does the client know which port to connect to?

In the case of a Default Instance, the client attempts to connect to good old port 1433.

The problem starts when we use a Named Instance, in which case there are two ways of letting the client know which port to connect to:

1.     Connection String -  We can state the port to which the Instance is listening in the Connection String (a bit tricky - as the port is dynamic and can change over time).  If we want to state the port in the Connection String, we need to use only the computer name and the port number, without any indication for the Instance name.  For example - Myserver,1578 instead of Myserver/InstanceName.
Of course, in this case we have to know which port the Instance is listening to.  This will be discussed later.

2.     Using SQL Server Browser - This is a Service that runs in an environment that has at least one Named Instance.  When it is running, it constantly listens to port UDP 1434.  When a client tries to connect to a Named Instance as follows Myserver/InstanceName, what happens is that a request is sent to the browser for port UDP 1434, and the response is the port to which the Instance is listening.

It should be noted, that when using the default port, the SQL Browser and port UDP 1434 are not required.

At this stage we should ask the question - what the devil do we do if we want to work using a Firewall, instead of using a port that is known to everyone????

The possibilities are as follows:

1.     Changing the Default Instance port and stating it in the Connection String.  In this case, we need to make sure that this port is open in the firewall.

2.     To configure a fixed port for the Named Instance and to state that port in the Connection String.  In this case, we need to make sure that the port is open in the firewall.

1)     To configure a fixed port for the Named Instance and to continue using SQL Browser.  In this case, we need to make sure that both that port and port UDP 1434 are open in the firewall. When using a Named Instance with a dynamic port and SQL Browser, it won't be possible to enforce working through the firewall, as the port can change.  Therefore, it is better not to use this method when using a firewall.

So how do we change the port?

We use the SQL Server Configuration Manager... actually, we read this - http://msdn2.microsoft.com/en-us/library/ms177440.aspx

How do we know which port the Instance is listening to?

1.     Check the SQL Server Configuration Manager, then Network Configuration, then TCP.

2.     Check the registry

SQL 2005

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.MSSQLServer
SuperSocketNetLibTCP

SQL 2008
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10.MSSQLServer
SuperSocketNetLibTCP

So to summarize:

1.     When working with default Instances, we can change the port we are using (for security reasons). 

2.     When working with Named Instances, we can use either a dynamic port or a fixed port.

3.     If we change the default values, we need to state the port number in the Connection String: Myserver, xxxx (when stating the port number, don't state the Instance name).

4.     When using Named Instances, we can forego stating the port in the Connection String, and use SQL Server Browser, which uses port UDP 1434 and is used to notify applications which port is used by a SQL Server instance.