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?

Adding a Logic App to Refresh your Analysis Services Data

You have a shiny new Analysis Services model containing all the data you need for your Power BI reports and Dashboards

It has been added into an Analysis Service because A There is a lot of data, possibly too much for a Power BI Imports, B You want to do incremental processing without needing Power BI Premium and C You want to refresh more than 8 times a day.

Everything is all set up but the final part of the puzzle is how to schedule the refresh into the Analysis Services Tabular model.

Its easy enough in Power BI, You simply set the schedule in the Service. Lets have a look at how to do this using one of the Options. Logic Apps

Create a Service Principal (SPN)

First we need to Create a new Azure Active Directory Application and Service Principal to use with Role based Access Control. Whenever you have code that needs to access and or modify resources (The logic App will Refresh the Analysis Services data) You need to create an Identity for the App.

Sign into Azure

Go to Azure Active Directory

Next go to App Registrations and + New registration

The web URL is simply one set like the example in the documentation

Then click Register

Assign the Application to a role

Before you assign resources,, the app must be assigned a role. For this example we are going to assign a role at the subscription scope for our Proof of Concept Subscription

Go to All Services and then Subscriptions

Im going to select the Proof of Concept Subscription

Next select Access Control and add Role assignment

Note I have selected the new analysisservicesapp

Click save to add the Role Assignment

The Service Principal is set up. Now we need to get the values so we can sign into the app

Get Sign in Values

Go to Azure Active Directory

Go to App Registrations again and select the new application

Copy the Tenant ID for later use

Copy the Application ID for later use

Set up your Credentials

I was going to create a certificate but I found it an extremely complicated process and I couldn’t in the end export the created certificate in Powershell

Because I haven’t been able to Create and Export a certificate I am going to use Client Secrets instead

Click on New Client Secret

Once added you need to copy the ID for use later. You wont be able to access this information again so DONT LOSE IT

Give the Service Principal Name Authority to administer the Analysis Services

Still in your App click on API Permissions

Next View API Permissions

+ Add a permission and find Azure Analysis Services in APIs my organisation uses

Ensure that the Permission allows you to read and write all models (Tick the box)

then Add permissions

Note that although permissions have changed we still need admin consent

Grant Admin Consent

Granting admin consent requires you to sign in as global administrator, an application administrator, or a cloud application administrator.

From Azure Active Directory go to App registrations and select the app

Within the App go to App Permissions

grant Admin Consent

Configure Permissions in Azure Analysis Services

Next we need to ensure the new Server principal created as server administrator permissions in Analysis Services

Open SQL Server Management Studio by Right clicking and Run as Administrator, Connect to the Analysis Service

Add in your User name (In my case the email address) but not the Password.

You add your Office 365 password when you sign into your Account (Next)

Right click on the Analysis Services name and go to Properties, Then Security

Click add, then Search for the app that has been created. This can then be added as a server administrator (Allows it to add models, amend models, refresh data etc)

Click OK

Create the Logic App

Finally, we can create the Logic App in Azure

The Logic App will be triggered by a HTTP Request which will be triggered by Data Factory the Azure Orchestration tool

In Azure Go to Logic Apps and Add

Then New Step and search for HTTP and then HTTP

Method

Post. Post appends the form data inside the body of the HTTP request

URI (Unified Resource Indicator)

URI = https://your server region/servers/aas server name/models/your database name/refreshes

Here is my example:

https://ukwest.asazure.windows.net/servers/adventureworksas/models/AdventureWorksV2/refreshes

The Server is the Analysis Services created in Visual Studio.

The Model is the model that we want to refresh. There may be multiple models on the server

Headers

As per the example I have set to Content-Type and application/json

Queries

Nothing set

Body

This is where you set up al the processing information

{

    “Type”: “Full”,

    “CommitMode”: “transactional”,

    “MaxParallelism”: 2,

    “RetryCount”: 2,

    “Objects”: [

        {

            “table”: “DimCustomer”

        },

        {

            “table”: “DimDate”

        },

      {

            “table”: “DimEmployee”

        },

      {

            “table”: “DimProduct”

        },

      {

            “table”: “DimProductCategory”

        },

     {

            “table”: “DimProductSubCategory”

        },

     {

            “table”: “DimSalesTerritory”

        },

     {

            “table”: “FactResellerSales”

        }

    ]

}

Type: Full

the type of Processing to perform. refresh command types can be

  • full – Process the Entire Model and recalculate all the dependents (Measures, columns etc)
  • clearValues – Clear values in the objects and dependents
  • calculate – recalculate your formulas
  • dataOnly – refresh the data in the objects, does not force recalculation
  • automatic – If the object needs refreshing and recalculating then do so.
  • add- Append data to the partition and recalculate dependents (measures, columns etc)
  • defragment – Defragmentation option will clean up values in dictionaries that are no longer used

CommitMode: transactional

Determines if objects are committed in batches of when complete. Models include

  • default
  • transactional
  • partialBatch

Its always a good idea to set up Partitions within your data, especially on the Fact table to process the data

However the above is an example specifiying what to process and when. You can simplify the code if you wish

{

    “CommitMode”: “transactional”,

    “MaxParallelism”: 2,

    “RetryCount”: 2,

    “Type”: “Full”,

}

The following is an example of adding Partitions into your code. In this example, DimCustomer is one Partition.

“table”: “DimCustomer”,

“partition”: “DimCustomer”

The fact table can be made up of many partitions. For example you could have one for each year. Then you can decide which partitions to process. For example, you may only want to process the current years data.

Authentication

Active Directory OAuth

Tenant

Use the Tenant ID that we collected previously

Audience

https:/*asazure.windows.net

Client ID

Use the Client ID that we collected Previously

Credential Type

Secret (Remember that we added the Secret to the App)

Secret

The Secret ID that we saved (Remember, for this one you cant go back to Azure and look at the secret. Its a one time ID Only)

Save and test the Logic App by Clicking Run

Consume Logic App with Azure Data Factory

We now have a logic app that will incrementally process the data. We now need to schedule this by creating a Data Factory or orchestrate the processing

First we need to copy the HTTP POST URL within the logic App When a HTTP request is received activity

And then create a new Data Factory in Azure

for the time being I am not enabling GIT

In author and Monitor create a pipeline and drag a web activity across to the design pane.

And then set up the schedule by adding a trigger

this should now be enough to process all your data into the Analysis Service model which has a line connection into Power BI.

What we need to do is to check that this is doing its job. i will be looking at this in a later blog post

Create your website with WordPress.com
Get started