Configure Access to SSAS Server for Excel PivotTable

Author: Serena Wang

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 and 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 a firewall's ports. Windows Firewall is on by default, 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 modes support 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. 

 

1. Open Windows Defender Firewall and select Advanced settings. 

 

 

2. Select Inbound Rules and then New Rule...

 

 

3. Select Port and then Next.

 

 

4. Select TCP and type the SSAS port number (e.g., 2383).

5. Click Next.

 

 

6.  Select Allow the connection and then Next.

 

 

7. Select applicable networks and then Next.

 

 

8. Enter a descriptive name for this rule (e.g., SSAS Server (2383)) and then click Finish.

 

 

Once completed, you will find the inbound rule in 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. 

 

1. Log in to Azure Portal and select the Virtual Machine icon on the left navigation pane.

2. Go to the target VM > Endpoints > Add.

 

 

3. Configure the endpoint as follows:

 

 

Note: Make sure to replace the SSAS port number you used when configuring the firewall.  

 

4. Click OK. 

 

Test Connections

Now you can test your connection to the SSAS database from Excel PivotTable. Refer 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 on which the SSAS instance is currently running.

 

1. Open Task Manager on the server that is running the target SSAS instance.

2. In the Details tab, locate the PID of the msmdsrv.exe process.  In this example, the PID value for the MSSQLSERVER2017 instance is 7328.

 

 

3. Open a command prompt window as Administrator.

4. 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 the firewall on the machine, and create an endpoint in Azure Portal if needed.