Dynamic DataSources in Power BI Report Builder

[MeganBig]

Hello! We have many clients that each have their own sql server and database but the database structure is the same across all instances. Instead of building the same report for each client, we create one paginated report with a parameter that changes based on who is viewing the report. This has me researching how to dynamically change the data source on a sql query but I am running into a credential issue.

I am using power query inside power bi report builder to dynamically change the datasource. I initially set up the sql query with Server A and Database A. After I confirmed it was working accordingly, I converted the sql connections to use the parameters I created to dynamically change what server/database the sql query would run on.

Testing this out, I run for a client on Server A and Database A and the report works fine. I test running for the same client but in a different environment (Server B and Database B) and receive a credential error even though the credentials to access the two different servers/databases are the same. 

I have tried adding in multiple data source connections (Server A, Database A; Server B, Database B, etc) into the rdl file (through datasources, not power query), deploying to the service and running the report. I can still only get the report to successfully run when connecting to Server A, Database A. 

I have tried changing the authentication method of the power query to Service Principal inside the rdl file but continue to receive the credential error if the connection is not Server A, Database A.

I have tried changing the authentication method in the gateway and cloud connections setting of the report to Service Principal but receive the error below, even when connecting to Server A, Database A.

Is dynamically changing data sources in power bi report builder something that is even possible? Is there a way around the credential error that keeps occurring? Any insight or help towards a solution would be greatly appreciated.

Note: Moving to Power BI Desktop is not an option.

[MeganBig]

@GilbertQ I appreciate the response but RLS is not something we are struggling with. We already have a filtering solution based on who is using the report. It is an architectural decision to have each client on their own database and hoping we can find a solution on the reporting side to support that. 

[GilbertQ]

Hi @MeganBig 

Unfortunately as Power BI is currently designed there is no way to dynamically create a data source per customer. You might be able to automate it by using the REST API to create the data sources?

Gateways - Create Datasource - REST API (Power BI Power BI REST APIs) | Microsoft Learn

[GilbertQ]

Hi @MeganBig 

As far as I know it is not possible, it would be better to have all the data in a single database and then use RLS to filter based on the logged in user?