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:

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)

In the Power BI service the report looks identical:

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

</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:
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
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:
 
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  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.
Share this:
Click to share on Twitter (Opens in new window)
Click to share on Facebook (Opens in new window)