Power BI Embedded and user security (RLS?)

[PaulDBrown]

Hi experts,

I am working on a project which entails embedding the PBI report in web portal. The users access the portal using their own ID and password.

I have been searching, reading, watching videos, going through documentation regarding how this is to be implemented and I am somewhat confused. 

for example: this documentation from Microsoft states the process, but at the end it also states the following:

Token-based Identity limitations

• You can use RLS only if you have a dedicated capacity.
• RLS doesn't work with SQL Server on-premises.

Does this mean that you cannot have secure access if the dataset is on an on premise SQL Server ? (which is actually my scenario). Does this mean we would have to migrate the dataset to cloud services?

Can anyone point me in the direction fo an adequate resource with a step-by-step process of how this is implemented and how the admin for new users/unsubscribed users?

Very much appreciate it!

[MattCalderwood]

Hi @PaulDBrown 

My understanding was that RLS is compatible with on-premises SQL Server: https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls  -  Are you using Direct Query with this DB - or using Power BI import mode?

The statement "You can use RLS only if you have a dedicated capacity." only relates to the section on Token Based Identity - where an Azure AAD token is passed to the Azure SQL DB instead of a more generic identity object. The token contains the details of the user account, which Azure SQL DB can parse/understand.

Passing through a UserName and Roles (as shown here) should work with direct query.

As you are embedding into a web portal, you will need a dedicated capacity regardless of the use of RLS in your data model. This also assumes the 'App owns Data' type of embedding is being used - where your end users do not have a Power BI licence and are logging in via a custom auth process.

Can you elaborate what you mean by 'admin for new users/subscribed users' ?
I have done lots of embedding projects - but not used RLS with Direct Query on an on-premises DB yet - so I am not sure if there is a step-by-step for achieving this.

Hope this helps.

[PaulDBrown]

Thanks, @MattCalderwood , that was indeed helpful. 

Please bear with me since this first PBI Embedding project, so I'm trying to understand the implications...

"As you are embedding into a web portal, you will need a dedicated capacity regardless of the use of RLS in your data model. This also assumes the 'App owns Data' type of embedding is being used - where your end users do not have a Power BI licence and are logging in via a custom auth process".

This is exactly my scenario.

We are aiming for an data import, as opposed to Direct Query.

BTW, the document I was referring to (which I failed to link to) is this:

https://docs.microsoft.com/en-us/power-bi/developer/embedded/embedded-row-level-security 

And from this video (from 2018 so things may well have changed...) I infer that maintenance on users must be done both in the actual PBI report file (RLS structure using dim table with USERNAME) and within the code used in the embedding process (minute 21:30 onwards):

https://channel9.msdn.com/Blogs/Azure/Managing-authentication-and-authorization-for-Power-BI-Embedde...

(If so, then the maintenance sounds pretty complex and not user friendly)

BTW, is the Gateway configuration the same as a regular Power BI report?

As I say, I've been searching around trying to find a simple, comprehensive step-by-step explanation but have not been too succesful.

Any help or reading recommendations are more than welcome, and thank you for your time in helping me understand this topic

[MattCalderwood]

Applying RLS rules by using USERNAME() is achievable in an in memory model, but whether you need to go that far is dependent on the granularity of the security model you are trying to achieve.

When using 'App owns data' - you can specify any Username and combination of Roles at the point of generating an embed token.
If you can configure the required security into a set of roles (Admin/User/Public/Private.... and so on) then your application can be responsible for applying a suitable role when a token is fetched. This way, you won't need to store any usernames or do any lookups.

If your reports are reliant on knowing the exact user (by username) - then you would likely need to create a table within your model that will hold the user details - with an RLS rule defining what a match to USERNAME() will do to restrict/filter the data.

I have worked with both scenarios in the past, so can confirm that both work well.
The difficult part is if your source database does not contain the required user details.

Are you building a multi-tenant model similar to the channel-9 video?

[PaulDBrown]

@MattCalderwood Thanks again for your very helpful input.

I have not yet explored the detail of the dataset (and user details in particular). I have presented a demo based on a subset of data (Excel files, though the real data is stored on SQL server) to showcase the possibilities. 

The users indeed belong to a number of companies (My client's clients for whom the reports are to be set up), so there will be potentially a hierachy consisting of Company/User.

"When using 'App owns data' - you can specify any Username and combination of Roles at the point of generating an embed token.
If you can configure the required security into a set of roles (Admin/User/Public/Private.... and so on) then your application can be responsible for applying a suitable role when a token is fetched. This way, you won't need to store any usernames or do any lookups."

Sorry if this sounds ignorant, but does this imply that the we should specify Username and combination of roles within the web app code? As in hard coded? if so, I guess this means that user maintenance (new users, lost clients/users) will have to be hard coded within the app code itself, correct?

On the other hand, if the model requires creating a table with user details, how does the PBI report know which user is actually logged into the web portal? (from the video I posted, I take it company/users need to be hard coded into the web code and somehow this allows the PBI to be filtered accordingly). I'm trying to undertsand the process and the implications regarding user maintenance (if hard coding needs to happen in the web code, then the maintenance needs to be done by a coder, as opposed to the more flexible maintenance involved in the regular RLS process, correct?)

Thanks again! 

[MattCalderwood]

"I guess this means that user maintenance (new users, lost clients/users) will have to be hard coded within the app code itself, correct?"

If your application sits behind a login, then the username (and possibly roles) should already be known to your web application.
Once a user has logged in, they will need to generate their own unique embedding token before a report can be rendered.
This is done via a REST API call - in which you can choose to specify details for Row Level Security (you dont have to if your model doesn't require it) -> https://docs.microsoft.com/en-us/rest/api/power-bi/embedtoken/reports_generatetokeningroup
So you shouldn't hard code any of these details into your application. They should either be retrieved from the context of the current users session, or fetched from a database before a user goes to generate a token.

"how does the PBI report know which user is actually logged into the web portal?"

PBI takes the details from the token generated via the REST API.

{
  "accessLevel": "View",
  "identities": [
    {
      "username": "UserId12345",
      "roles": [
        "DefaultRole"
      ],
      "datasets": [
        "abcd-1234-abcd-1234"
      ]
    }
  ]
}

You need to define at least 1 role for RLS to work. That single role "could" be hardcoded -> so every user who accesses your app is given a single default role. Then in your data model, your RLS role can apply filtering based on the USERNAME DAX measure.
Which will resolve to the username passed by your app into the embed token.


So in this RLS rule -> the 'DefaultRole' will filter the 'UserClientSecurity' table by matching the values in [UserId] with our UserName (UserId12345) as defined in our token request above.

So if all you need is to pass through a username -> then your web application should be able to supply that at runtime, without hardcoding any of these user details. It is then up to your data model to filter out the relevant data.

Hope this makes sense and I am not just repeating concepts that you already understand.