Azure Analysis Services is a fully managed platform as a service (PaaS) that provides enterprise-grade data models in the cloud. Interacting with Azure Analysis Services directly using SDKs and APIs from your custom applications means you can expose your data directly to your audience, wherever they are. This posts will walk you through how to open a connection from C# code.
Create Azure Analysis Service resource
Follow this quick starter to create the server. You can also create a model with sample data (adventure works) right from within your Analysis Server. Click 'Manage' in the blade and click 'New Model'. Select 'Sample data' from the drop down and press 'Add'. It should add the model for you.
Also assign your account as Service Admin in order to connect from SSMS.
And copy the connection string directly from Azure Portal so we will use it later:
Register an application with the Microsoft identity platform
Azure Analysis Services uses Azure Active Directory (Azure AD) for identity management and user authentication. Service principals are used to perform automate common tasks, in our case, a c# application to query data from Azure Analysis Services. Follow this guide to register an application with Azure AD.
When registration completes, the Azure portal displays the app registration's Overview pane, which includes its client ID and tenant ID.
Create a new application secret
You can add both certificates and client secrets (a string) as credentials to your confidential client app registration. Follow this guide to add a client secret to your registered application. After saving the client secret, the value of the client secret is displayed. Copy this value because you won't be able to retrieve the key later.
Grant permissions to app principal on the model
First, connect to Analysis Service using SSMS V17.1 and higher.
Then, select the database model and right click on Roles and add a new Role with read permission.
Now you can add the service principal you created in "Membership" tab.
Enter your principal in the "Manual Entry" box in the following format:
Acquire service principal token
This article show you how to connecting to Azure Analysis Services using service principal. The code below is to acquire a service principal token.
private async Task<string> GetTokenAsync()
{
var authContext = new Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext("https://login.windows.net/" + tenantId);
var credential = new ClientCredential(clientId, clientSecret);
var token = await authContext.AcquireTokenAsync($"https://{aasRegion}.asazure.windows.net", credential);
return token.AccessToken; //Use this token to connect to AAS.
}
Connecting to Azure Analysis Service using service principal token
Microsoft announced the .NET Core support for Azure Analysis Services client libraries in preview (AMO and ADOMD.NET). These packages are still in preview though, below I will use .Net as example.
Once we have the token, we can pass the token in password field in the connection string and leave user id empty.
private string GetConnectionString(string token)
{
return $"Provider=MSOLAP;Data Source=asazure://{aasRegion}.asazure.windows.net/{aasServerName}:rw;Initial Catalog={aasDatabaseName};User ID=;Password={token};Persist Security Info=True;Impersonation Level=Impersonate";
}
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. Initial Catalog is be the name of your model.
The full source code can be downloaded from my Github repository.
