topic Power BI Embedded + Data Gateway + SQL server + Import method + RLS in Developer

Power BI Embedded + Data Gateway + SQL server + Import method + RLS

Dan_0101 — Fri, 21 Feb 2020 10:43:56 GMT

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,

Re: Power BI Embedded + Data Gateway + SQL server + Import method + RLS

Jayendran — Fri, 21 Feb 2020 11:29:40 GMT

Hi @Dan_0101 ,

Please see the below links

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/14216322-leverage-sql-server-2016-row-level-security-for-po

https://powerbi.microsoft.com/en-us/blog/announcing-single-sign-on-support-when-connecting-to-data-sources-from-the-power-bi-service/

https://community.powerbi.com/t5/Service/RLS-for-SQL-Server-2016-with-Power-BI/td-p/133868

Re: Power BI Embedded + Data Gateway + SQL server + Import method + RLS

Dan_0101 — Fri, 21 Feb 2020 11:52:13 GMT

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.

Re: Power BI Embedded + Data Gateway + SQL server + Import method + RLS

Jayendran — Fri, 21 Feb 2020 12:08:41 GMT

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.

Re: Power BI Embedded + Data Gateway + SQL server + Import method + RLS

Dan_0101 — Fri, 21 Feb 2020 12:04:08 GMT

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 🙂