Azure – Connect to Azure VM named instance Tabular model from on premise Excel

September 6, 2015 Leave a comment

As we know, Excel by default connects to default instance of Azure VM SQL installation but requires some settings in case of named instances.

Here are simple settings to connect to Azure VM named instance tabular models for Excel reporting.

Environment information:

1. Azure VM, SQL Multidimensional models with default instances
2. Tabular models with named instance
3. Azure VM is on different domain to on premise machine domain

Step 1: We have Multidimensional on default instance and tabular on named instance in Azure VM

01. Azure VM - OLAP DBs

Step 2: Connecting to VM server from Excel and it will show only default instances, in this case SQL server default port 2383

02. MOLAP Default port

Step 3: We can see Multidimensional cubes only

03. MOLAP Cube

Step 4: Trying to connect to named instance and received connection error. Here error message is bit confusing (at least to me) by showing SQL Browser but SQL Browser is running when checked on services

04. Tabular Connection Error

Step 5: This is the key, we need to setup the dedicated port for named instance and open the same port on VM machine for client connections.

On tabular server properties, go to General and setup the port number, here used 2381 but it is good to check that this port is not used for any other purposes on VM.

05. Tabular Instance Port settings

Step 6: Open the Azure VM endpoints to the same port number using Manage Windows Azure portal

06. Azure VM Endpoints Ports

Step 7: Now connect to tabular instance using the port number in the server name rather than name of the instance

07. Name Instance port in connection

Step 8: We will see tabular model and ready for reporting

08. Tabular Model

Points to consider:
1. These settings only required for named instances but doesn’t require any settings if you have only default server with (either OLAP or tabular) SSAS installation
2. Here Azure VM is on different domain to on premise local domain so not used windows logon settings

Categories: SSAS Tags: