Forum Discussion
Power BI Embedded + Data Gateway + SQL server + Import method + RLS
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
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
- SSO is not able to work with external users.
- Import mode is only suitable for a single service account sql autentication.
4 Replies
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
- SSO is not able to work with external users.
- Import mode is only suitable for a single service account sql autentication.
