Configure Access to SSAS Server for Excel PivotTable – Kepion Support Center

Author: Serena Wang

Introduction

This article describes the steps to enable access to SQL Server Analysis Services (SSAS) and allow connection from third party reporting tools, e.g. Excel PivotTable, Power BI Desktop.

Before You Start

Understand if the server that hosts the target SSAS instance is on-premise or an Azure VM.
An essential step in making SSAS available on the network is to unblock ports in a firewall. Windows Firewall is on by default and is blocking remote connections. You must manually open a port in the firewall to allow inbound requests to SSAS.
The port of a default SSAS instance is 2383. If you have a named instance or multiple instances, please refer to the section Determine the SSAS Port to identify the port.
Please note, only Windows AD and Azure AD Integrated authentication mode supports direct connection to SSAS.

Configure Windows Firewall

To allow access to an SSAS instance, you will need to open the SSAS port on the machine.

Step 1. Open Windows Defender Firewall with Advanced Security -> Inbound Rules. Click New Rule.

Step 2. In Rule Type, check Port and then click Next.

Step 3. In Protocol and Ports, check TCP and type the SSAS port number, e.g. 2383. Then click Next.

Step 4. In Action, check Allow the connection and then click Next.

Step 5. In Profile, clear any network locations that do not apply and then click Next.

Step 6. In Name, type a descriptive name for this rule, e.g. SSAS Server (2383), and then click Finish.

Once completed, you will find the inbound rule on the list.

Note: If users need to access the SSAS machine from the internet (e.g. connecting from home without VPN), then additional ports may need to be opened or forwarded. Please contact your IT team to ensure external network traffic on the specified port is routed to the SSAS machine.

Configure Azure Portal (Optional)

Note: If the target SSAS instance is hosted on-premise, you can skip this section.

If the target SSAS instance is hosted on an Azure VM, you also need to create an endpoint to allow access.

Log into Azure Portal, select the Virtual Machine icon on the left navigation pane, select the VM you are working on, click Endpoints, and click Add.

The port of a default SSAS instance is 2383. Create the endpoint as follows:

Test Connections

Now you can test your connection to the SSAS database from Excel PivotTable on your own laptop. Click here for more details.

Determine the SSAS Port

If the target SSAS instance is not the default instance (or you are not quite sure), follow the steps below to find the port an SSAS instance is currently running on.

Step 1. Open Task Manager on the server that is running the target SSAS instance. In the Details tab, locate the PID of the msmdsrv.exe process. In this example, the PID value for the MSSQLSERVER2017 instance is 7328.

Step 2. Open a command prompt window as Administrator. Type the following command, and press Enter.

netstat -abo | find /i "LISTENING" | find "7328"

In this example, the port number used by the MSSQLSERVER2017 instance is 49940.

You will then use 49940 to configure firewall on the machine, and create an endpoint in Azure Portal if needed.