Connect to Azure Analysis Services

This document outlines the various methods and tools you can use to connect to your Azure Analysis Services server. Successful connection is the first step to querying data, building models, and administering your Analysis Services instance.

Connection Strings

The primary way to connect to Azure Analysis Services is by using a connection string. The format is generally:

Provider=MSOLAP;Data Source=.asazure.windows.net;Initial Catalog=;User ID=;Password=;Impersonation Level=Impersonate;

Key Components:

Provider: Usually MSOLAP for Analysis Services.
Data Source: The fully qualified domain name (FQDN) of your Azure Analysis Services server. This typically follows the pattern <server_name>.asazure.windows.net.
Initial Catalog: The name of the database within your Analysis Services instance you want to connect to. If omitted, you connect to the server instance itself.
User ID and Password: Credentials for authentication. For Azure AD authentication, this might be your Azure AD username and password, or an application principal.
Impersonation Level: Controls how the connection is impersonated. Impersonate is common.

Note: For Azure Active Directory (Azure AD) authentication, it's recommended to use Azure AD accounts or service principals instead of SQL Server authentication.

Tools for Connecting

1. SQL Server Management Studio (SSMS)

SQL Server Management Studio is a comprehensive tool for managing and interacting with Analysis Services. To connect:

Open SSMS.
In the 'Connect to Server' dialog, select Analysis Services as the Server type.
Enter your server name (e.g., myserver.asazure.windows.net).
Choose your Authentication method. Common options include:
- Azure Active Directory - Universal with MFA: Recommended for interactive user sign-in.
- Azure Active Directory - Password: For Azure AD accounts.
- Windows Authentication: If your machine is domain-joined and your account has permissions.
Click Connect.

2. Visual Studio with Analysis Services Projects Extension

For developing tabular models, Visual Studio is essential. When working with an existing Azure Analysis Services database, you can connect directly:

In Visual Studio, go to File > Open > Project/Solution.
If you have a tabular model project, open it.
In the Solution Explorer, right-click on the Model node and select Connect.
Enter your server name and authentication details, similar to SSMS.

3. Power BI Desktop

Power BI Desktop can connect to Azure Analysis Services as a data source.

Open Power BI Desktop.
Click Get data.
Search for and select Azure Analysis Services database.
Enter your server name.
Choose your connection mode (Live Connection is typical for AS Azure).
Authenticate using your Azure AD credentials.

4. Tabular Editor

Tabular Editor is a popular third-party tool for authoring, editing, and administering Analysis Services tabular models.

Download and install Tabular Editor.
Open Tabular Editor.
Click File > Open > Connect to server.
Enter your server name and select your authentication method.

Permissions Required

To connect to an Azure Analysis Services server, your user account or service principal must have appropriate permissions granted. Common roles include:

Administrator: Full control over the server.
Database Owner: Full control over a specific database.
Reader: Allows querying the database.

Permissions are typically managed within the Azure portal or through SSMS by assigning users to server roles or database roles.

Troubleshooting Connection Issues

Firewall Rules: Ensure that firewall rules in Azure or on your local network allow connections to the Analysis Services server's port (typically 443 for public endpoints).
Credentials: Double-check your username, password, and tenant information for Azure AD authentication.
Permissions: Verify that your account has been granted the necessary roles (e.g., Administrator, Database Owner) on the server or database.
Server Name: Ensure you are using the correct FQDN for your server.