…brew your own Business Intelligence

Managing User Access to Azure Analysis Services Databases

In order to access a tabular model, users must either be a member of the Analysis Services instance administrators group or granted access via a database role. For on-premise SSAS instances, this meant adding the windows user account (e.g. Domain\User) to the SSAS database project via SSDT during development (or after deployment via SSMS). However, for Azure Analysis Services, things are a bit different… mainly because of the integration point with Azure Active Directory (i.e. users must be in your Azure Active Directory and the use of organizational email addresses or UPNs).

Note: more info on managing users for Azure AS can be found here

Post-Deployment

In many scenarios, there’s a division in responsibilities between those who develop the models and those who administer the (production) servers (i.e. deployment, security access, etc). In these scenarios, it’s typically better to manage user-access outside of the SSAS database project – which for Azure AS means using PowerShell/TMSL (Tabular Model Scripting Language) to control access after the model has been deployed to the Azure AS instance.

Here’s a (very) basic example of a TMSL command to add a user (azure ad account) to a role called “ReadOnly” with read only access to the [Test] database on one of my Azure AS development instances:

{
  "createOrReplace": {
    "object": {
      "database": "test",
      "role": "ReadOnly"
    },
    "role": {
      "name": "ReadOnly",
      "description": "users to query the model",
      "modelPermission": "read",
      "members": [
        {
          "memberName": "anton@opifexsolutions.com",
          "identityProvider": "AzureAD"
        }
      ]
    }
  }
}

image

 

Pre-Deployment

In development/test scenarios, where it’s much more common for developers to also be responsible for administration, it’s more common to manage user access via roles during development time with in the SSDT project. However, when working w/ Azure AS, you will want to be using the latest version of SSDT (currently 17.0 RC2).

Note: for those who are hesitant to use a version of SSDT that’s “not recommended for production use”, please keep in mind that Azure AS is not recommended for production use yet either as it’s still in Preview mode!

For Azure AS development, there’s a new button on the Role Manager window for adding Azure AD users (i.e. organization email addresses):

image1

It’s important to note that the email addresses you enter at this point are not validated until the model is deployed.

In the screenshot below, you can see where I’ve added 2 additional accounts (neither of which is valid):

image2

When attempting to publish this database to Azure AS, I get the following error:

image3

After removing the bogus accounts, I’m able to successfully deploy:

image4

2 thoughts on “Managing User Access to Azure Analysis Services Databases

  1. Mike D says:

    Hello, Have you tried adding actual users through this method? I am able to add myself, but get an error trying to add any other user, even though they do exist in Azure AD. The error I am getting is “Failed to save modifications to the server. Error returned: ‘The identity ‘xxxx@abc.com’ was not found in Azure Active Directory. Details: Authentication failed.”

    1. Bill says:

      Hi Mike – yes, I’ve added users w/ the script

      Are the account(s) you tried adding associated w/ a different tenant? i do believe there is some additional setup required for that scenario – and I haven’t tested

Leave a Reply