Connecting to the Tabular Instance of an Azure-hosted SQL Server Analysis Services Server

Question

I have set up an Azure Virtual Machine with a SQL Server 2016 Standard Edition image. I have installed an instance of Analysis Services Tabular on the machine and configured endpoints such that I can access it from my local machine.

I have deployed SQL Server, SSAS Multidimensional, and SSAS Tabular databases to this machine. I am able to connect to the relational databases, and when I connect to Analysis Services I either use the IP Address or DNS name, and I am able to connect to SSAS Multidimensional databases. Usually I would add the suffix \tabular to the Server name to connect to a Tabular Instance when accessing it from Excel/Tableau/other reporting tool.

However using the \tabular suffix in the Server Name I receive the error "Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser server is running on that server". I have checked the virtual machine, and I have that service running. I'm wondering if perhaps \tabular is not the correct syntax to connect to a tabular instance of an Azure-hosted SQL Server.

Any help or advice would be appreciated.

Answer 1

I would RDP to the Server and connect in SSMS to the Tabular instance. Right click on Object Explorer on the Server Node and choose Properties. Go to the General tab and change the Port property to something like 1234 so the Port will be fixed. Restart the SSAS Tabular service. Open Windows Firewall app on the VM and open ports 2382 and 1234. Then edit the Network Security Group for that VM in the Azure portal and open tcp port 2382 for SQL Browser and 1234 (or whatever port you chose) for SSAS Tabular. Now you can connect to either of the following Server names:

Servername\TABULAR or Servername:1234

Documentation about this can be found here.