Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Valtyr
Advocate I
Advocate I

Azure Analysis Services RLS and Embedding

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.

1 ACCEPTED SOLUTION
Valtyr
Advocate I
Advocate I

I finally got this to work with PowerBi Embedding and RLS using the followingt instructions:

https://docs.microsoft.com/en-us/power-bi/developer/embedded-row-level-security#working-with-analysi...

 

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

 

 

View solution in original post

33 REPLIES 33
Valtyr
Advocate I
Advocate I

I finally got this to work with PowerBi Embedding and RLS using the followingt instructions:

https://docs.microsoft.com/en-us/power-bi/developer/embedded-row-level-security#working-with-analysi...

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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?

 

@Valtyr  @nimrod  @u02cm62  @Eric_Zhang 

I finally got this resolved and posted my solution on my question thread.

u02cm62
Helper V
Helper V

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?

Anonymous
Not applicable

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...:

Using the CustomData feature

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

 

@u02cm62

 

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.

 

@nimrod

 

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?

markus_x5
Regular Visitor

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: 

https://feedback.azure.com/forums/34192--general-feedback/suggestions/32984296-embed-rls-power-bi-re...

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors