Azure Active Directory, B2C and Rights

Azure Identity Management is a fairly large body on knowledge.  Basically, dividing it into different areas makes if easier to understand.

RBAC in Azure:
Azure AD and B2C bother offer a way to authenticate a user thru the user providing an identity.
The user is assigned to 1 or more groups, and then the groups (or individual users) are assigned to Roles.  The diagram below shows internal and external users and how permissions can be given out.  Resulting in Role Based Access Control (RBAC).  The application itself deals with the operations a user can perform but having the users role/claims allows the individual applications to figure out what action the user can perform.

RBAC can be assigned at 1 of these 4 levels to manage Azure Resources:

Tip: For small Azure Tenants, managing resources are the resource level works well, but in most enterprises, you should mange at the Resource group or even subscription level to keep management controllable.

Note: There is the concept of "Directory", multiple "Resource Groups" are setup to a directory.  I believe all companies should have a single directory but it is more common to find even relatively small businesses common to have multiple directories. 

"Multiple subscriptions can trust the same Azure AD directory. Each subscription can only trust a single directory." Microsoft Docs

Adding users to all new SQL database using Azure AD groups

Problem:  I have a dedicated SQL 2017 VM on Azure that is joined to my Azure AD tenant e.g. int.contoso.com (Azure AD Domain Service).  I need a set of users to have read and write access to all databases that get provisioned on the SQL 2017 instance.

Initial Hypothesis: 
Create an Azure AD security group and add all the AAD users and
Add the AAD group to the Model database with the permissions that all new database should have.

Resolution:
1. Using Azure AD create a new security group, I called my group developers and add the users as members Fig 1.& Fig2.

Fig 1. Azure Portal, go to Azure AD and Groups

Fig2. Add the security group

2. Add the AAD Group e.g. int.contoso.com\Developers to the System "Model Database", I have given the group read and write access below in Fig 3.

Fig3. Add permissions to the Model DB

3. Create a new database and validate that the new permissions are added to the new database as shown in fig4.

Fig4.

Note: Changing exsiting DB permissions
To add permissions to existing database, an option is to run
EXEC sp_MSForEachDB 'exec sp_addrolemember ''db_datareader'',''INT\paul.beck'''
T-SQL to list of Daatbase: EXEC sp_MSforeachdb 'USE ? SELECT SF.Name FROM sys.databases SF'

PowerBI Pro for Licencing & Understanding the two query options

Problem: Require reporting and dashboards quickly and securely at a reasonable price.
Hypothesis: There are great reporting solutions, and two other enterprise-leading products are Tableau and Qlik. However, these can be expensive to install and pay for licencing.

Proposed Resolution: PowerBI Pro (PowerBI Premium is for larger enterprise solutions) is a cloud-based solution that can connect to multiple data sources and on-prem using the Gateway. E5 licences include the PowerBI Pro licence for creating and publishing reports. An E3 licence can get an add-on for about £7.50 per month. This is only needed by the people creating the reports.
 
To paraphrase "You can embed the report (not dashboard) on a SharePoint web page and share it with company users. You will then only be needing one licence to publish the report. The downside of this option is there is no builtin security for the report. Anyone who has access to the web page can access the report."

Disclaimer: These are my thoughts and understanding, please check your licencing with Microsoft and a licencing professional.

Updated: Nov 2022

3 Power BI licence types:

1. Free (per user)
2. Pro (per user per month) $
3. Premium (resource/capacity based)  $$$ - 2 options

Choose depending on your usage pattern/scenarios. Premium is great for enterprise-level but out of the range for most SME businesses.  Feature and pricing comparison

Creating Reports

When you create a Power BI report, a semantic model is created within the Power BI Service.  This shows the data you can access, and using DAX queries binds the report to the underlying queried data.

There are two ways to access the data:

1. Import Mode (Data is periodically brought into the Power BI Dataset.  Reporting is fast, but there is a time lag until the data pull is refreshed); or 

2. Direct Mode (queries the underlying system, so data is up to date, but it's slow and can hammer the source systems).

Options for Querying Data from Power BI (Pre MS Fabric)

Last updated: Jan 2025