Windows Firewall helps prevent unauthorized access to computers in the network. By default, Windows Firewall will be turned ON once the operating is installed. If a firewall is turned ON and if it is not configured correctly then attempts made by the users to connect to SQL Server Analysis Services will be blocked. In order to access an instance of SQL Server Analysis Services which is behind a firewall, database administrator needs to configure the firewall on the computer that is running SQL Server Analysis Services to allow users access.
This article, will review how to quickly configure Windows Firewall in Window Server 2008 or in Window Server 2008 R2 to allow SQL Server Analysis Services access to users.
Click Start → All Programs → Administrative Tools → Server Manager to open up Server Manager.
In Server Manager, expand the Configurations tab and then expand Windows Firewall with Advanced Security. Right click Inbound Rules and click on New Rule... to open up New Inbound Rule Wizard.
In New Inbound Rule Wizard's Rule Type Page, select Port option to control connections for a TCP or UDP Port. Click Next.
In Protocol and Ports, specify the protocols and ports to which this rule applies. Choose the TCP option and then specify the port number as 2383. Click Next to continue with the wizard.
If you have installed Analysis Services as a named instance on the server, then Analysis Service will be assigned a dynamic port. In such a scenario, make sure that SQL Server Browser Service is running and the UDP Port 1434, which is used by SQL Server Browser Service, is open in the Windows Firewall. Secondly, make sure that you have opened TCP Port 2382 in the Windows Firewall, so that the clients can connect to the SQL Server Browser and be redirected to the named instance of SQL Server Analysis Service or else the dynamic port which is used by Analysis Service Named Instance should be open in Windows Firewall. You can then connect to server in the format ServerName\InstanceName.
Alternatively, you can assign a fixed port, and then unblock access to that port. This approach offers better auditing capability than if you allowed access to the program executable. For this reason, using a fixed port is the recommended approach for accessing any Analysis Services instance. A thorough description of setting up a fixed port for an Analysis Services Instance can be found here.
In Action page, specify the action to be taken when a connection matches the conditions specified in this rule. Choose, Allow the connection and click Next .
In Profile page, specify the profiles for which this rule should apply.
Choose, Domain , as you want everyone connected to its corporate domain to get connected to the SQL Server Analysis Services Instance provided they have permission to connect to the SQL Server Analysis Services Instance. Click Next .
On the Name Page, provide a name and description. Click Finish to complete the wizard.
You will now be able to connect to a remote Analysis Server from excel. For detailed information about configuring the Windows Firewall for Analysis Services access please visit the Microsoft MSDN article on the topic here.