Is it possible to export Paginated Report from Logic App enforcing Row Level Security

[bhavya11]

Hello,

I am trying to export a Paginated Report (Power BI) from Azure Logic App. I am using the Export To File for Paginated Reports Logic App Action for this. I am able to export the report without enforcing RLS. But I want to export the report by enforcing RLS.

Export To File for Paginated Reports supports Identity.

I want to achieve something similar to what has been described here: https://learn.microsoft.com/en-us/power-bi/developer/embedded/paginated-reports-row-level-security#u...

So, I create an access token using the Generate Token REST API endpoint passing my desired filter in the username. I get token in the response. I pass the same token to the Identity.username in the Export To File for Paginated Reports Action. But it gives an error "User not found in the organization".

Identity.Username documentation states: The effective username within a token that applies row-level security rules.

Thank you

[v-saisrao-msft]

Hi @bhavya11 ,

Could you please confirm if your query have been resolved? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you.

[v-saisrao-msft]

Hi @bhavya11 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[bhavya11 · Accepted solution]

Hello Sai,

Apologies for the delayed response. The above solution did not work. The following combination is not supported by Power BI:

Exporting Paginated Report with PBI Semantic Model enforcing RLS.

To implement RLS, we need to pass custom string in Effective Identity's username key. E.g "state" (If I want to restrict data only to particular state for the logged in person).

I get an error "User was not found in organisation.

Basically, the REST API endpoint goes to check for that user in the Azure AD.

But I have implemented RLS in an alternate way.

Thank you.

[v-saisrao-msft]

Hi @bhavya11,

We greatly appreciate your efforts and the update.Is the issue been resolved from your end. If you have any further concerns, please feel free to reach out to us.

If this helps then please Accept it as a solution and dropping a "Kudos" so other members can find it more easily.

Hope this works for you!

Thanks.

[bhavya11]

As mentioned in my previous post, there exporting paginated report with RLS using Effective Identity with custom string in username is not supported. So, I implemented a workaround by passing the required RLS parameters in the parameter-value collection in Paginated report.' I added report parameter and query parameter for each column that I use for RLS. Then I added filters on each of those columns in the DAX Query.

Thank you

Thank you.

[v-saisrao-msft]

Hi @bhavya11,

Thank you for sharing your findings. I appreciate the effort you put into troubleshooting and updating the community with your workaround. It's excellent to hear that you successfully implemented Row-Level Security (RLS) by passing the required RLS parameters in the parameter-value collection of the Paginated Report. 

Your approach of: 

1. Adding report parameters and query parameters for each RLS column, 
2. Applying filters in the DAX query, 

 This insight is highly valuable for others encountering similar challenges, and your detailed explanation will certainly benefit the community.

If you find this post helpful, kindly mark it as the solution and give it a kudo. This will assist other community members in easily locating useful information. 

Thank you. 

[v-saisrao-msft]

hi @bhavya11 
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @bhavya11 

Thank you for reaching out. That you want to export a Paginated Report from Azure Logic App while enforcing Row Level Security (RLS) using a custom value as a filter. Here are the detailed steps to achieve this: 

• Instead of using identity.username to enforce RLS with a user in Azure Active Directory (AAD), you can use a custom value to filter your data. For example, if you want to filter products that are green in color, you can set the identity.username to "green" and configure your report to apply this filter accordingly. 

• First, set your Paginated Report to take the custom value through the identity.username parameter. You would do this by setting up a parameter within your report in which the custom value is passed into it for filtering out data. For example, you can set up such a parameter as Color for your report and will pass its value to the identity.username parameter. 

• Next, apply the custom value filter by using an expression-based filter placed in the Filter section of your report's Tablix Properties. Open your Paginated Report in Power BI Report Builder and select the Tablix (table or matrix) that you want to filter. Right-click on the Tablix and select "Tablix Properties." Then go to the "Filters" tab and click on the "Add" button to add a new filter. In the "Expression" field, select the column you want to filter on (e.g., Color). Then from the "Operator" field, select the appropriate operator (e.g., "="). From the "Value" field, enter the expression that references the parameter you created (e.g., =Parameters!Color.Value). 

• Finally, when generating the token using the Generate Token REST API endpoint, the generated token must contain the custom value for the identity.username parameter. This will allow the report to apply the filter based on the custom value. Pass the generated token to the identity.username parameter in the Export to File for Paginated Reports action. 

I hope this helps! If you have any more questions or need further assistance, feel free to let me know.

If this post clears your doubt, please give us Kudos and consider marking Accepting it as a solution to guide other members in finding it more easily.

Thank you. 

[bhavya11]

Hello,

I tried the above, but I get 403 error.

I authenticate using Service Principal and then pass the oAuth token as bearer token to get the Embed Token for the PBI.

While calling the GetToken PBI Rest API, I pass the required filters as username in the payload along with other required fields:

POST https://api.powerbi.com/v1.0/myorg/GenerateToken

{
  "datasets": [
    {
      "id": "{dataset-guid}",
      "xmlaPermissions": "ReadOnly"
    }
  ],
  "reports": [
    {
      "id": "{report-guid}"
    }
  ],
  "identities": [
    {
      "username": "84170958",
      "datasets": [
        "{dataset-guid}"
      ]
    },
    {
      "username": "84170958",
      "reports": [
        "report-guid}"
      ]
    }
  ]
}

I get 403 in response.

Thank you

[v-saisrao-msft]

Hi @bhavya11 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @bhavya11 

Based on our previous conversation, if the solution I provided works for you, please don't hesitate to reach out if you have any further questions. If my response has resolved your query, kindly accept it as a solution and give it a 'Kudos' to help other members find it easily.

Thankyou.

[v-saisrao-msft]

Hi @bhavya11 
It looks like you're encountering a 403 error when calling the GetToken PBI Rest API. This error typically indicates an authorization issue. Here are some steps and considerations that might help you resolve this issue:

• First ensure that the Service Principal has the necessary permissions to access the Power BI resources. You need to grant the required permissions to the Azure AD app associated with the Service Principal. This includes Power BI service delegated permissions. Also verify that you are using the correct OAuth 2.0 endpoint for token generation. Sometimes, using the OAuth 2.0 V1 endpoint can cause issues. Try using the OAuth 2.0 V2 endpoint instead.
• Double-check the structure of the payload you are sending to the GetToken API. Ensure that all required fields are correctly specified and that there are no typos or missing fields and make sure you are calling the correct API endpoint and that the scope of the token includes the necessary permissions for the Power BI REST API.

Thank you.

[v-saisrao-msft]

Hi @bhavya11 

Thanks for posting in Microsoft Forum community.  

Regarding your query, you can indeed export a Paginated Report from Logic App while enforcing Row Level Security. Here are some steps and considerations that could assist you in addressing this issue: 

1. Make sure that the username you're passing in the token is in the correct format and exists in your organization. The username should match the format expected by your Azure Active Directory (AAD) and also verify that the user has the necessary permissions to access the report and the data. The user should have appropriate roles assigned in both Power BI and AAD. 
2. Double-check the process of generating the token. Ensure that the token includes the necessary claims and is generated correctly using the Generate Token REST API endpoint Also, make sure that the token is valid and not expired.  
3. And ensure that the Identity.username parameter is correctly set in the Export to File for Paginated Reports action. The parameter should be set to the effective username within the token that applies the RLS rules. 

 If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS. 
Thank You. 

[bhavya11]

Hello,

I do not want to set identity.username to a user in the AAD. I want to set it to a custom value that I want to use as a filter. 

E.g. I have Product table with a Color column. I want to filter all products that are green in color. So, I will be setting the username to green and it should filter the result accordingly.

Thank you

[lbendlin]

Hiding pages is purely cosmetic.  There is absolutely no security behind that.