I've recently found myself in the position of quickly deploying a production app on SQL Server 2008 (EXPRESS), and I've been having some issues with configuring firewall rules between our web server running the ASP.NET app and our database server.
Everything that I can find on the internet claims that I should only need to have TCP ports 1433/1434 and UDP port 1434 accessible on the database server. However, we were unable to get connectivity going between the web app and the database with just those ports. With the help of one of the guys in our datacentre, we discovered that there was traffic also going to TCP port 2242 on the database server. After opening this port, everything worked, but we're not sure why.
Later on, I had to reinstall SQL Server due to some disk space issues, and found that the problem had resurfaced – after another session with the packet sniffer, we discovered that this time traffic was going to TCP port 4541 on the database server. My question is, is there some configuration option that I'm missing in SQL server that's making it choose random ports? I'd like to have our firewall rules locked down as much as possible, and of course we'd like to avoid any future mysterious connectivity issues, especially once the app is live.
Both servers are running Windows 2003 R2 X64.
Best Answer
To answer my own question, it seems that by default SQL Server might use port 1433, but on my setup it was set to use dynamic ports. This article explains how to configure a fixed port:
http://msdn.microsoft.com/en-us/library/ms345327%28v=SQL.100%29.aspx