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 3

  • 20 February 2014
  • Author: CraigLove
  • Number of views: 14068
  • 0 Comments

This is the 3rd part of my series of blog posts I am writing regarding configuring firewalls to permit connections to SQL Server services.

In Part 1, I reviewed opening the port for the default instance of the SQL Server relational database engine. 

In Part 2, I covered how to set the static port for named SQL Server relational database instances and also how to configure the SQL Browser service to permit connections using the named instance.

In this blog post I will review the firewall settings for SQL Server Analysis Services Multidimensional servers.

Just as with the SQL Server relational database engine, the following issues need to be addressed when configuring your server firewall to permit SSAS MD connections.

  • Determining the ports for the Analysis Services default and named instances.

  • Ensuring that the SQL Browser can be accessed so that clients can connect to named instances using the instance name.

Determining the Analysis Services Port Number

The default instance of SQL Server Analysis Services Multidimensional uses port 2383 unless changed manually.  The process for creating the firewall exception for this port is identical to what I described in both Part 1 and Part 2 of this series.  I have included a screen shot of the completed entry below.

Port 2383

Your job is now complete if you are only using a default instance and you are using the default port.  If not, read on.

SSAS MD named instances will be assigned a different port # each time the service is started.  Therefore, you need to manually assign a port number.  This can be done within the SQL Server Management Studio Object Explorer.  Connect to the named instance and access the server properties by right-clicking on the server name and selecting properties. 

In this example, the server name is SQL2012-A and the named instance is SQL2012.  (Don’t ask, it’s just how it turned out!)

Accessing SSAS Server Properties

In the General pane, locate the setting for Port.  This will be zero (0) by default which means that the port is dynamically assigned.  Enter an unused port number and click OK to close the properties pane.  You will need to restart the instance for the new static port to take effect.

Setting SSAS Port

 

After setting port #:

After Setting SSAS Port

Now that the named instance has a static port assigned, you need to open a port on the firewall.  I have included an image below showing the port openings for the default instance and the SQL2012 named instance.

Both SSAS Ports Open in Firewall

At this point, I cannot connect using the name of the instance SQL2012-A\SQL2012.   The message returned is “A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running.”

This is exactly the same problem I reviewed in Part 2 for SQL Server Engine named instances.  The SQL Server Browser directs Analysis Services connections for named instances to the correct server port.  If this service is not running or is not accessible due to a firewall, the client will not be able to connect using the instance name.  If you know the port number for the named instance, you can use it to make connections.  Note that a colon is used before the port number.  This is different than when connecting to a SQL Server Engine instance where a comma is used!  

SSAS Connection using Port Number

In the SQL Server Engine example, the SQL Brower required an opening for UDP Port 1434.  In this case we need to create a firewall opening a TCP port for 2382.  

SQL Browser Ports open in Firewall

Now I can connect using the name instead of the port.

SSAS Connection using Instance Name

For the next entry in this series I will cover the same issue with SQL Server Analysis Tabular servers.

Craig

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

CraigLoveCraigLove

Other posts by CraigLove

Please login or register to post comments.