In my previous blog entry on firewall settings, I provided guidance for setting up a firewall opening for the default instance of SQL Server. In this second entry, I will provide the steps for setting and allowing a connection to a non-standard port. I am using SQL Server 2008R2 but the steps shown are the same for SQL Server 2012.
By default, SQL Server uses port 1433 for the default instance. If you plan on using SQL Server named instances (i.e. ServerName\Instance), you will need to decide if you want to use a dynamic or static port for the named instance. For this post I will set a static port for a named instance.
The second named instance I installed on test server is called “SECOND”. For this example I will start with the firewall on my database server turned OFF in order to simplify the instructions. I will turn firewall back on later.
In order to set a static port, launch the SQL Server Configuration Manager and access the TCP/IP Properties. The IP Addresses tab is where you set the port for each IP address you are using. For this example I am only using 192.168.4.10. (I am not going to do anything fancy with multiple addresses!) By default, the setting in “TCP Dynamic Ports” will be 0 meaning that the instance is using dynamic ports. Eliminate the 0 and enter a free port in the “TCP Port” field. Make the same change in the IPAll section of the tab. You will need to restart the instance for the port change to take effect.
You should now be able to connect to instance using the Port #. This can be done in SQL Server Management Studio by using the “Server Name (comma) <Port #>”.
If you attempt to connect using the instance name, you may be greeted with a nasty surprise.
The reason for this inability to connect (assuming the server firewall is turned off) is likely that your SQL Browser service is not running. In cases of non-standard ports (static or dynamic) the SQL Browser is responsible for connecting the instance name request to the port.
Once running, you should be able to connect to the instance using the name.
Now you must add a new firewall inbound rule for the named instance for Port 40001. I am including screen shots of the configured rule below. My earlier blog entry provides detailed guidance for creating an inbound rule.
Note that there are some interesting additional settings such as “Computers > Authorized Computers” that you can use for the rule to restrict the rule even further. These are beyond the scope of this blog entry but I recommend that you play investigate them at some point.
Both inbound rules present in the firewall settings.
Now, with the firewall on you should be able to connect using the “Server Name (comma) <Port #>” method. However, you will not be able to connect using “ServerName\InstanceName”.
The reason for this is that the SQL Browser Service also must be configured to communicate through the firewall. Below are the steps you can follow using the Inbound Firewall Wizard to permit this communication.
Now that the SQL Browser can communicate through the firewall, it is now possible to connect to the second instance using “ServerName\InstanceName” method.
I hope this helps!
On my next posting for firewalls, I will provide guidance on SQL Server Analysis Services.