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?
If anyone has a link to an article detailing a process for implementing Row Level Security for SAP BW or HANA Import Queries based on SAP Authorizations, it would be much appreciated.
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?
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.
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?
You would need to define row level security in Power BI Desktop.
Hi @v-yuezhe-msft 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.
Power BI doesn't inherit SAP BW roles. It is possible to set dynamic row level security with SSAS tabular model.
Hi Lydia @v-yuezhe-msft
would there be any way of automaticaly retrieving the SAP BW roles and transforming them into the RLS format? Has someone done this yet?
After investigating this in my organization we decided to forgo bringing in the BW roles and just use the management chain that was maintained in Active Directory (AD) by our Human Resource department.
I have a direct connection to AD from Power BI to get a few dimensions (company, username, e-mail address, and management chain (that contains all the e-mail addresses of users that have access to the data). I delimite the management chain column so that there is a column for each user in the access chain.
I then do merge in Power BI between our sales data and the AD data to bring in the management chain columns to each row in my sales data.
Lastly, I created a role to apply RLS based on the user's e-mail address that is logged into the Power BI web-app so that the data is restricted to the sales person in the record, and his/her management chain based on what was in AD.
[Sales Position]=USERNAME() || [BuckmanManagementChain.ManagementChain.1]=USERNAME() || [BuckmanManagementChain.ManagementChain.2]=USERNAME() || [BuckmanManagementChain.ManagementChain.3]=USERNAME() || [BuckmanManagementChain.ManagementChain.4]=USERNAME() || [BuckmanManagementChain.ManagementChain.5]=USERNAME() || [BuckmanManagementChain.ManagementChain.6]=USERNAME()
You could probably use the same approach to BW role authorization by importing it as a .csv file and joining it with your data.
Thanks for taking the time to explain your scenario @Brysonds.
Seems a good idea but I'm afraid our BW roles are way more complex and even if we would use our AD data it wouldn't still be enough to cope with our distinct modules.
Anyway, thank you very much.
I think we will give up on this, and go for the RLS (double-work, double-maintenance).
Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.
Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!
At the monthly call, connect with other leaders and find out how community makes your experience even better.
Click here to read more about the May 2022 updates!