Opening Ports SQL Server Instances

Question

They recently blocked all the ports on our SQL Server. The Server has different instances, and opening port 1433 didn't work. What do we need to open up to allow access to SQL Server?

Answer 1

On the computer where the server instance are installed:

1. Launch the SQL Server Configuration Manager utility.
2. Open the SQL Server Network Configuration node on the tree control, you should see one child node for each server instance.
3. For each instance, select its node and note whether the TCP/IP protocol is enabled (if it's not enabled, there is no port).
4. Right-click the TCP/IP protocol entry and select Properties from the context menu. This will open the TCP/IP Properties dialog.
5. Go to the IP Address tab and scroll down to the bottom to the IPAll section to view the TCP Port.

If you haven't configured your servers previously, you are probably using dynamic ports. This is not what you want, because the port could change: that will invalidate your firewall settings. So, just clear the TCP Dynamic Ports entry and enter the current dynamic port number in the TCP Port entry for IPAll. After you restart the server instance, the server will be pinned to that port.

One follow-up note: if you want to reference the servers from outside the firewall by instance name, you'll have to allow the SQL Server Browser through the firewall, which is UDP port 1434. In my view it is better to reference instances by port from outside and don't worry about the SQL Browser. For example, instead of using MYSERVER\SQLEXPRESS in your connection string, use MYSERVER.mydomain.com,12345 (in this example SQLEXPRESS is running on port 12345). Note that the port syntax in a connection string uses a comma delimiter instead of the colon delimiter used for an http uri.

Answer 2

You must to set diferent ports to each instance (or multiple ips with the same port) Go to "SQL Server Configuration Manager", go to "SQL Server Network configuration", select protocols for the first instance, double click on TCP/IP, select tab "Ip address", at the end you will find the Group "IPAll", edit tcp port and put 1433, and remove value in "Dinamic ports tcp", restart the service. Do the same with the next instances but change 1433 with 1434 , 1435, or what ever you want. Then open your ports in the firewall.