I am trying to connect to Azure Analysis services using ADOMD and authenticated using a Service principal. So I have done following:
- Create app in AAD.
- Granted the app (Service principal) read permission on the Azure Analysis service.
Below is my code to connect to Azure Analysis service.
var clientId = "******";
var clientSecret = "*****";
var domain = "****.onmicrosoft.com";
var ssasUrl = "northeurope.asazure.windows.net";
var token = await TokenHelper.GetAppOnlyAccessToken(domain, $"https://{ssasUrl}", clientId, clientSecret);
var connectionString = $"Provider=MSOLAP;Data Source=asazure://{ssasUrl}/{modelname};Initial Catalog= adventureworks;User ID=;Password={token};Persist Security Info=True;Impersonation Level=Impersonate";
var ssasConnection = new AdomdConnection(connectionString);
ssasConnection.Open();
var query = @"Evaluate TOPN(10,Customer,Customer[Customer Id],1)";
var cmd = new AdomdCommand(query)
{
Connection = ssasConnection
};
using (var reader = cmd.ExecuteXmlReader())
{
string value = reader.ReadOuterXml();
Console.WriteLine(value);
}
I am able to get a valid access token, but I get following error when trying to open the connection:
AdomdErrorResponseException: Either the user, 'app:xxxxxxx@xxxxxx', does not have access to the 'adventureworks' database, or the database does not exist.
Additional info: :
- I have verified that permissions (Reader & also tried with contribute) are given to Service principal to Azure analysis Service thru the Azure portal.
- I have tried same code with service account (username & password) and it works.
- If I remove "Initial Catalog= adventureworks" from the connection string then my connection will succeed. But I do not see why Analysis services permission is not propagated to the model.
Resolution:
Silly that I got the resolution by myself just after posting this. The point no 3 above gave me a clue.Granting permission on the Azure analysis services through the portal does not propagate to the model for the Service principals (Azuire AD apps).
Steps:
- Open the Azure analysis service in Sql server Mgmt Studio.
- In the target model, go to Roles.
Add the service principal into required role with permission. Service principal is added in below format:
app:[appid]@[tenantid]
example : app:8249E22B-CFF9-440C-AF27-60064A5743CE@86F119BE-D703-49E2-8B5F-72392615BB97
database roles. If it needs access other Azure resources, it needsOwnerorContributorrole. – Shui shengbao