SQL Server Browser Service and “Network Discovery”

Client Applications normally connect to the Default SQL Server Instance on TCP Port 1433. Additional Instances are normally allocated Dynamic TCP Port Numbers by the Server Operating System.

For the benefit of Clients, Microsoft SQL Server 2005 provides the “Browser Service” (UDP Port 1434) to advertise the Names and Port Numbers of each Instance of SQL currently installed.

When a Client requests access to a particular Instance, the Browser Service responds with the appropriate Port Number and the client uses this to establish a connection.

If the Client is a Remote Client, Windows Firewall (on the SQL Server) needs to be configured to allow inbound traffic to the ports used by:
[1] The SQL Browser Service (UDP 1434).
[2] The Default SQL Instance (TCP 1433).
[3] Additional Named Instances (Dynamic TCP Ports).

It is easy to see that in this scenario, it is impossible to pre-configure the Firewall for a Dynamic Port (since we do not know what the port number is going to be). Therefore, additional Instances should be allocated a “Fixed TCP Port Number” using the SQL Server Configuration Manager. In this way, the correct firewall Exceptions can be configured.

Choosing a Fixed Port
To view the current Dynamic Port of a particular Instance, use the “SQL Server Configuration Manager” (see below).

SQL Server 2005 Dynamic Ports 001

To turn this into a “Fixed Port”, remove the current value from the “TCP Dynamic Ports” field and place it into the “TCP Port” Field. Restart the SQL Service. It will now be a case of Configuring the SQL Server Firewall to pass traffic to the Fixed TCP Port (in this example Port “1266”).

SQL Server 2005 Dynamic Ports 002

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s