Forum Discussion

Stefkus's avatar
Stefkus
Frequent Visitor
11 years ago

Power BI + Azure SQL Server row-based security

Is it possible with Power BI and Azure SQL Server database to use row-based security? I know it's possible with a SSAS model.

 

We have a SQL Server database running on Azure, and we would like to connect Power BI based on a database username in combination with Azure Active Directory.

 

Our goal:

1 database with data for 10 customers (already have this in Azure SQL database), we want each customer to have a dashboard in Power BI with their own data.

 

Do we need to create 10 reports and dashboards? Are there other options? Is this only possible with SSAS?

 

I hope someone can shed some light on this matter. All ideas are welcome! Thanks in advance!

 

I hope

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    11 years ago

    My understanding is the the DAX USERNAME function only functions with SSAS and that therefore SSAS is the only data source that supports row-based security currently.

  • andre's avatar
    andre
    Icon for Memorable Member rankMemorable Member
    11 years ago

    SSAS is the only way to implement row level security for shared content.  All other datasets when shared run in the context of the person who created them and not the person who is viewing them.

    • mstefancik's avatar
      mstefancik
      Icon for Advocate IV rankAdvocate IV
      11 years ago

      We have got the same scenario. If it is possible only with SSAS, what else would you suggest, how to achieve everybody sees its data? We need to find the solution although it should be the part of Power BI to succeed.

      • andre's avatar
        andre
        Icon for Memorable Member rankMemorable Member
        11 years ago

        mstefancik,

        well, there are three things that I have to say about this:

         

        1. Power BI should have built-in rich security capability that permeates groups, datasets, reports dashboards and dataset attributes and calculations - unfortunately, it is not currently part of the service and we have not heard any comments from Microsoft on whether security will be built into Power BI
        2. I suggested here to make USERNAME() work in the service but it's not a good, robust solution, although it would allow for some options to secure data in the model by user.  Again, no comments from Microsoft on that
        3. Unfortunately, your only option is to promote your Power Pivot models to Analysis Services.  Even more unfortunate, your Desktop models cannot be promoted to Analysis Services in SQL 2014 because they are using the SQL 16 features and are not backwards compatible.