March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
We would like to embed reports with RLS but I'm starting to get the feeling that is not possible with Analysis Services(both Azure and on premise) using live connection in Power BI.
We started with on-premise analysis service (live connection) but that is not supported according to the following document:
https://docs.microsoft.com/en-us/azure/power-bi-embedded/power-bi-embedded-connect-datasource
Therefore, we moved the data to Azure and now we have Azure Analysis Service live connection and would like to embed that with RLS.
We are having problems implementing this and on the following webpage there is a note saying that Analysis Services live connections are not supported:
https://powerbi.microsoft.com/en-us/documentation/powerbi-developer-embedded-rls/
Is this really the case and if so when can we expect to embed RLS Power Bi reports with Analysis Service data (live connection)? Is this on the roadmap?
Btw, we are working with big datasets so using import instead of live connection is not really an option for us.
Solved! Go to Solution.
I finally got this to work with PowerBi Embedding and RLS using the followingt instructions:
Make sure that you use Customdata DAX funtion to filter the data and also that add an Azure user to the Role:
=INS_TABLE[column] = Customdata()
I used the sample project found here:
https://docs.microsoft.com/en-us/power-bi/developer/embed-sample-for-customers#embed-your-content-us...
with the following modification:
// Generate Embed Token for reports without effective identities.
var rls = new EffectiveIdentity("xxxAzureUserName", new List<string> { report.DatasetId }, new List<string>() { "SchoolFiltering" }, "SchoolName");
generateTokenRequestParameters = new GenerateTokenRequest(accessLevel: "view", identities: new List<EffectiveIdentity> { rls });
Hope this helps
I finally got this to work with PowerBi Embedding and RLS using the followingt instructions:
Make sure that you use Customdata DAX funtion to filter the data and also that add an Azure user to the Role:
=INS_TABLE[column] = Customdata()
I used the sample project found here:
https://docs.microsoft.com/en-us/power-bi/developer/embed-sample-for-customers#embed-your-content-us...
with the following modification:
// Generate Embed Token for reports without effective identities.
var rls = new EffectiveIdentity("xxxAzureUserName", new List<string> { report.DatasetId }, new List<string>() { "SchoolFiltering" }, "SchoolName");
generateTokenRequestParameters = new GenerateTokenRequest(accessLevel: "view", identities: new List<EffectiveIdentity> { rls });
Hope this helps
Thanks @Valtyr,
for sharing your solution.
Regards,
Tom
Hi @Valtyr ,
I have been trying to embed a powerbi report based on an AAS model as well
We keep getting the following error:
Result
11:12:36:747 USER_DEBUG [153]|DEBUG|Body: {"error":{"code":"InvalidRequest","message":"Creating embed token for accessing dataset *dataset id* requires effective identity to be provided"}}
The modification specified in your comment, which file did you add it in?
And did you not use Customdata() function along with it?
Is there a sample code you can provide with which we can get this working?
I would really appreciate any kind of help.
Thanks!
Assuming that you're using C#, below is an implementation that I have used for a Razor pages implementation.
It is by no means perfect but it works, I hope it helps
// CSPROJ
<PackageReference Include="Microsoft.AnalysisServices.NetCore.retail.amd64" Version="19.10.0-Preview" />
<PackageReference Include="Microsoft.ApplicationInsights" Version="2.14.0" />
<PackageReference Include="Microsoft.ApplicationInsights.AspNetCore" Version="2.14.0" />
<PackageReference Include="Microsoft.AspNet.WebApi.Client" Version="5.2.7" />
<PackageReference Include="Microsoft.AspNetCore.Authentication.AzureAD.UI" Version="3.1.7" />
<PackageReference Include="Microsoft.CodeAnalysis.Common" Version="3.7.0" />
<PackageReference Include="Microsoft.Identity.Web" Version="1.2.0" />
<PackageReference Include="Microsoft.IdentityModel.Clients.ActiveDirectory" Version="5.2.8" />
<PackageReference Include="Microsoft.PowerBI.Api" Version="3.14.0" />
// App Settings
"ActiveDirectoryUpn": {
"ClaimTypesToSearch": [
"http://schemas.xmlsoap.org/ws/2005/05/identity/claims/upn",
"http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress",
"preferred_username"
]
}
// Reports.cshtml.cs
// _settingsType is an enum
public async Task OnGetAsync(Guid? reportId)
{
ClaimsPrincipal user = _httpContextAccessor.HttpContext.User;
List<Claim> claims = user.Claims.ToList();
IList<Report> reportsResult = await _reportRepository.GetAvailableReportsAsync(_settingsType);
Reports = new List<Report>(reportsResult);
string[] claimTypesToSearch = _activeDirectoryUpnSettings.Value.ClaimTypesToSearch;
// SECURITY : Don't do this unless we can secure things downstream
string upn = claims.Where(c => claimTypesToSearch.Contains(c.Type) && RegexUtilities.IsValidEmail(c.Value)).Select(c => c.Value).FirstOrDefault();
string claimsJson = JsonConvert.SerializeObject(GenerateDictionaryForUserClaims(claims));
if (reportId.HasValue)
{
SelectedReport = await _reportRepository.GetEmbeddedReportConfigAsync(reportId.Value, upn, claimsJson, _settingsType);
var identityNameId = user.Claims.FirstOrDefault(x => x.Type == ClaimTypes.NameIdentifier);
var identityName = user.Claims.FirstOrDefault(x => x.Type == "preferred_username");
_telemetry.TrackEvent("ReportOpened", new Dictionary<string, string>
{
{ "report_Id", SelectedReport.Id },
{ "report_Name", SelectedReport.Name }
});
}
else
{
SelectedReport = await _reportRepository.GetEmbeddedReportConfigAsync(null, upn, claimsJson, _settingsType);
}
}
// ReportRepository.cs
using Microsoft.PowerBI.Api;
using Microsoft.PowerBI.Api.Models;
public async Task<EmbeddedReportConfig> GetEmbeddedReportConfigAsync(Guid? reportId, string name, string claimsJson, SettingsTypeEnum settingsType)
{
PowerBiSettings powerBiSettings = GetPowerBiSettings(settingsType);
AzureToken azureToken = await _authenticationHandler.GetAzureTokenDataAsync(settingsType);
using (var powerBiClient = new PowerBIClient(new Uri(powerBiSettings.ApiUrl), azureToken.TokenCredentials))
{
List<string> roles = await GetDataFromAzureAnalysisServices(settingsType, name);
int assignedRolesCount = roles.Count;
string debugOutput = BuildDebugOutput(reportId, name, claimsJson, roles, assignedRolesCount);
if (!reportId.HasValue)
{
// Initial page load where no report is selected or the user has manually removed the report GUID from the URL
return GetEmbeddedReportConfig(null, null, 0, debugOutput);
}
else if (!roles.Any())
{
// A report is selected but the user has no roles
return GetEmbeddedReportConfig(new Report(reportId.Value), null, 0, debugOutput);
}
try
{
Report powerBiReport = await powerBiClient.Reports.GetReportAsync(powerBiSettings.WorkspaceId, reportId.Value);
// SECURITY : Is there a better way to do Effective Identity without passing through CustomData?
EffectiveIdentity rowLevelSecurityIdentity = new EffectiveIdentity(powerBiSettings.Username, roles: roles, datasets: new List<string> { powerBiReport.DatasetId }, customData: name);
GenerateTokenRequest powerBiTokenRequestParameters = new GenerateTokenRequest("View", powerBiReport.DatasetId, false, rowLevelSecurityIdentity);
EmbedToken powerBiTokenResponse = await powerBiClient.Reports.GenerateTokenInGroupAsync(powerBiSettings.WorkspaceId, powerBiReport.Id, powerBiTokenRequestParameters);
return GetEmbeddedReportConfig(powerBiReport, powerBiTokenResponse, assignedRolesCount, debugOutput);
}
catch (HttpOperationException ex)
{
//Bad Request
var content = ex.Response.Content;
Console.WriteLine(content);
throw;
}
}
}
private EmbeddedReportConfig GetEmbeddedReportConfig(Report powerBiReport, EmbedToken powerBiTokenResponse, int assignedRolesCount, string debugOuput)
{
return new EmbeddedReportConfig
{
Id = powerBiReport?.Id.ToString(),
Name = powerBiReport?.Name,
EmbedUrl = powerBiReport?.EmbedUrl,
AccessToken = powerBiTokenResponse?.Token,
AssignedRolesCount = assignedRolesCount,
DebugInformation = debugOuput
};
}
private async Task<List<string>> GetDataFromAzureAnalysisServices(SettingsTypeEnum settingsType, string upn)
{
AzureAnalysisServicesEndpointSettings settings = GetAzureAnalysisServicesEndpointSettings(settingsType);
string serverDomain = settings.ServerDomain;
string serverName = settings.ServerName;
// Use the trial database as a safe default
string databaseModel = settingsType == SettingsTypeEnum.Premium ? "PilotV1" : "PilotV2Demo";
string serverAddress = $"asazure://{serverDomain}/{serverName}";
// Used to be used in the authority URL
string tenantId = settings.TenantId;
string appId = settings.ApplicationId;
string appSecret = settings.AppSecret;
string authorityUrl = settings.AuthorityUrl;
AuthenticationContext authContext = new AuthenticationContext(authorityUrl);
// Config for OAuth client credentials
ClientCredential clientCred = new ClientCredential(appId, appSecret);
AuthenticationResult authenticationResult = await authContext.AcquireTokenAsync($"https://{serverDomain}", clientCred);
string connectionString = $"Provider=MSOLAP;Data Source={serverAddress};Initial Catalog={databaseModel};User ID=;Password={authenticationResult.AccessToken};Persist Security Info=True;Impersonation Level=Impersonate";
List<string> roles = new List<string>();
using (Server server = new Server())
{
server.Connect(connectionString);
// If your database isn't appearing here, then it is most likely because the AAS role which has the "Full Control" permission
// along a member entry for app:<app-registration-object-id>@<azure-active-directory-tenant-id> has been removed.
Database database = server.Databases.FindByName(databaseModel);
if (database == null)
{
return roles;
}
var modelRoles = database.Model.Roles;
List<RoleMapping> roleMappings = modelRoles.Select(r => new RoleMapping(r.Name, r.Members.Select(m => m.MemberName).ToList())).ToList();
roles = roleMappings.Where(g => g.Members.Contains(upn, StringComparer.InvariantCultureIgnoreCase)).Select(k => k.Name).ToList();
}
return roles;
}
Hi @MattStannett
Thank you so much for the code!
In the following:
// SECURITY : Is there a better way to do Effective Identity without passing through CustomData?
EffectiveIdentity rowLevelSecurityIdentity = new EffectiveIdentity(powerBiSettings.Username, roles: roles, datasets: new List<string> { powerBiReport.DatasetId }, customData: name);
GenerateTokenRequest powerBiTokenRequestParameters = new GenerateTokenRequest("View", powerBiReport.DatasetId, false, rowLevelSecurityIdentity);
What paremeter values am I passing here?
powerBiSettings.Username
customData: name
Also, are you using CUSOTMDATA() filter in the roles?
No problem @Rishabh-Maini!
For powerBiSettings.Username I use the email address of my Master User in Power BI.
For name I use the value passed into the function, which you can see is the upn variable set in the OnGetAsync method.
Yes I am using CUSTOMDATA to filter the roles on the AAS side.
Thanks for the post. I have simillar scnerio where in embedded mode I need to display PowerBi report and Powerbi is consuming data from Azure analasis Services. I need to filter data from analysis services based on RLS rule (AccountID). Not getting how you have specified CustomeData() function. Can you please pass on any sample? thanks.
Hi,
The method works like this:
=TABLENAME[COLUMNNAME] = Customdata()
In your case it might look something like this:
=ACCOUNTS[ACCOUNTID] = Customdata()
Hope this helps 🙂
I am using PBI App Owns data, Azure Analysis Services Live Connection, and Azure Active Directory.
In order to pass through an EffectiveIdentity and have RLS applied to the resulting report using a UPN and to avoid the following error
"Creating embed token for accessing dataset xxxxxxxxxxxxxxxxxx requries effective identity username to be identical to the caller's principal name”
do I need to use the CustomData() function and add a row filter to my roles in Azure Analysis services to enforce this?
We have it working for on premise SSAS 2017, AppOwnsData, live connections and embedded in Salesforce with an Azure web app.
SSAS Paas (Azure AS) i felt wasn't ready yet for RLS which is why we held off. Are you only concerned with Azure AS with AppOwnsData and live connections?
Hello u02cm62,
At this moment I am trying to setup RLS in Power BI Embedded (in a portal) using on premise SSAS, AppOnwsData, Live connection with a multidimensional cube.
Could you tell how to setup? I tried with CustomData() but according to this site, it's not possible when you are not having a Azure Analysis Services: https://docs.microsoft.com/nl-nl/power-bi/developer/embedded-row-level-security#on-premises-data-gat...:
The CustomData feature only works for models that lie in Azure Analysis Services, and it only works in Connect live mode. Unlike users and roles, the Custom data feature can't be set inside a .pbix file. When generating a token with the Custom data feature, you need to have a username.
I posted my question online, but got no responses. It's hard to find any information about how this works.
Thanks in advance
We are currently only concerned about Azure AS with AppOwnsData and live connection but it is good to hear that it works for On-Premise. We will probably have to go down that route until the azure live connection is fully supported.
As mentioned in my previous comment, we will go live with CustomData support for AAS using live connection.
CustomData funciton can be used for RLS as well.
Hi,
Any updates on ETA when the Custom data will be supported in AAS?
Hi,
PBIE supports RLS on AS Live connection. You can see it here- https://docs.microsoft.com/en-us/power-bi/developer/embedded-row-level-security#working-with-analysi....
In addition, We support filtering by pre-defined roles in AAS.
And- we will release within a month support of filtering by CustomData in AAS.
As mentioned in the limitations in the link you sent this is only supported for On-Premise:
-analysis Services live connections are supported for on-premises servers.
Can you give us any insights on when this will be fully supported for Azure analysis service?
I'm having the same issue, and I thought I was doing something wrong (the documentation is not great), but apparently it's a lack in the service.. Like everyone else in this thread, I would also love to get more info on future plans for support.
Edit:
I create a feedback entry for this issue at feedback.azure.com, feel free to upvote it, that way maybe we can get some attention on this:
I've heard vague rumours that this might be support in Q1 2018. I would like to get that confirmed but I'm not sure where to get a proper answer.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
36 | |
27 | |
19 | |
11 | |
8 |
User | Count |
---|---|
55 | |
43 | |
25 | |
13 | |
12 |