Forum Discussion
Advocate IAbility to fetch SAP BW data respecting user's authorization
Hi all,
we're trying to conclude if we can avoid building up Row Level Security when using IMPORT or DIRECTQUERY connection type to SAP BW. So our current scenario is testing the SSO capabilities when connecting Power BI (online) to SAP BW. The reason is hat we put so much effort and have a team working just for roles in SAP BW and we don't want to have to do it in paralel in power bi.
So, we've been exhaustively testing the SSO (using the windows/kerberos) authentication to Business Warehouse Server (BW), using the 64 bit version, with different users having different roles and we seem not to be able to get the data the user is allowed to see, and as such, not following BW authorizations. (please take into consideration I am only referring to the online platform scenario, right after the dataset is published from the desktop tool).
Specifically, if using the DirectQuery connection type, it seems to be using the BW authorization of the user we've set in the gateway datasource connection (we're using Windows authentication so we supposed the SSO would work here). Unfortunately it is not following the BW authorization of the user refreshing the data. This is quite important as it is a possible no-go for the SAP BW connector adoption.
For the IMPORT connection type, I suppose this would need to be handled via Row Level Security. or maybe not? Nonetheless, we want to avoid double maintenance on roles .
The most used situation in our environment is PRO users sharing with a group of other users (FREE and PRO). If we allow this connector at this stage then it would result in users seeing data they're not supposed to see.
Does anyone have a solution on how to overcome this partly or fully?
many thanks
Rúben
12 Replies
rubensalvador,
You would need to define row level security in Power BI Desktop.
https://docs.microsoft.com/en-us/power-bi/service-admin-rls
Regards,
Lydia- rubensalvador
Hi Anonymous Lydia,
that's in fact what we want to really avoid. The task to reproduce the SAP BW roles it's simply too heavy to consider. It'd be a no-go for us.
best regards
rubensalvador,
Power BI doesn't inherit SAP BW roles. It is possible to set dynamic row level security with SSAS tabular model.
https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-row-level-security-onprem-ssas-tabular
Regards,
Lydia
We're facing the same issue, but haven't tried to use Kerberos which was my plan to test. So just to be clear; it isn't possible to create a setup where a Power BI gateway connects to a SAP BW system and passes the AD credentials from the Power BI service down to the database execution layer and thereby filters the data to only contain the rows that she/he is authorize to see based on the BW authorization system?
- rubensalvador
Hi aamikkelsen,
no, not according to my tests.
Whatever scenario I use, IMPORT or DIRECT QUERY, Gateway user rows are allways retrieved and unfortunately not the user rows that I've share the report with. Microsoft restates the need to recreate roles by using the Row Level Security.
We're trying to explore other possibilities such as Azure Analysis Services, but that will bring a huge cost for us definitely.
br
Rúben
Hey there rubensalvador!
I just stumbled upon your post, and I wonder if it's still the case that SSO can't be used to authenticate the end user when using direct query? Or did you manage to get it working?
Hello,
Any update on this, pls? any progress or success in inheriting SAP BW roles?
