Checking Port Numbers

[Sunite]

Checking Port Numbers





Q

I created a server alias that uses the Transmission Control Protocol/Internet Protocol (TCP/IP) network library. The client-side server alias is configured to dynamically determine which port to use. How can I find out which port the client is using so that I can lock down our firewall?



A


The port that the client uses to communicate with an instance of SQL Server is the same port that the instance uses to listen for client requests. You have several options for determining the port number that is being used. Keep in mind that SQL Server dynamically chooses the port when the server is first installed. The same port number is used each subsequent time the server is stopped and re-started. Dynamic port allocation is a one-time occurrence.

To check the port number being used by an instance of SQL Server, do any one of the following:



Start the Server Network Utility and click Properties for the TCP/IP entry in the enabled protocols list.



Look in the error log for the specific instance. You should see an entry in the error log that looks similar to the following:

SQL Server listening on 127.0.0.1: 1362.

The four-digit number after the colon is the port that SQL Server is listening on for the IP address, which is specified to the left of the colon.



Look in the registry. Specific registry subkeys vary based on how you've installed and upgraded SQL Server, but you should find a key similar to the following:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\
InstanceName\MSSQLServer\SuperSocketNetLib\Tcp\

Where InstanceName is the name of the instance of SQL Server you're using (for a default instance, the InstanceName is MSSQLServer). The key will have an entry called TcpPort, which contains the port number being used.

You need the port number to lock down a firewall between the client and server. The client determines which port to use by sending a special request to SQL Server on User Datagram Protocol (UDP) port 1434. SQL Server responds with a list of available server instances, along with the port numbers that each instance is using. Connections cannot be made to the SQL Server instance through the firewall if you block UDP port 1434.