Submit a request

Configure access to SSAS Server for third-party tools

Avatar
Kepion Support
  • Updated

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 and Power BI Desktop).

 

 

Before you start

  • Identify if the server that hosts the target SSAS instance is on-premise or an Azure VM.
  • 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.
  • 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. 

 

advancedsettings.png

 

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

 

newrule.png

 

3. Select Port and then Next.

 

mceclip4.png

 

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

 

mceclip5.png

 

5. Select Next.

6.  Select Allow the connection and then Next.

 

mceclip6.png

 

7. Select applicable networks and then Next.

 

mceclip7.png

 

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

 

mceclip8.png

 

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

 

mceclip10.png

 

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.

 

ConfigureAccesstoSSAS-AddVMEndPoint.png

 

3. Configure the endpoint as follows:

 

  • Name: Enter a descriptive name of the endpoint.
  • Protocol: Select TCP.
  • Public port: The port number used to configure the firewall rule.
  • Private port: The port number used to configure the firewall rule.

 

3-create_endpoint.png

 

4. Select OK. 

 

 

Test connections

Now you can test your connection to the SSAS database from Excel PivotTable. Refer here for more details.

 

 4-data_connection_wizard.png

 

 

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.

 

mceclip0.png

 

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.

 

mceclip2.png

 

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

Related articles