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 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.
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!)
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.
After setting 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.
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!
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.
Now I can connect using the name instead of the port.
For the next entry in this series I will cover the same issue with SQL Server Analysis Tabular servers.