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

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



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



Step 3: We can see Multidimensional cubes only



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



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.



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



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



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



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