to SQL Server Analysis Services (SSAS) through Windows firewall.
I found myself installing and setting up a SSAS multidimensional
default instance on a Windows 2012 R2 Server and created a test project to
validate the installation. All worked
well when I did a remote desktop onto the server, however, when I tried connecting to the service from my laptop on the same network, SQL Server Management Studio (SSMS) returned an error that it
was not able to find the server using the server name, or the fully qualified
name, or IP address of the server. I
would alt-tab over to the RDP session directly on the server, run a query in
SSMS, and behold, it works. What is
I tried connecting using
Excel 2013, and got a similar error message.
This is telling me that the condition is not specific to an individual
tool, but occuring between my laptop and the server. The
server and laptop are on the same physical network, but different domains with
a trust exists between them. Explains
why I can RDP into the server with my windows credentials wtihout issue. I
started to look at other causes by searching the blogs online, and one of the
top candidates was a firewall in place blocking the TCP connection
attempt. Makes sense as more and more
features and setting are being turned off by default for security
purposes. No smoking gun with an error message that a
firewall is active, so I had do some investigation into the possibility. As it
turned out, Windows Firewall was turned on for the server, and a Group Policy
was in effect for some settings.
Analysis Services communicates from the client to the server
using TCP, and a default instance uses port 2383. You can confirm this by running a netstat –ao –p TCP command in a command window to see what
ports the server is actively listening on.
The –o option displays the
owning process ID (PID) associated with the connection.
To get the PID of Analysis Services to confirm which port it
is listening on, go to SQL Server Configuration Manager, and Click on the SQL
This confirms that the default instance is PID 2656, and
from the netstat output above, we can see that it is listening on the default
port of 2383 (TCP 0.0.0.0:2383).
The next step was to explicitly enable communications over
that port by creating a new Inbound Rule in Windows Firewall. Go to Control Panel and Select Windows
Firewall. Click on Advanced Settings,
and the right click on Inbound Rules and specify New.
Here are the steps to create
a Inbound Rule in Windows Firewall.
Specify Port as the
Specify the default TCP port of 2383 for Analysis
Specify Allow the connection.
Allow access for inside the domain.
Finally, give it a meaningful name.
When I now do a connection test from SSMS and Excel on my laptop,
I am able to connect to the service without issue. Success!!!!
Now, if you have a Named Instance, you will have something a
bit different. You will need to give
access to the port that the SQL Browser service is listening on, as the client
will have to connect to the Browser to know how to connect to the Named
Instance. Since SSAS uses TCP, and not UDP that SQL
Server can use (SQL Server can use UDP on port 1434 in addition to TCP), you
have to open up a TCP port for the Browser and that TCP port number is 2382. Create a separate Inbound Rule to
support. Alternatively, if you define a fixed port for
the Named Instance, which means you do not require the SQL Browser Service, you
can create an Inbound Rule to that port number that you define.