Search code examples
azureazure-active-directoryadomd.netazure-analysis-services

Azure analysis service connection using Service principal not working

I am trying to connect to Azure Analysis services using ADOMD and authenticated using a Service principal. So I have done following:

  1. Create app in AAD.
  2. 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: :

  1. I have verified that permissions (Reader & also tried with contribute) are given to Service principal to Azure analysis Service thru the Azure portal.
  2. I have tried same code with service account (username & password) and it works.
  3. 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:

  1. Open the Azure analysis service in Sql server Mgmt Studio.
  2. In the target model, go to Roles.

  3. 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

Solution

  • 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 (Azure AD apps).

    Steps:

    1. Open the Azure analysis service in Sql server Mgmt Studio.
    2. In the target model, go to Roles.

    3. 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

    I have blogged my whole experience here:https://unnieayilliath.com/2017/11/12/connecting-to-azure-analysis-services-using-adomd/