Connect to an SQL database using connection file?

[zeroms]

Good morning,

I have the following situation that I'd like some thoughts on if possible please, I'm pulling my hair out with Google searches for answers!

We provide a database solution to around 1000 clients, each of the clients have an onsite SQL server, the server names, instance names and database names will differ from client to client, although the database structure will always be the same.

When we create a PBIX file, we do this locally on our own network, with a development SQL server, when putting this onsite at a client, we need to change the datasource of the pbix file to look at their data, which means having PowerBI desktop on their server, and weeks of work to roll out, the issue is that doing this for 1000 clients just isn't possible, as updates to the PBIX would mean another full roll out.

The end user will only view the PowerBI dashboards in their browser and not in desktop (via a gateway or service, not sure on which to use on this yet any advice appreciated), but it seems that we would need desktop somewhere on their network in order to connect the datasource after install or update, as the pbix is still pointing at our dev instance, this just isn't an option.

Moving the data into Azure cloud is not an option due to the data being sensitive.

Is there any way to streamline this process and not have to update every clients pbix manually? 

Thanks in advance

[v-yangliu-msft · Accepted solution]

Hi  @zeroms ,

This may not work, although the database structure of each client is the same, but the SQL server, server name, instance name and database name are not the same, this is still equivalent to a separate data source for each client, power bi service does not support automatic conversion of credentials, this operation still needs to connect to Power BI Desktop to achieve.

If it is the same data source, you can also use dataflow to achieve, if they are separate, you need to connect to Power BI Desktop to use.

Creating a dataflow - Power BI | Microsoft Learn

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.