Skip to main content

Open Default Port to Connect to Analysis Services Cubes

Overview

Windows Firewall helps prevent unauthorized access to computers in the network. By default, Windows Firewall will be turned on after the operating system is installed.  If a firewall is turned on and not configured correctly, any 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, your database administrator needs to configure the firewall on the computer that is running SQL Server Analysis Services to allow access to users.

This article describes how to quickly configure Windows Defender Firewall in newer versions of Windows to allow SQL Server Analysis Services access to users.

Process

This KB article assumes the default port (2383 ) for SQL Server Analysis Services is being used. To check if the service is running on a port other than the default port, please see the Microsoft support site at the following address: http://support.microsoft.com/kb/2466860

  1. Select the Start button, enter Windows Defender Firewall with Advanced Security and select it.

  2. In Windows Defender Firewall with Advanced Security, select Inbound Rules > New Rules. The New Inbound Rule Wizard page is displayed.

    mceclip3.png

  3. In Rule Type, select Port option to control connections for a TCP or UDP Port. Click Next .

  4. In Protocol and Ports, specify the protocols and ports to which the firewall 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 Services will be assigned a dynamic port. In this scenario, make sure that SQL Server Browser Service is running and that 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 that is used by Analysis Service Named Instance should be open in the Windows Firewall. You can then connect to the 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. 

  5. In Action, specify the action to be taken when a connection matches the conditions specified in the firewall rule. Select Allow the connection and click Next >.

    mceclip6.png

  6. In Profile, specify the profiles for which this rule should apply.

    Select 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. Select Next >

  7. In Name, provide a name and description. Select 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 Microsoft MSDN.

Was this article helpful?

We're sorry to hear that.