Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
I was hoping someone could clarify a situation I am in, the goals/overview are:
- Share reports with our external customers (NOT a B2B scenario)
- Use Power BI Embedded (yes we have developer resources)
Page 162 of the Power BI Enterprise Deployment guide mentions it may be better to create roles for RLS on the data source instead of within the PBIX file - which I definitely agree with. The issue I seem to have is it does not seem possible to pass a role (or any information really) back to the SQL server, it only knows about the SQL account configured on the Data Gateway Data Source.
SQL server <-> Data Gateway + Data Source (access configured using local SQL account)
PBIX file with Roles published - matched to Data Gateway + Data Source
Web App + Service Principal + Role <-> External customer login (Auth is performed, role is passed, appropriate data is displayed)
The only account the SQL server ever knows about is the one configured on the Data Gateway Data Source. Can we configure RLS on the SQL server somehow or do we need to look at using a different method (like SSAS as the data source?)
I hope that makes sense, many thanks for any insight.
Cheers,
D
Solved! Go to Solution.
Hi @Dan_0101
Ah yes now I get some more detailed requirements, as per the data you gave we can say that you couldn't able to use RLS from your data source.
SSO will not work for your requirement due to
Hi @Dan_0101 ,
Please see the below links
https://community.powerbi.com/t5/Service/RLS-for-SQL-Server-2016-with-Power-BI/td-p/133868
Thanks for your reply @Jayendran !
I have read most of those and I think there are a couple of things:
1) For each query, the Power BI service includes the user principal name (UPN), which is the fully qualified username of the user currently signed in to the Power BI service
We do not have accounts for our external customers on our domain - as I understand it userprincipalname() and SSO will not be useful to us?
2) We are executing stored procedures to collect our data from the reporting server, thus we are using Import method, not DirectQuery
3) We have a working demo environment that works with the RLS configured within the PBIX file - we just want to shift the roles back to the SQL server.
I don't understand how a piece of data from a non-AD/AAD external customer can traverse the Data Gateway and be 'viewed' by SQL Server.
Hi @Dan_0101
Ah yes now I get some more detailed requirements, as per the data you gave we can say that you couldn't able to use RLS from your data source.
SSO will not work for your requirement due to
Thank you for clarifying @Jayendran
I also just re-read this article:
https://docs.microsoft.com/lt-lt/power-bi/developer/embedded-faq
With service principal, you can configure row-level security (RLS) using an SQL Server Analysis Services (SSAS) on-premises live connection data source. This way you can manage users and their access to data in SSAS when integrating with Power BI Embedded using a service principal.
It specifically says SSAS, so I think that if we want to do this we would need to move to using that instead of SQL database.
So many pieces, it is sometimes hard to figure out what supports what 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
2 | |
2 | |
1 | |
1 |