Connecting 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 going on?
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 Server Services.
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 option.
Specify the default TCP port of 2383 for Analysis Services.
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.
by Vince Napoli
Business Intelligence Architect
Vince Napoli is a Data Warehouse and Business Intelligence Architect with over 20 years’ focus on DW/BI and SQL Server. A Mainframe developer since 1987, Vince specializes in MVS technical support, System Testing, Data Warehousing/ Business Intelligence architecture and development, SQL Server DBA/ ETL and database development. He is a Professional Association of SQL Server member and former president of the Philadelphia SQL Server User group.