Datasets - Execute Query endpoint with RLS

[marcio_fornari]

This endpoint: https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries

Can i use the identities to set a role and username? 

[v-kpoloju-msft]

Hi @marcio_fornari,

Thank you for reaching out to the Microsoft fabric community forum regarding the use of the execute Queries endpoint with Row-Level Security (RLS) in Power BI.

Yes, you can use the identities field in the Execute Queries API to apply Row-Level Security (RLS). You can set both the username and roles in your API call to simulate the security context. Make sure the role you're passing matches one defined in the dataset, and that the username aligns with what your DAX security rules expect (USERPRINCIPALNAME() or USERNAME()).

You’re following correct documentation, to implement RLS effectively using the identities field, please refer to the official Microsoft documentation, which provides comprehensive guidance on structuring your request: Execute Queries – Power BI REST API

• Defining RLS Roles: Ensure that RLS roles are properly configured in your Power BI dataset.
• Constructing the identities Array: Your request body should include the identities array with the appropriate username, roles, and datasets fields.
• Authentication: Use a valid Azure AD bearer token with the necessary permissions to access the dataset.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[marcio_fornari]

Hello, this documentation is not clear about Rls identities. Can you send a body example?
I tested this and not filter the rls, return all rows.

{
  "queries": [
    {
      "query": "EVALUATE VALUES(table)"
    }
  ],
 "identities": [
    {
      "username": "xxx@xxx.com",
      "roles": [
        "myrole"
      ],
      "datasets": [
        "xxx-xxx-xxx-xxxx"
      ]
    }
  ]
}

[v-kpoloju-msft]

Hi @marcio_fornari,

Thank you for the follow-up and for sharing the request body you tested. You are correct in using the identities field in the Execute Queries endpoint to simulate RLS. However, RLS filtering only applies under specific conditions. Here are the steps to ensure it is configured correctly.

Requirements for RLS to Work:

1. Premium or Fabric Capacity: The dataset must be in a workspace supported by Premium capacity (P SKU or Embedded A SKU) or Microsoft Fabric capacity.
2. Defined RLS Roles: Ensure that the role name (such as "myrole" in your example) is precisely defined in the Power BI dataset under Manage roles in Power BI Desktop or service.
3. Matching Identity: The username must align with what your DAX RLS expressions evaluate using USERNAME() or USERPRINCIPALNAME(). For instance, if your DAX rule filters based on email, the username must be the user's email.

Example Request Body (Correct Format):

{

  "queries": [

    {

      "query": "EVALUATE VALUES('YourTableName')"

    }

  ],

  "identities": [

    {

      "username": "user@yourdomain.com",

      "roles": [

        "SalesRegionRole"

      ],

      "datasets": [

        "your-dataset-guid"

      ]

    }

  ]

}

Required Headers: Ensure you are using an Azure AD bearer token with permissions to read from the dataset. The request must include:

Authorization: Bearer <your-access-token>

Content-Type: application/json

If you are still retrieving all rows, it typically indicates one of the following issues: The RLS role name does not match exactly. The dataset is not located in a Premium/Fabric workspace. Your dataset does not utilize USERNAME() or USERPRINCIPALNAME() in the DAX security rule. The token does not impersonate the user (though the identities field should simulate this if Premium capacity is enabled).

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @marcio_fornari,

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.

[marcio_fornari]

Hi, sorry for a long time to answer. 
not work for me 😞 

still return all data.

[v-kpoloju-msft]

Hi @marcio_fornari,

Apologize for the delayed reply. Thank you for your patience and the update. I have identified few workarounds that may help resolve the issue.

Premium/Fabric Capacity: Ensure the dataset is hosted in a workspace with Premium capacity (P or A SKU) or Microsoft Fabric capacity. Note that RLS simulation via the identities field is not supported in Pro-only workspaces.

RLS Roles Are Properly Defined: Confirm that the role (e.g., "SalesRegionRole") is created in Power BI Desktop under Modelling > Manage Roles and published to the service.

DAX Expressions Use USERNAME() or USERPRINCIPALNAME(): Your DAX filters within the role should utilize these functions to evaluate the username from the API call:

[EmailColumn] = USERPRINCIPALNAME()

Accurate Role Name and Username in API Payload: The roles field is case-sensitive and must match precisely. The username must correspond to what your RLS expression expects (typically an email).

Azure AD Token: Ensure your request includes a valid Azure AD bearer token with permission to access the dataset. Sample JSON Payload:

{

  "queries": [

    {

      "query": "EVALUATE VALUES('YourTableName')"

    }

  ],

  "identities": [

    {

      "username": "user@yourdomain.com",

      "roles": [

        "YourRoleName"

      ],

      "datasets": [

        "your-dataset-guid"

      ]

    }

  ]

}

Replace 'YourTableName' with the actual table name from your dataset

'YourRoleName' with your defined role

'your-dataset-guid' with the Dataset ID (GUID)

'user@yourdomain.com' with the value your RLS rule evaluates against

Ensure the role name matches exactly what’s defined in Power BI Desktop under Manage Roles.

Please refer these links for more information:
Row-level security (RLS) with Power BI - Microsoft Fabric | Microsoft Learn
What is Power BI Premium? - Power BI | Microsoft Learn

Kindly refer to the below solved link for better understanding:
Solved: Rest API to query dataset with RLS - Microsoft Fabric Community

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @marcio_fornari,

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-kpoloju-msft]

Hi @marcio_fornari,

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.

[v-kpoloju-msft]

Hi @marcio_fornari,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[marcio_fornari]

Hello, still not working. I think this endpoint (https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries ) not work with RLS. 

[v-kpoloju-msft]

Hi @marcio_fornari.,

Thank you for the update, and I appreciate your continued patience.

You are correct. The Execute Queries REST API does not enforce RLS by default as it does when a user views the report in the Power BI Service. The identities field can simulate RLS only under certain conditions, such as:

• The dataset is hosted in a Premium (P or A SKU) or Microsoft Fabric capacity workspace not in Pro-only workspaces.
• The username and roles fields in the payload must exactly match your RLS configuration expectations.
• The RLS role must correctly use USERPRINCIPALNAME() or USERNAME().
• A valid Azure AD token must be used with the appropriate dataset permissions.

If all these conditions are met and RLS is still not being applied, it is essential to understand that RLS simulation via API has limitations and may not behave exactly like the Power BI Service.

• Verify Workspace Capacity: Ensure that the dataset is located in a Premium or Fabric capacity workspace.
• Test RLS Simulation: As an alternative, you can test RLS directly in the Power BI Service using "Test as Role" to verify the logic.
• Explore Other Options: If enforcing RLS via API is essential, consider using embedding scenarios with effective identity, designed for secure per-user data access in embedded analytics.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you again for reaching out on the Microsoft Community Forum.

[marcio_fornari]

Hi, sorry for the long time to response. 
None of those solutions worked for me 😞 

Into my embedded report, the RLS works fine, the problem is just about the execute queries. 

My steps:

1 - Generate a authentication token by service principal and master user (the both not work)
2 - Get the token from the step 1 and call the api generate token:

POST:https://api.powerbi.com/v1.0/myorg/GenerateToken
{
  "datasets": [
    {
      "id": "xxx-xx-xxxx"
    }
  ],
  "targetWorkspaces": [
    {
      "id": "xxx-xxx-xxxx-xxx"
    }
  ],
  "identities": [
    {
      "username": "usernamexxx",  
      "roles": ["rolexxx"],     
      "datasets": ["xxxx-xxx-xxxx"]
    }
  ]
}

3 - Get the token from step 2 and execute querie:

POST: POST https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/executeQueries
BODY example:
{

  "queries": [

    {

      "query": "EVALUATE VALUES('mytable')"

    }

  ],