Forum Discussion

fabio78's avatar
fabio78
Helper I
7 months ago
Solved

Power BI Report Server with custom authentication error Power Bi Desktop

Hi, in a previous post I asked for help configuring PowerBI Report Server with custom authentication and I solved it, so the portal now asks me for username and password via a Logon page, I can acces...
Power BI Report Server Power Bi Desktop custom authentication
  • v-sgandrathi's avatar
    v-sgandrathi
    6 months ago

    Hi fabio78,

     

    You’ve used the correct and reliable approach for integrating Power BI Report Server with Custom Authentication and SSAS. By adding CUSTOMDATA={{USERID}} to the PBIRS connection string and referencing it in an SSAS role, you successfully recreate the user context that PBIRS doesn’t provide in Forms mode. Your setup works as intended: the Custom DLL sets the user identity, PBIRS forwards it via CUSTOMDATA, and SSAS applies Row-Level Security through DAX. This method is supported for per-user filtering when EffectiveUserName isn’t available.

    Your architecture is solid: PBIRS operates in Custom Authentication mode, the report sends the custom identity in the connection string, and SSAS handles security in the role. This ensures that all security is managed within the semantic layer, not the report. The results you’re seeing, admins having full access and other users being filtered are expected.

    To expand this to groups, modify your DLL to capture user groups and pass a normalized value into CUSTOMDATA, such as a group key or list. Then, add a group-to-business key mapping table in your Tabular model and adjust your role DAX to filter using CUSTOMDATA(). This approach keeps authorization logic in SSAS, maintains your custom identity throughout, and avoids unsupported changes in PBIRS.

     

    Thank you.

fabio78's avatar
fabio78
Helper I
6 months ago

Hi v-sgandrathi 

I wanted to update you on the situation. My current configuration is as follows:
- Power BI Report Server configured in custom authentication mode
- Custom DLL
- Power BI Desktop for report creation
- SSAS for cubes

The thing I was missing was how to filter custom users with ssas, and it seems I've done it this way.
I added the PBIRS service user as an SSAS server administrator, then created a Windows user (user group) to connect to the ssas tabular database, and also created a role (read-only) with a custom DAX query, passing it my customdata(). In the report deployed to PBIRS, I added the customdata string to the connection string (for example, CUSTOMDATA={{USERID}}).
Now, based on the DAX query, if I log in with the Admin user, I see everything; if I log in with other users, such as user or user1, the data is filtered.
The next step I'll take is to be able to filter data not just by users, but also by groups.

Thank you.

Map_67's avatar
Map_67
Frequent Visitor
6 months ago

Hi,

I have the same configuration:

  • Power BI Report Server configured in custom authentication mode
  • Custom Security Extension (based on the Microsoft sample)
  • Power BI Desktop for report creation
  • SSAS Tabular as the data source

I’m currently using the CustomSecurity sample from this repository:
https://github.com/microsoft/Reporting-Services/blob/master/CustomSecuritySample/README.md

I would like to know if you made any modifications to the CustomSecurity implementation to make the CustomData ({{USERID}}) work correctly with SSAS.

If your answer is yes, could you please explain what changes you made and where?

Thank you in advance for your help.

  • fabio78's avatar
    fabio78
    Helper I
    6 months ago

    Hi, my current configuration, still under testing, is this:
    - SQL Server Database
    - SSAS
    - Power BI Report Server, custom authentication
    - Power BI Desktop

    I've implemented various changes to the CustomSecurity DLL. Currently, my DLL performs programmatic authentication for three users and interfaces with a database to retrieve the group. This is because the authentication will be integrated with an external API service. Once authenticated, Power BI Report Server recognizes the user or group for managing site security rights and folder/report rights. Additionally, "user,group" is injected into customdata().

    In SSAS, I created a tabular database and created rules for user X, not the Administrator user, which will then be used for the connection between Power BI Report Server and SSAS. In the DAX string, I defined the query to filter the data, e.g.,

    =VAR _CD = CUSTOMDATA()
    VAR _CD_Standardized = SUBSTITUTE(_CD, ";", "|")

    VAR _User = PATHITEM(_CD_Standardized, 1) -- Extracts "user1"
    VAR _Groups = PATHITEM(_CD_Standardized, 2) -- Extracts "GROUP"

    RETURN
    IF (
    ISBLANK(_CD) || _CD = "",
    FALSE(),
    SWITCH (
    TRUE(),
    -- 1. ADMIN management
    _User = "admin" || CONTAINSSTRING(_Groups, "ADMIN"), TRUE(),

    -- 2. MANAGEMENT OF OFFICE_SALES GROUP
    CONTAINSSTRING(_Groups, "SALES_OFFICE"), 'OrariOdoo'[Employee] = "Fabio",

    -- 3. Managing SINGLE USERS
    _User = "user1", 'OrariOdoo'[Employee] = "Mario",

    -- Default
    FALSE()
    )
    )

    When connecting the report after it's deployed to Power BI Report Server, you must add ;CUSTOMDATA={{USERID}};Roles=RuoloFilter at the end like this:

    Data Source=localhost;Initial Catalog=TabularOrariOdooPowerUser;Cube=Modello;CUSTOMDATA={{USERID}};Roles=RuoloFilter

    If you want, I can send you the project; it's very cumbersome for various tests.

    Bye

    • Map_67's avatar
      Map_67
      Frequent Visitor
      6 months ago

      Hi,

       

      Thank you again for your detailed explanation — it was very helpful.

       

      If possible, I would really appreciate it if you could share your project. Having a working example would help me better understand the CustomSecurity implementation and how CustomData is being used.

       

      You can share it in any way that is convenient for you:

       

      - GitHub repository

      - OneDrive / Google Drive / Dropbox link

      - Or, if you prefer, you can send it by email — I can provide my email address.

       

      Even a simplified sample (without any sensitive information) that includes the CustomSecurity project and the related configuration would be extremely helpful.

       

      Thank you very much for your time and for sharing your work.

       

      Best regards

      • Map_67's avatar
        Map_67
        Frequent Visitor
        6 months ago

        Hi,

         

        I’d like to share the configuration that worked for me for Dynamic RLS in the following scenario:

         

        Scenario

         

        - Power BI Report Server with Custom Authentication (Microsoft CustomSecuritySample)

        - SSAS Tabular

        - Dynamic RLS using "CUSTOMDATA()"

        - No changes to the CustomSecuritySample code

         

        ---

         

        Step 1 — Create RLS Role in SSAS Tabular

         

        Create a role (Your_Role) with Read permission.

         

        Example filter:

         

        V_AccessDynamicRowLevelSecurity[LoginName] = CUSTOMDATA()

         

        ---

         

        Step 2 — Add a Non-Admin Account to the Role (Important)

         

        Create or choose a domain user that is NOT an SSAS Server Administrator.

         

        Add this user as a member of Your_Role.

         

        «This step must be done before using the account in Power BI Report Server.

        Otherwise, the connection to SSAS may fail due to insufficient permissions.»

         

        ---

         

        Step 3 — Configure the Data Source in Power BI Report Server

         

        Connection string:

         

        Data Source=SERVERNAME;Initial Catalog=Your_OLAP;Cube=Model;CUSTOMDATA={{UserId}}

         

        In Credentials:

         

        - Authentication: Windows Authentication

        - Select: Using the following credentials

        - User name:

         

        DOMAIN\non-adminUser

         

        ---

         

        Key Points

         

        - The account used in the PBIRS Data Source:

           - Must be added to the SSAS Role

           - Must NOT be an SSAS Server Administrator

        - If an administrator account is used, RLS will be ignored and all data will be visible.

        - With this configuration, Dynamic RLS worked correctly using "{{UserId}}" without modifying the CustomSecuritySample.

         

        Hope this helps anyone facing a similar issue.