Azure Analysis Services: How to open a connection from .NET
In my previous post I walked through the options for integrating into Azure Analysis Services using .NET client SDKs, PowerShell cmdlets and REST APIs.
I also laid out a simple guide to choosing the right framework to fit your scenario. In each of the cases, the first step that you'll need to deal with is creating and opening a connection to the server - just like any other database technology. This post explains the ins and outs of creating Azure Analysis Services connections, including code samples for each of the key scenarios.
Connection string basics
If you've done any work with any other database technology, then an Azure Analysis Services connection string should be pretty familiar. This article from Microsoft explains things quite nicely, but there's some specifics about the cloud-hosted Azure Analysis Services connection info that differ from an on-premise version.
Here's the basic structure of a connection string, using username and password authentication:
Provider=MSOLAP;Data Source=<Data Source>;Initial Catalog=<Initial Catalog>;User ID=<User ID>;Password=<Password>;Persist Security Info=True;Impersonation Level=Impersonate
The Data Source value is bit that needs explaining, as there's some Azure Analysis Services specific conventions that need to be followed. The naming schema for the Data Source is as follows:
asazure://<region>.asazure.windows.net/<servername>
Where region is the Azure region in which the Azure Analysis Services instances has been deployed, and servername is the name you used to create the instance.
Conveniently, as you'd expect, this value is displayed inside the Azure Portal, when looking at the overview blade of an Azure Analysis Services instance. The screenshot below shows an instance called endjindemo, deployed into uksouth region:
The Initial Catalog value is the name of the database inside the Azure Analysis Services instance that you want to connect to.
N.B. For the most part, the term "database" and "model" can be interchanged within Azure Analysis Services. Within the portal UI, the notion that a model belongs to a database is simplified by only referring to models. So,
Initial Catalogwill, in fact, most likely just be the name of your model.
Query replicas
Azure Analysis Services is a cloud-hosted, platform-as-a-service version of Analysis Services. One of the clear benefits of this model is that you can scale your instances up and down according to the processing power needed and only pay for what you use. As well as scaling up and down (by using the different tiers available), Azure Analysis Services also allows horizontal scale out.
The Query Replicas feature allows queries to be distributed among multiple, read-only, replicas of the data model, reducing query response times during high load. You can also separate processing from the query pool, so that query response times are not affected by background processing operations.
However, if you have decided to separate your processing server from the query pool, then your connection string will change, according to what type of connection you need. The important bit to note is that the management server name includes a special :rw (read-write) qualifier that needs to be appended to the end of the Data Source name. This is also conveniently displayed in the Azure Portal as before:
The advice from Microsoft is "For end-user client connections like Power BI Desktop, Excel and custom apps, use Server name. For SSMS, SSDT, and connection strings in PowerShell, Azure Function apps, and AMO, use Management server name.", which is very sensible. However, if you're considering building custom applications over the top of Azure Analysis Services, it's worth noting that there may be occasions that you still want to use the Server Name value (i.e. without the :rw) to benefit from the performance of using the query replicas. If not, you'll be tying all your custom querying to the management server.
The important thing is to understand that there are two connection options when Query Replicas are enabled in Azure Analysis Services, and you should use the right one according to your use case. It may be that your application integration uses both connection types, under difference circumstances (i.e. executing queries vs. updating model metadata).
Token-based access
The connection string example above uses username and password authentication, which may be appropriate for your scenario, however other options are supported, as listed in this article. For example, Azure Analysis Services supports Integrated Azure Active Directory authentication, picking up the credential from the cache if available.
Our use cases were all based on integrating Azure Analysis Services into custom applications - hosted in Azure App Service, either as Web Apps, or Functions Apps running under Managed Identities. Managed Identities are a special kind of service principal - a non-human account that can be used in automation, or other administrative tasks that require authentication. For us, username and password wasn't going to work, but luckily Azure Analysis Services supports a different type of authentication flow based on tokens.
This article explains how to retrieve a service principal token and use it in place of a password in the connection string. However, the example requires a application/client secret to have been set up for the service principal, which is essentially a password, stored in Azure Active Directory. There are two downsides to this approach:
- The Azure AD application credentials need to be stored and managed somewhere.
- The Azure AD application credentials expire, and so need to be renewed, else can lead to application downtime.
Using a Managed Identity removes the need for the application/client secret and instead uses the AzureServiceTokenProvider to retrieve the authentication token for the running application. This article goes into more detail about how this works in relation to accessing Azure Key Vault, but the principle is the same. Managed Identity allows Service-to-Azure-Service authentication, removing the need for username and passwords, and Azure Analysis Services supports this approach.
To retrieve the authentication token, you need to request one for the specific resource you're interested in - i.e. our Azure Analysis Services instance. We don't need the actual server name, just the region that we've deployed in to.
When this code runs under a Managed Identity, then the token returned can be used to authenticate to other Azure services as this identity. The token is used in place of a regular password, and when doing this, you don't need to specify a user name:
N.B. The great thing is that the same token can be used when connecting to the REST API, passing it as a Bearer token in the Authorization HTTP header. So, from .NET, the same Managed Identity token-based authentication pattern can be used for ADOMD, AMO and the REST API for Azure Analysis Services.
Remember, the Managed Identity needs to have access to the Azure Analysis Services instance, just like any other user account, with the appropriate roles and permissions according to what you're trying to do.
When adding the Managed Identity to Azure Analysis Services, you should specify the user information in the following format:
app:<ManagedIdentiyClientId>@<AzureActiveDirectoryTenantId>
Opening the connection
Once you have the connection string, you can use it to create the connection to Azure Analysis Services, according to the client SDK you're using. The following examples show how to use the connection string to create the connection in each case.
ADOMD
When using ADOMD, you use the AdomdConnection class to create and open the connection using the connection string.
AMO
When using AMO, you create a new Server object, and call .Connect() to open the connection with the connection string.
REST API with Bearer token
Finally, when using the REST API, pass the token in the Authorization HTTP header when issuing requests to your Azure Analysis Services instance.
Here's the link to the examples in full.
Conclusion
Opening a connection to Azure Analysis Services is straightforward, and similar to any other database technology. However, there are some nuances around how the connection string is formed, and there's options around using a token-based approach instead of the traditional username/password combination.
This post should give you everything you need to get started, allowing you to make the initial connection from within your application, whatever the scenario.
