Azure Analysis Services RLS and Embedding

Hi,

The method works like this:

=TABLENAME[COLUMNNAME] = Customdata()

In your case it might look something like this:

=ACCOUNTS[ACCOUNTID] = Customdata()

Hope this helps :)

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;
}

Are all these about only Azure AS or also about on premise AS 2017? The first post said both were tried but that was Oct and in Oct it wasn't ready for RLS, but came out shortly thereafter for changes in the web app. We are using SSAS 2017 on premise because we felt Azure AS didn't have RLS working yet.  We are using AppOwnsData model and have RLS and using live connections to tabular cubes on premise, but we display these through the gateway on Salesforce and it works fine.  Is this only a problem with Azure AS?  It would be good to know as we want to go there, but felt it wasn't ready for prime time yet.

I concur with Valtyr; this feature is also critical and an urgent matter for us to go forward with our PBI Embedded project.