Connecting to Azure Analysis Services using Service principal or User token
There are some challenges in connecting to Azure Analysis Services (AAS) using service principal or user token from Web Api or any C# code. This article will help you to overcome those problems and connect to AAS. The first section has details about how to connect to AAS using service principal token. The second section covers details about connecting to AAS using user token. You can directly go to section two if you are just looking to connect Azure Analysis service using AAD user token.
- Connecting to Azure Analysis Service (AAS) using service principal token.
You can connect to AAS using Azure AD service principal instead of SQL Server user name and password. You need to create Azure AD app and you can use it to connect to AAS. Refer Active Directory integration applications article for creating AD application. After creating AD app get token as shown in below code.
var resourceID = "https://aspaaseastus2.asazure.windows.net";
The resource id is your Azure Analysis Server region end point. If it is hosted in west central us the regional end point may be https://westcentralus.asazure.windows.net. you can find your AAS server name in Azure portal.
var authContext = new AuthenticationContext("https://login.windows.net/" + tenantID);
ClientCredential cc = new ClientCredential(clientId, appKey);
AuthenticationResult token = authContext.AcquireTokenAsync("resourceID, cc).Result;
return token.AccessToken //Use this token to connect to AAS.
Now you can connect to AAS using connection string as shown below.
Provider=MSOLAP;Data Source =<<AAS server name>> ;Initial Catalog=PBITabular;Password =<<service principal token>> ;Persist Security Info=True;Impersonation Level=Impersonate
AAS Server Name example: asazure://westcentralus.asazure.windows.net/blogdemotabular. You can find your AAS server name in azure portal.
Note: User parameter is not required in connection string. The password parameter token will have complete details about service principal.
The Azure AD app need access to AAS server. You can grant permissions by adding service principal in the format app:objid@tenantid to AAS. The object id is Azure AD app object id. You can find both object id and tenant Id in azure portal. The Directory ID property of active directory is tenant id. At the time of writing this article the format of service principal identity that AAS can recognize is app:objectid@tenanatid. It will change to app:clientid@tenantid format in future updates to AAS. I will update here once the format changes to app:clientid@tenantid. To be safe side you can add both formats. Refer below screen to add service principal to AAS.
Now you should able to query data using app token based on the permissions granted to service principal.
- Connecting Azure Analysis Service (AAS) using user token.
You can use user token to access AAS only if user accepts consent. You should set AD app required permissions to "Azure Analysis Service" to get user consent. But the challenge is at the time of writing this article, azure analysis service is not found in azure active directory when you search to add it as a required permission. AAS should be available like Graph, office 365, Power BI etc. The other way to add required permissions is by editing Azure AD app manifest. Edit Azure Ad app manifest and add below entry to "requiredResourceAccess" array. Ensure to save changes and verify required permissions.
{
"resourceAppId": "4ac7d521-0382-477b-b0f8-7e1d95f85ca2",
"resourceAccess": [
{
"id": "59ef67ea-d35f-4c6d-b1ce-95468f134ccb",
"type": "Scope"
}
]
},
User need to delete prior consent by going to https://aka.ms/myapps and accept new consent after updating manifest. That allows you to use his token for connecting to AAS.
You need to pass AAS server region end point as resource id to acquire user token. So that the token issued by AAD will be valid for connecting to AAS. See below sample code to request token for accessing AAS.
private static string ssasResourceId = "https://westcentralus.asazure.windows.net";
.
.
.
AuthorizationCodeReceived = (context) =>
{
var code = context.Code;
ClientCredential credential = new ClientCredential(clientId, appKey);
string signedInUserID = context.AuthenticationTicket.Identity.FindFirst(ClaimTypes.NameIdentifier).Value;
AuthenticationContext authContext = new AuthenticationContext(authority, TokenCache.DefaultShared);
AuthenticationResult result = authContext.AcquireTokenByAuthorizationCode(code, new Uri(HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Path)), credential, ssasResourceId);
return Task.FromResult(0);
}
Now you can connect to AAS using connection string as below.
Provider=MSOLAP;Data Source =<<AAS server name>> ;Initial Catalog=PBITabular;Password =<<user token>> ;Persist Security Info=True;Impersonation Level=Impersonate
AAS Server Name example: asazure://westcentralus.asazure.windows.net/blogdemotabular. You can find your AAS server name in azure portal.
If user has enough permissions to access AAS then he will get data. This implementation will ensure that only user who have access to data can access it from your application.
Leave comment if you have any further questions or need complete working code.