I created a server alias that uses the TCP/IP network library. The client-side server alias is configured to Dynamically determine port. How can I find out which port the client is using so that I can lock down our firewall?
The port the client uses to communicate with an instance of SQL Server is the same port the instance uses to listen for client requests. You have several options for determining the port number a SQL Server instance is using. 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 started. Dynamic port allocation is a one-time occurrence. (See also, "Dynamic Port Detection").
To see the port number the server is using, launch the Server Network Utility from the server in question and click Properties in the TCP/IP entry in the Enabled protocols list. You can also check the port number an instance of SQL Server is using by looking in the error log for the specific instance. You should see an entry in the error log that looks like this:
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.
Alternatively, you can check the port number an instance of SQL Server is using by looking in the registry. Specific paths to registry keys vary based on how you've installed and upgraded SQL Server, but you should find a key similar to the following:
hkey_local_machine\softwaremicrosoft\microsoft sql serverInstanceName\MSSQLServer\Super SocketNetLib\Tcp\
where InstanceName is the instance name of the SQL Server you're using. The key will have an entry called TcpPort, which contains the port number the instance is using.
You need this information to lock down a firewall between the client and server. The client will determine which port to use by sending a special request to SQL Server on UDP port 1434. SQL Server will respond with a list of available server instances, along with the port numbers that each instance is using. You won't be able to connect to the SQL Server instance through the firewall if you block UDP port 1434.