Using CUSTOMDATA and On-Premises SSAS with Power BI Embedded

Another Power BI embedded blog post while I have a playground up and running. Often when you want to use SSAS with Power BI embedded you have a “authentication issue”. Power BI and SSAS both leverage AD so that means that any user you pass from Power BI to SSAS needs to be known in AD. Now that is great in enterprise scenario’s where this usually is the case (and even required) but for ISV’s and any custom application this is a bigger problem. When embedding Power BI in a custom app you generally want to use the custom app’s authentication and not AD. Traditionally SSAS allows you to solve this using CUSTOMDATA and ROLES on the connection string https://docs.microsoft.com/en-us/sql/analysis-services/instances/connection-string-properties-analysis-services. With Power BI you cannot access the connectionstring so it is a bit harder. But there is a way.

Let’s solve this issue for Power BI and On premises SSAS.
Note: For Azure Analysis services you can use the CustomData function as described here: https://docs.microsoft.com/en-us/power-bi/developer/embedded-row-level-security. These steps won’t work for Azure AS.

First of course I have a SSAS model, for convenience sake I added a simple measure CustomData = Customdata() that will allow us to see what has been passed in.

I start with a PBI desktop file that points to the SSAS model and create a very simple report. You see the CustomData measure doesn’t return anything as it hasn’t been set on the connection string:

image

Next I publish the report to Power BI and set up a data gateway.  Here I am using a AD user that is admin to SSAS as the username and password (usually a service account)

image

In the Power BI service the report looks identical:

image

Now by default Power BI sets the EffectiveUsername property on the connection string as we can see in SQL Server Profiler output below on my Discover end (pro tip: Always have this up and running when testing like this, it is a real treasure trove Smile):

</pre>
<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<CATALOG_NAME>Sales</CATALOG_NAME>
<PERSPECTIVE_NAME>Model</PERSPECTIVE_NAME>
<VERSION>2.0</VERSION>
</RestrictionList>
<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
<Catalog>Sales</Catalog>
<Cube>Model</Cube>
<SspropInitAppName>PowerBI</SspropInitAppName>
<EffectiveUserName>DOMAIN\USERNAME</EffectiveUserName>
<LocaleIdentifier>1033</LocaleIdentifier>
<ClientProcessID>45052</ClientProcessID>
<Content>SchemaData</Content>
<Format>Tabular</Format>
<DbpropMsmdActivityID>ebe7a284-b140-4bd9-8c38-e162e36d8f99</DbpropMsmdActivityID>
<DbpropMsmdRequestID>66a221b2-37b9-283a-1064-6d0c2fb6a4a0</DbpropMsmdRequestID>
<DbpropMsmdCurrentActivityID>c882c8d1-60e8-4c1e-8beb-01865c2751c7</DbpropMsmdCurrentActivityID>
< /PropertyList>
<pre>

Now here comes the interesting part. In the gateway configuration part we can actually configure Power BI to also send the customdata propertie. This is hidden away in the Map user names pane:

image

As soon as you check this you will see 2 things. One the report now shows my email address as result from the CUSTOMDATA function

image

But also in SQL profiler you will see Customdata showing up

</pre>
<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<CATALOG_NAME>Sales</CATALOG_NAME>
<PERSPECTIVE_NAME>Model</PERSPECTIVE_NAME>
<VERSION>2.0</VERSION>
</RestrictionList>
<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
<Catalog>Sales</Catalog>
<Cube>Model</Cube>
<SspropInitAppName>PowerBI</SspropInitAppName>
<EffectiveUserName>DOMAIN\USERNAME</EffectiveUserName>
<CustomData>username@domain.com</CustomData>
<LocaleIdentifier>1033</LocaleIdentifier>
<ClientProcessID>45052</ClientProcessID>
<Content>SchemaData</Content>
<Format>Tabular</Format>
<DbpropMsmdActivityID>ebe7a284-b140-4bd9-8c38-e162e36d8f99</DbpropMsmdActivityID>
<DbpropMsmdRequestID>66a221b2-37b9-283a-1064-6d0c2fb6a4a0</DbpropMsmdRequestID>
<DbpropMsmdCurrentActivityID>c882c8d1-60e8-4c1e-8beb-01865c2751c7</DbpropMsmdCurrentActivityID>
</PropertyList>
<pre>

Part 1 completed. Now let’s start the Power BI embedding part.

Again I used the PowerBIEmbedded_AppOwnsData sample to get started and get up and running, again by default when you run it will act the same way as Power BI and the credentials used as the service account will be the one passed into SSAS. Now as explained in this Power BI help document you can change the sample to pass in any username you want. Now here is the key, because we configured the gateway to use CUSTOMDATA instead of the EffectiveUsername it will change the CUSTOMDATA part with whatever you specify. So in my sample project (HomeController.cs) I replaced :

var generateTokenRequestParameters = new GenerateTokenRequest(accessLevel: "view");
var tokenResponse = await client.Reports.GenerateTokenInGroupAsync(GroupId, report.Id, generateTokenRequestParameters);

with

var generateTokenRequestParameters = new GenerateTokenRequest("View", null, identities: new List<EffectiveIdentity> { new EffectiveIdentity(username: "username", roles: new List<string> { "roleA" }, datasets: new List<string> { report.DatasetId.ToString() }) });
var tokenResponse = await client.Reports.GenerateTokenInGroupAsync(GroupId, report.Id, generateTokenRequestParameters);

Now things get interesting on the SSAS side. We see something we might not expect as we are connecting to SSAS as administrator:

Either the user, ‘DOMAIN\USERNAME’, does not have access to the 'Sales' database, or the database does not exist.

The reason we are getting this is because we also specified the role. Going back to the connection string document in MSDN we can read the following on Roles:

Specify a comma-delimited list of predefined roles to connect to a server or database using permissions conveyed by that role. If this property is omitted, all roles are used, and the effective permissions are the combination of all roles. Setting the property to an empty value (for example, Roles=’ ‘) the client connection has no role membership.An administrator using this property connects using the permissions conveyed by the role. Some commands might fail if the role does not provide sufficient permission.

So this means that on this connection he is no longer admin and he needs a role to connect to. So let’s add one, I go into SSMS and add the role with the role name “roleA” as defined in the code above with Read rights:

Next you have to make sure you add the service account as member and lastly you can add a RLS expression.

In my case I added something hard coded:

=DimProduct[ColorName] = IF(CUSTOMDATA() = "username", "Silver", BLANK())

but of course you can follow the complete RLS pattern here and just replace USERNAME for CUSTOMDATA.

Running the report shows what we wanted to see. In the profiler we see it connecting to the Role:

And in the report it only shows Silver colors and returns “username” out of the CUSTOMDATA function:
image

Looking at the profiler trace we can again see it passes in the right CUSTOMDATA field:

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
<Catalog >Sales </Catalog >
<Cube >Model </Cube >
<DbpropMsmdOptimizeResponse >9 </DbpropMsmdOptimizeResponse >
<DbpropMsmdActivityID >0BA0F14D-56AA-4F8A-BC65-93FD2556FD8E </DbpropMsmdActivityID >
<DbpropMsmdRequestID >0A1AEB37-E7C5-45EB-8F75-8816A0648B0C </DbpropMsmdRequestID >
<DbpropMsmdCurrentActivityID >0BA0F14D-56AA-4F8A-BC65-93FD2556FD8E </DbpropMsmdCurrentActivityID >
<LocaleIdentifier >1033 </LocaleIdentifier >
<Roles >roleA </Roles >
<EffectiveUserName >DOMAIN\USERNAME </EffectiveUserName >
<sspropinitappname >PowerBI </sspropinitappname >
<CustomData >username</CustomData >
</PropertyList >

That’s it folks Smile This opens a lot of interesting scenario’s for connecting to AS from Power BI. Unfortunately this doesn’t work for Azure AS as you cannot use the gateway to connect to it.

Note: Some folks are getting errors connecting to the dataset, it is important that the service account you are using needs to be work space admin. You do a take over to make sure. From the Power BI portal > Select your Workspace > Datasets > On the Dataset of interest, go to Settings > Take over.

8 Replies to “Using CUSTOMDATA and On-Premises SSAS with Power BI Embedded

  1. Terrific post! Thank you so much for this; it really helped me implement RLS on my Tabular model using CustomData and then embed a Power BI report with the model as its data source into my web app.

  2. Hi,

    I have a SSAS instance on a VM and Power BI on another VM. Though I was able to connect to SSAS instance through Power BI desktop, after publishing the report to the service and confugring a on premises gateway am getting a message saying Please contact administrator.

    Any input is highly appreciated.

  3. Hi kasper,

    i dont see you passing any custom data like product id or some other data, you are still passing username.
    Could you please let me know if there is a change in this snippet if custom data has to be passed
    var generateTokenRequestParameters = new GenerateTokenRequest(“View”, null, identities: new List { new EffectiveIdentity(username: “username”, roles: new List { “roleA” }, datasets: new List { report.DatasetId.ToString() }) });

    there is a overload which takes customData as a string

    1. You don;t need to in this case it will automatically use the username as customdata. You are right there is now a new function where you can pass it yourself. I should update the blog post.

  4. This is a great post and the approach has worked “as advertised”. My previous implementations have been on Premise AS. If I read correctly you say it won’t work with Azure AS. Do you know if there has been an update where we can now pass customdata to Azure AS?

  5. Kasper,

    Is it possible to have two roles, one admin and the other the readonly role.
    The admin role has an explicit list of administrators
    readonly role has the service account so that customdata() will work?

    I tried this on my end but it doesn’t seem to work so was wondering if you tried this or is there documentation saying that if you enable customdata() the admin functionality in a role is ineffective.

  6. Hi Kasper,

    we can pass parameters to the SSAS model using the CUSTOMDATA() function, this is working fine for the reports which has Import Mode but for direct query mode i am getting error, “Couldn’t load the data for this visual” Http Status Code401. I see its un-authorized but at the same time other reports deployed to same app workspace in import mode are working fine.

    Need your help!!!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.