I have a collection of PowerPivot Excel workbooks in a SharePoint 2013 farm. I had configured everything including a SQL Server Analysis Services (SSAS) server in PowerPivot mode and SQL Server PowerPivot Service Application.
Suddenly filters and sliders in my all PowerPivot reports stopped working throwing following error message.
Then I checked ULS logs, which showed me the cause of the error
“Uncaught CLR exception crossing the Interop boundary: Microsoft.AnalysisServices.Streaming.ServerNotFoundException: There are no servers available or actively being initialized.
at Microsoft.AnalysisServices.Streaming.OnPremise.ServerPool.Microsoft.AnalysisServices.Streaming.OnPremise.IServerPool.GetAvailableServers()”
That means, the Excel Services cannot locate my SSAS server. Following are the diagnostics I did
- Check if the SSAS server is up – Success
- Ping the SSAS server from SharePoint serve and vice versa – Success
- Check if my SSAS PowerPivot instance is listed in Excel Services –> Data Model settings – Success
- Check if relevant services are running from SQL Server Configuration Manager – Success
- Check if firewall is enabled or ports are blocked – Gotcha !!
My domain firewall is enabled after a restart. Initially I kept it disabled to make things simple. But it seams that I need a concrete solution.
Following are the steps I used to open required ports
1. What are the ports need to be open?
As a practice I will open 2383, 2382 ports for a SSAS server. Since we have a named instance <SSAS Server>/PowerPivot, we need to open the custom port used by that instance.
2. How to find custom port for named SSAS instance
- Navigate to “C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig”
- Open msmdredir.ini in text editor
- Get the port specified in that file
3. Configure a firewall rule
- Open Windows Firewall with Advanced Security
- Create a new Inbound rule
- Specify following ports to open
That fixed the issue.
