Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

SQL Server Firewall Settings - Part 2

  • 11 November 2013
  • Author: CraigLove
  • Number of views: 13126
  • 0 Comments

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.

Both SQL Server Instances

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.

Static Port

 

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 #>”.

SSMS Connect Using Port

 

SSMS Object Explorer with Port

 

If you attempt to connect using the instance name, you may be greeted with a nasty surprise. 

SSMS Cannot Connect

 

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.

Browser Stopped

 

Once running, you should be able to connect to the instance using the name.

SSMS Connected Using Instance 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.

Inbound Rule - General

Inbound Rule - Programs and Services

Inbound Rule - Computers

Inbound Rule - Protocols and Ports

Inbound Rule - Scope

Inbound Rule - Advanced

Inbound Rule - Users

 

Both inbound rules present in the firewall settings.

Both SQL Server Inbound Rules

 

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”. 

What gives?

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.

Browser Inbound 1

Browser Inbound 2

Browser Inbound 3

Browser Inbound 4

Browser Inbound 5

All 3 Inbound Rules

 

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.

Print
Categories: Analysis Services
Tags:
Rate this article:
No rating

CraigLoveCraigLove

Other posts by CraigLove

Please login or register to post comments.