PowerBi Embedded API Works with RLS!

[rossnruthie]

Hey everyone, 

One of my frustrations with the new API is that it did not include RLS capabilities that were present in the old 1.x version.  I noticed that the API has been updated to 2.0.2 and is now working with RLS within powerBI models (only against reports currently).

API Generate Embed Token

Power BI Article Explaining RLS and Embedded in the New API

Before making the code change you first must create a role in your PowerBI report.  More than likely if you already have an embedded solution and found that RLS wasn't possible, you instead created a filter within the JavaScript and applied that to the report instead.  Whatever table you applied the filter in that scenario will probably be the table used in your role(s).  For me it was my Seller Table.  My report had a seller table (with CompanyId) that was related to a transaction table.  The goal is to only display transaction data applicable to the specific company viewing the report.

To do this I create a new role which I call "CompanyId" that will filter the seller table and in turn, the transaction table.  To create a new role:

1. Click on the Modeling tab
2. Select Manage Roles
3. Create a New Role (This will be the name of the role you pass in to the API)
4. Select the dimension table that contains the members that will filter your fact data
5. create the dax expression [YourColumnNametoFilter] = USERNAME().  In my case, CompanyId

That's it for the report.  Unfortunately for me, it's impossible to test this because my username (or anyones username) doesn't equate to a companyID, It's something that is resolved in our web app and can be used at that point and passed into the API.  I suppose if you wanted to test you could hard code a value that exists in your dim table instead of USERNAME().  Once I made this change I deployed the report

Now that the report is ready I had to make 1 small change in the code to enable RLS functionality.  First, make sure you have updated your API library to 2.0.2.  The change happens where you create the parameter list to generate an embed token.

The previous code:

// Generate Embed Token.
var generateTokenRequestParameters = new GenerateTokenRequest(accessLevel: "view");

The new Code:

If you remember, i created a role in my report called CompanyId and this is the literal value that I pass as the role name "CompanyId".  I also pass a value of the companyId and as an example here "412125".  I also pass in the datasetId that I want to filter.

// Generate Embed Token.
var generateTokenRequestParameters = new GenerateTokenRequest(
     accessLevel: "view", 
     allowSaveAs: null, 
     identities: new List<EffectiveIdentity> { new EffectiveIdentity(username: "412125", roles: new List<string> { "CompanyId" }, 
     datasets: new List<string> { "DataSetId" }) });

And the results.

When running the code without RLS I get a table of the following values.  You can see company 412125 along with all other companies (Not Good!).

After creating the role and and passing in the companyId as the user in the code:

Hope this helps!

[Shabyman2]

Hey all,

Need some help with this.

We have a pro licence, we have both Premium and a specific Embedded Capacity and have used the example shown to try and provide via our own portal reports to our suppliers. We have a pro account for the API call, which has admin access, all the tokens are coming back. We want to essentially say supplier A has logged onto the portal, they click on the report section, the API the uses the default user login, within the call it says Supplier ID = XXXX pull back just that supplier code/s and show this data to supplier A. If supplier B logs in they will only then see their associated code YYYY.

I have created the role "supplier", in the DAX i have done: [Supplier Id] = username()

When our platform team tries the API call they get this error:


Is there something i or they are missing, thanks for your help in advance 🙂 

[Shabyman2]

Encase this useful:
Underlying Error: PowerBI service client received error HTTP response. HttpStatus: 503. PowerBIErrorCode: OpenConnectionError
OpenConnectionError: Failed to open the MSOLAP connection.

[pbipbj]

Looks like you're putting the company id value inside the username field in the EffectiveIdentity in the embed token.  This must mean that the username value isn't used for actual authentication/authorization in your scenario.  I've found another reference to this ability here:  https://azure.microsoft.com/en-us/updates/power-bi-embedded-rls-ascii-characater-support/

Is this username flexibility exclusive to the "Power BI Embedded" Azure resource?  I've tried to do the same thing with the Power BI Service (not Power BI Embdedded) and the REST API rejected my request.  I received a 401 Unauthorized response when trying to embed the report.  When I put my master user UPN back in the username field (of the EffectiveIdentity in the embed token) it started working again.  

So, I'm wondering if there is a way to do the same thing (put an arbitrary value in the username field) without using Power BI Embedded.  CUSTOMDATA() doesn't work for me because I'm not using Azure Analysis Services.

Thanks for any info or guidance.

[pbipbj]

FYI.  I was able to figure out my issue.  Previously, the report was shared with the master user account.  This situation required the EffectiveIdentity in the embed token to have the master user username.  We couldn't use it for dynamic RLS.  Then we moved the report to a workspace for which the master user is an admin.  After that, the username could be used for dynamic RLS (i.e., username could contain a value other than the master user username).  So that was the difference.  Shared reports couldn't use dynamic RLS.

[shaunwilks]

We have had similar issues in the past using RLS within the REST API.

We are using the API in a "User Owns Data" model.  When we open the report we want that report to auto filter using the RLS identifier that we pass to the openinfg the report via the API.

It would seem this is what you are saying you have achieved. In my testing I am the only user account involved. Report is published to my Workspace - so I am an admin.

Could you possibly paste in some code extracts within your API of your call to the report and how youy past the RLS name ? Thanks in advance

[pbipbj]

This is the approach we used, which is taken from Microsoft's code in their embed tool (https://app.powerbi.com/embedsetup/appownsdata).  If you're in the User Owns Data scenario, there is an embed tool for that scenario too:  https://app.powerbi.com/embedsetup/userownsdata

The tool will send you a Visual Studio solution that demonstrates the use of their API.

If this still doesn't work for you, try publishing the report to a workspace other than "My Workspace" (one for which you're still an admin).  

            try
            {
                using (var client = new PowerBIClient(new Uri(API_URL), tokenCredentials))
                {
                    PBI.Report report = await client.Reports.GetReportInGroupAsync(workspaceId, reportId);
                    if (report == null)
                    {
                        return null;
                    }

                    var datasets = await client.Datasets.GetDatasetByIdInGroupAsync(workspaceId, report.DatasetId);

                    GenerateTokenRequest generateTokenRequestParameters;

                    var username = await GetUsernameValue();
                    if (!string.IsNullOrWhiteSpace(username))
                    {
                        string roles = "RoleA,RoleB";
                        var rls = new EffectiveIdentity(username, new List { report.DatasetId });
                        if (!string.IsNullOrWhiteSpace(roles))
                        {
                            var rolesList = new List();
                            rolesList.AddRange(roles.Split(','));
                            rls.Roles = rolesList;
                        }
                        // Generate Embed Token with effective identities.
                        generateTokenRequestParameters = new GenerateTokenRequest(accessLevel: "view", identities: new List { rls });
                    }
                    else
                    {
                        // Generate Embed Token for reports without effective identities.
                        generateTokenRequestParameters = new GenerateTokenRequest(accessLevel: "view");
                    }

                    var tokenResponse = await client.Reports.GenerateTokenInGroupAsync(workspaceId, report.Id, generateTokenRequestParameters);
                    if (tokenResponse == null)
                    {
                        return null;
                    }

                    return new EmbedConfig()
                    {
                        EmbedToken = tokenResponse,
                        EmbedUrl = report.EmbedUrl,
                        ReportId = report.Id
                    };
                }
            }
            catch (HttpOperationException ex)
            {
                return null;
            }

[toy]

invaluable example - thank you

[Anmolgan]

How to use comanyid in place of username in this scenerio?????

[mohittimpus]

Below my code for AppownData for PowerBI Embedded- 

generateTokenRequestParameters = new GenerateTokenRequest("View", null, identities: new List<EffectiveIdentity> { new EffectiveIdentity(username: "username", roles: new List<string> { "Manager", "Manager1" }, datasets: new List<string> { "bd725bc3-09b4-4c21-84b4-727d84a3f131" }) });

when i pass username for eg: my email & role as Manager then it show result for both Manager & Manager1.

but what i want it should show Manager role for one email, Manager1 for another email?

Its showing proper in Power Bi service but not when run this application.

Can you pls help???

[mohittimpus]

What should i do if i want to pass different username with different role?below my code

 generateTokenRequestParameters = new GenerateTokenRequest("View", null, identities: new List<EffectiveIdentity> { new EffectiveIdentity(username: "username", roles: new List<string> { "Manager", "Manager1" }, datasets: new List<string> { "bd725bc3-09b4-4c21-84b4-727d84a3f131" }) });

1) For user name i want to add two emails id: 

2) And different role for each email id.

when i pass username, it show result for both, Can you pls help??? 

[Anonymous]

Very awesome, you filled in some blanks I needed to make this work.

They really need to update their documentation to include this exact information.

Thanks!

[Eric_Zhang]

Thanks for your sharing. 🙂

[rossnruthie]

You're welcome.  It's good to see that this feature is now supported as it provides us the security we needed to our customer facing products.

kudos to the devs for making it happen so quickly.