Azure Analysis Services Security

I have recently been setting up training sessions for Analysis Services and Azure Security has been one of the biggest learning curves.

Lets have a look at what we needed to do with our Adventureworks demo and all the areas of Possibility

Azure Analysis Service Access Control (AIM)

I have created an analysis Service in Azure which makes me the Administrator. I can do everything, including add models that I create in Visual Studio 2019.

However I will be working with lots of other people. In the first instance they will be using the models I have set up to create reports in Power BI

This is only a training demo and is not how I would set up a fully functioning Live Analysis Service model

in Azure we can add role assignments (Click on Access Control (IAM) but what does this mean in terms of actually using the service?

I am an owner. Azure has a number of built in roles role-based access control (RBAC) to use

  • Owner – Has full access to all resources including the right to delegate access to others.
  • Contributor – Can create and manage all types of Azure resources but can’t grant access to others.
  • Reader – Can view existing Azure resources.
  • User Access Administrator – Lets you manage user access to Azure resources.

The model above shows how you can use RBAC to segregate duties.

Using the following information, I set my users up as contributors in the hope they would be able to access the models

With Contributor in place the users could see the Analysis Service in Power BI but didn’t have access to the model

As a test the users were bumped up to Owner and still couldn’t see the models in Power BI

Azure AIM is in relation to the Analysis Service, Not the models created on the Analysis Service

Visual Studio 2019 (Roles)

The next step is to see what happens when we create a role in Analysis Services model in Visual Studio

In tabular Explorer (Make sure you have downloaded the Analysis Services extension for Visual studio) click on Roles. I have created a training role for this demo

I gave all the Users the permission of Read and Process

PermissionsDescription
NoneCant modify the model in SSMS. Cant query data.
ReadCant modify the model in SSMS. Can query data.
Read and Process Can query data. Can run process operations by running a script or package Cant make any changes to the database. Cant view the model in SSMS
Process Cant query data. Can run process operations by running a script or package Cant make any changes to the database. 
Administratorcan modify the model schema and query data

Once the training role was set , the model was reprocessed

Users now have access to the Analysis Services model and can use the model in Power BI

The question is, can the users access the model if they are in this Role, but they are not in Analysis Services (AIM)

Both users were taken out of AIM in Azure and still had access to use the models

New requirement for users to add new models to the Analysis services Database

the next stage was to allow the users to create their own adventureworks models in the Analysis Service.

With all the above security in place, when a user Processed their tabular model, the following error occurred

This user has owner Access in Azure IAM and Read and Process Access on the Analysis Services model

How do we allow users to actual create and modify models?

SSMS – Roles

SQL Server Management Studio is the place to grant model permissions

Open up SSMS as Administrator by Right Clicking and Run as Administrator.

Note that you can see the Training role set up in visual Studio. originally, Full control was unticked.

I ticked Full Control which is immediately set on the model. No need to process anything.

The user tried again and still couldn’t add the new model to the Analysis Service

Only server administrators, or database administrators having Full Control permissions, can deploy a cube from source files to a server, or create roles and assign members.

It would appear that Full Control (Administrator) is not the same as server administrator because you cant deploy a tabular model

Question, If I Re-process the visual Studio model with permission set as read and Process, will this override the new selection in SSMS?

Yes. If you reprocess your tabular model (Because you have updated the model, Added new measures etc) , Role permissions from here will override the Role permissions set in SSMS. Ensure that any Roles changed in SSMS are reflected in Visual Studio

SSMS – Analysis Services Properties

right click on the Analysis Service model (Under Connect) and Choose Properties

then select Security

Note that this is where Server Administrators are assigned and only these people can create and deploy their own models to the analysis Service

Going back to Azure, you can also set up Admins in here

Note that everything we just did in SSMS is available in Azure. Also, if we redeploy the Analysis Services Tabular model via Visual studio, it doesn’t do anything to Analysis Services admins

Our roles aren’t available to view in Azure . Just Analysis Services Admins

Everything is now working

There is still questions to be answered. Feel free to let me know your thoughts

  • When would you need to add users to Azure IAM if you can use the tabular models and also deploy models by adding users to Roles and the server administrator role in visual Studio and SSMS?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s