cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Dan_0101
Regular Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

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

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. 

 

 







Did my reply helpful to you ? Please give Kudos

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Super User II
Super User II

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

Dan_0101
Regular Visitor

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

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.

Super User II
Super User II

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

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. 

 

 







Did my reply helpful to you ? Please give Kudos

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Dan_0101
Regular Visitor

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

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 🙂

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors