Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rubensalvador
Advocate I
Advocate I

Ability 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 12
vpshamsbi
Frequent Visitor

Hello,

 

Any update on this, pls? any progress or success in inheriting SAP BW roles?

joel_gibby_csc
Helper II
Helper II

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. 

 

Thanks,

Joel

aamikkelsen
New Member

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?

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

Anonymous
Not applicable

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?

v-yuezhe-msft
Employee
Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

 

br

Rúben

Hi Ruben,

 

 

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.

 

Good luck!

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

 

br

Rúben

@rubensalvador,

I am afraid not. You can submit an idea here.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors