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 ...
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.
      • mstefancik's avatar
        mstefancik
        Icon for Advocate IV rankAdvocate IV
        11 years ago

        andre

         

        That is crazy, how do they want to compete to the other visualization tools?

        Once it is supposed for enterprise usage, security is on the first place for every single comapny, or at least it should be.

         

        Anyway, we have to find way how to achieve it.

        As I am new to MS technology (formerly worked with Sybase) I want to ask to clear it out.

         

        Customer has DWH and makes reports to excell using BI tool to excel.

        We want him to use Power BI to have dynamic, real time dashboards accessible by broader range of employees (not only 3 managers) and to have them in any time.

         

        DWH is base on SQL server, so if we tried to connect directly to sql server, how can it be ensured that everybody will see only data he has got permission for. (Active directory, MS sql login passed to BI??)

        This is something we do not want to as there is complex logic behind.

         

        So we want to connect to excel workbook created and just visualize data.

         

        Do you have any usable suggestion? ONce again , i am new to MS, despite my question.

         

        Thx