Greg Galloway on 19 Jan 2017 03:35:11
If you have an SSAS cube and you setup a gateway data source then go to User Mapping and switch the radio button to CustomData, the connection string used is now:
EffectiveUserName=user@domain.com;CustomData=user@domain.com
Basically turning on CustomData should stop emitting the EffectiveUserName property which is the whole point of switching to CustomData! Please fix this bug.
- Comments (6)
RE: SSAS Gateway data source CustomData setting is broken
'@Haggagy Sleem, pleasure!
I can confirm that this is still working and we are still using this approach successfully.
The steps are accurate to me but perhaps I have not made them clear enough.
some things to try:
- Make sure your non-admin account is getting through to your RLS Roles when connecting locally
- use Profiler to verify the connection string coming through to SSAS from the Gateway
- make sure the Gateway is sending CustomData () in Power BI Gateway Admin
- make sure the attribute on AD for Admin account is set and you have put that attribute correctly in the gateway config under ADUserNameReplacementProperty (i used "description"),
- also remember that the gateway is finding the admin account in AD using the ADUserNameLookupProperty, I used "userPrincipalName"(email), however you may need to use SAMAccount if you entered the Admin credential in gateway datasource that way etc.
When is works you will see in profiler connect with Admin Acc, Effective will be Non-Admin Acc, and then can use CustomData for your RLS rules.
RE: SSAS Gateway data source CustomData setting is broken
'@Duncan Mitchell
Hi,
Thank you very much for your explanation on using switching out the Admin user.
I applied your steps, but i can not use CustomData in PowerBI because once i use CustomData, the EffectiveUserName is not emitted (neither Admin nor any user).
Can you post an example about your entire solution.
RE: SSAS Gateway data source CustomData setting is broken
'Hi All, I have managed to work around this issue.
One can actually switch out the required Admin user from EffectiveUserName in the connection string...
Once you are using CustomData in "Map User Name" and have your SSAS Admin Domain account set for the Gateway Datasource, do this:
1) Find another non-Admin domain account.
2) Make it a member of your RLS Role in SSAS.
3) Update a miscellaneous/unused AD property of the Admin Domain account (e.g. "description), set this property to the UPN of the non-admin RLS account.
4) Update your Gateway config to do a manual lookup, i.e.
description
userPrincipalName
5) Restart yoiur Gateway service.
This will force the Gateway to intercept the EffectiveUserName!
i.e. you will connect with Admin, Impersonate Non-Admin RLS, and then can use CustomData for your RLS rules.
@PowerBI, I think this needs to be documented and shared because I searched for days and eventually managed to figure this out myself.
RE: SSAS Gateway data source CustomData setting is broken
Maybe just give user full access to "Additional Connection Parameters" in a freetext way just like Management Studio. Allow us you user any mix of supported connection parameters!
RE: SSAS Gateway data source CustomData setting is broken
Any update on this issue please?
RE: SSAS Gateway data source CustomData setting is broken
Let's say I register the gateway data source with username VM1\admin and then turn on CustomData in the user mappings. The actual connection string used to connect is when logged into Power BI as email@domain.com:
EffectiveUserName=VM1\admin;CustomData=email@domain.com
So the issue is that if VM1 is not domain joined, then EffectiveUserName=VM1\admin will fail since that's only supported against domain accounts.
So this appears to be a Power BI gateway bug that needs to be fixed so that it doesn't specify EffectiveUserName in the connection string when CustomData is on.