title | description | author | ms.author | ms.reviewer | ms.service | ms.subservice | ms.topic | ms.date |
---|---|---|---|---|---|---|---|---|
Use row-level security with token based identities |
Learn about embedding single Power BI content within your application using token based, single sign-on (SSO) identities. |
mberdugo |
monaberdugo |
powerbi |
powerbi-developer |
how-to |
11/09/2023 |
[!INCLUDECustomers yes Org no]
The token-based identity allows an ISV to use an Microsoft Entra access token to pass the identity of a customer to an Azure SQL database managed in the customer's tenant.
ISV customers that keep and manage their data in Azure SQL Database can keep their data secure in their tenant when integrating with Power BI Embedded in the ISV app.
When generating the embed token, specify the identity of the user in Azure SQL by passing that user's Microsoft Entra access token for the Azure SQL server. The access token is then used to pull only the relevant data for that user from Azure SQL, for that specific session.
:::image type="content" source="media/rls-sso/pass-identity-using-token.png" alt-text="Schematic drawing showing ISV passing the effective identity to the SQL tenant and the customer passing an embed token back.":::
The token-based identity only works for DirectQuery models on a capacity connected to an Azure SQL Database that's configured to allow Microsoft Entra authentication. The semantic model's data source must be configured to use end users' OAuth2 credentials, to use a token-based identity. Learn more about Microsoft Entra authentication for Azure SQL Database.
-
From the Power BI portal, select Semantic model > More Options (three dots) > Settings > Data source credentials > Edit credentials.
:::image type="content" source="media/rls-sso/dataset-settings.png" alt-text="Screenshot dataset settings option in Power BI portal.":::
-
Check the OAuth2 option box.
:::image type="content" source="media/rls-sso/token-based-configure-azure-sql-db.png" alt-text="Screenshot of configure Azure SQL server.":::
Send a Gateways - Update Datasource API call with "useEndUserOAuth2Credentials" = True
and "credentialType": "OAuth2"
for the desired semantic model. The request body should look something like this:
{
"credentialDetails": {
"credentials": "{\"credentialData\":[{\"name\":\"accessToken\",\"value\":\"eyJ…\"}]}",
"credentialType": "OAuth2",
"encryptedConnection": "Encrypted",
"encryptionAlgorithm": "None",
"privacyLevel": "Organizational",
"useEndUserOAuth2Credentials": true
}
}
To create an access token for Azure SQL, the app must have Access Azure SQL DB and Data Warehouse delegated permission to Azure SQL Database API on the Microsoft Entra app registration configuration in the Azure portal.
:::image type="content" source="media/rls-sso/api-permissions.png" alt-text="Screenshot of Microsoft Entra app registration configuration settings in the Azure portal.":::
Authenticate and acquire a token for the user from the Azure AD v2 endpoint for the following scope: https://database.windows.net/.default
See the following MSAL code samples for help:
- Code samples for Microsoft identity platform authentication and authorization - Microsoft Entra | Microsoft Learn
- Microsoft identity platform and OAuth 2.0 authorization code flow
To Embed a report with token-based identity, generate an embed token that contains the token base identity of the desired ISV user. See the following examples for generating embed tokens for different scenarios.
{
"datasets": [
{
"id": "66ba5010-xxxx-xxxx-xxxx-f2bf0125abeb",
}
],
"reports": [
{
"allowEdit": false,
"id": "9e6da541-xxxx-xxxx-xxxx-7d9442827cce"
}
],
"datasourceIdentities": [
{
"identityBlob": "eyJ…",
"datasources": [
{
"datasourceType": "Sql",
"connectionDetails": {
"server": "YourServerName.database.windows.net",
"database": "YourDataBaseName"
}
}
]
}
]
}
{
"reports": [
{
"allowEdit": false,
"id": "2b0a27c7-xxxx-xxxx-xxxx-4d2036b42f90"
}
],
"datasourceIdentities": [
{
"identityBlob": "eyJ…",
"datasources": [
{
"datasourceType": "Sql",
"connectionDetails": {
"server": "YourServerName.database.windows.net",
"database": "YourDataBaseName"
}
}
]
}
]
}
{
"datasets": [
{
"id": "fff1a505-xxxx-xxxx-xxxx-e69f81e5b974",
}
],
"reports": [
{
"allowEdit": false,
"id": "10ce71df-xxxx-xxxx-xxxx-814a916b700d"
}
],
"identities": [
{
"username": "YourUsername",
"datasets": [
"fff1a505-xxxx-xxxx-xxxx-e69f81e5b974"
],
"roles": [
"YourRole"
]
}
],
"datasourceIdentities": [
{
"identityBlob": "eyJ…",
"datasources": [
{
"datasourceType": "Sql",
"connectionDetails": {
"server": "YourServerName.database.windows.net",
"database": "YourDataBaseName"
}
}
]
}
]
}
Paginated report connected to Power BI semantic model with RLS, and SSO data source that is connected with DirectQuery to another Power BI dataset
{
"datasets": [
{
"id": "35a4a948-xxxx-xxxx-xxxx-2e6ad7a02dc7",
"xmlaPermissions": "ReadOnly"
},
{
"id": "064ef46d-xxxx-xxxx-xxxx-fd6dda7a467b",
"xmlaPermissions": "ReadOnly"
}
],
"reports": [
{
"allowEdit": false,
"id": "9e81ebf7-xxxx-xxxx-xxxx-5a6294b43d55"
}
],
"identities": [
{
"username": "YourUsename",
"datasets": [
"35a4a948-xxxx-xxxx-xxxx-2e6ad7a02dc7"
],
"roles": [
"YourRole"
]
}
],
"datasourceIdentities": [
{
"identityBlob": "eyJ…",
"datasources": [
{
"datasourceType": "Sql",
"connectionDetails": {
"server": " YourServerName.database.windows.net",
"database": " YourDatabaseName "
}
}
]
}
]
}
The following example shows an embedded Power BI report with SSO and RLS applied to the dataset:
:::image type="content" source="media/rls-sso/report-example.png" alt-text="Screenshot of an embedded Power BI report with SSO and RLS applied to the dataset.":::