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
|None||Cant modify the model in SSMS. Cant query data.|
|Read||Cant 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.|
|Administrator||can 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?