Forum Discussion

Bhavik13101991's avatar
Bhavik13101991
New Member
4 days ago

Power BI Embedded: Last User's Database Parameter Affects All Users

Hi,

We have a Power BI report embedded in our .NET application. We use DirectQuery, and our SQL Server production environment contains around 1,800 databases. In Power BI, we pass a database parameter to connect the report to the appropriate district's database.

We also use an on-premises data gateway. The gateway data source connection is updated dynamically using the Power BI Update Datasource REST API. Based on the server and database values, we update the corresponding database parameter in the Power BI report.

Each user belongs to a specific district, and the report should display data only from their respective district/database.

We are facing an issue when multiple users log in at the same time.

For example, User A logs in from District A, and the report connects to the District A database and displays the correct data.

Then User B logs in from District B, and the gateway datasource and database parameter are updated for District B.

After User B logs in, User A also starts seeing District B data.

It appears that the latest updated gateway datasource/database parameter is being used by all users instead of maintaining a separate database connection for each user's embedded report session.

Our current architecture is Power BI Embedded with a .NET application, DirectQuery, an on-premises data gateway, and approximately 1,800 SQL Server databases in production. The gateway datasource is updated through the Power BI Update Datasource REST API based on the server and database required for the user.

What is the recommended approach for this architecture? How can we ensure that each user's embedded Power BI report independently connects to the correct database without affecting other concurrent users?

Would Dynamic RLS, effective identity, separate datasets/datasources, or another Power BI Embedded approach be appropriate for this scenario?

Any guidance on how Power BI handles gateway datasource updates, parameters, and DirectQuery connections for multiple concurrent embedded users would be greatly appreciated.

Thanks!

Need Help

2 Replies

  • SamInogic's avatar
    SamInogic
    Super User
    3 days ago

    Hi,

     

    What you're seeing is expected with the current architecture. The main issue is that the gateway data source and database parameter belong to the shared semantic model, not to an individual embedded user's session.

     

    So when User A connects and you configure the model for Database A, it works correctly. But when User B comes in and you update the same data source/parameter to Database B, you are changing the configuration used by the shared model. Power BI does not create a separate Direct Query database connection for User B.

     

    That's why the behavior can look like:

     

    User A → Shared Model → Database A

    User B → Updates Model → Database B

    User A → Shared Model → Database B

     

    The Update Data source API updates the gateway data source configuration; it should not be used as a per-user database-switching mechanism.

     

    What I would recommend is if the 1,800 databases have the same schema, the better design would be to have a common reporting source and use Dynamic RLS with Effective Identity.

     

    Your .NET application can generate an embed token for each user and pass their district as the effective identity. RLS then filters the data for that district.

     

    Ex. User A → Embed Token (District A)

           → Same Semantic Model

           → RLS

           → District A data

     

          User B → Embed Token (District B)

           → Same Semantic Model

           → RLS

           → District B data

     

    This is much safer for concurrent users because you are not modifying the shared data source when another user logs in.

     

    One important distinction: RLS controls which rows the user can see; it does not switch the underlying SQL Server database. Therefore, if District A and District B must remain completely separate physical databases, RLS alone will not solve the problem.

     

    In that case, you would need an architecture where the database/model is isolated per district, for example separate semantic models/data sources and selecting the appropriate model for the user. With ~1,800 databases, though, maintaining 1,800 Power BI models could become difficult to manage.

     

    If possible, I would consider introducing a centralized reporting layer that combines the district data and includes a District/Tenant ID. Then Power BI can use one semantic model with Dynamic RLS + Effective Identity.

     

    So in short:

    • Do not update the shared gateway data source for every user login.
    • Do not use a shared database parameter as a per-user session setting.
    • If the data can be consolidated → Dynamic RLS + Effective Identity is the preferred approach.
    • If databases must remain separate → consider isolated semantic models/data sources and model routing/binding.

     

    The key point is that a Power BI Embedded session does not give each user an independent gateway data source configuration. The data source/model configuration you're changing is shared, which is why the last update can affect other users.

     

    Hope this Helps!!