Dynamic Connection & Query Execution Across Multiple Databases in Azure SQL Server

[sevenhills]

Subject: Dynamic Connection & Query Execution Across Multiple Databases in Azure SQL Server

Hi,

We are having Azure SQL Server across Dev, QA, and Prod environments, with approximately eight databases in each instance.

These databases operate in isolation and cannot directly reference or communicate with one another.

I have a Version table that contains a single row tracking deployment details, version information, and other metadata. My goal is to retrieve and append this data across all databases for consolidated display.

While I can successfully connect to each server and database individually, execute queries, and append the results, I am facing an issue in Azure SQL when attempting a dynamic connection adjustment using a function.

On-premises, I can dynamically adjust connections using a function, passing parameters to retrieve data seamlessly. However, this approach does not seem to work in Azure SQL.

https://radacad.com/change-the-source-of-power-bi-datasets-dynamically-using-power-query-parameters

Has anyone encountered a similar challenge or found a workaround for dynamically connecting to multiple databases in Azure SQL Server?

Any insights would be greatly appreciated.

Thanks

[v-echaithra]

Hi  @sevenhills ,

Power BI currently does not support dynamic data source references for cloud sources like Azure SQL Database unless the server and database names are hardcoded, or the query is routed through a gateway with pre-configured credentials.

The main limitation is that Power Query's Sql.Database function does not handle dynamic connection parameters for cloud sources the same way it does for on-premises sources especially when those parameters are used inside a function or driven by a parameter table.

I recommend submitting your detailed feedback and ideas through Microsoft's official feedback channels, such as the Microsoft Fabric Ideas.  

[v-echaithra]

Hi @sevenhills ,

Thanks for the update. 
As it worked with fabric warehouse, please try for azure SQL.

let
    Dev = Sql.Database("x6eps4xrq2xudenlfv6naeo3i4-wwtdsvdlvx2elaqgwcxhcqgmly.msit-datawarehouse.fabric.microsoft.com", "WH123", [Query="select * from SALES"]),
    QA = Sql.Database("x6eps4xrq2xudenlfv6naeo3i4-wwtdsvdlvx2elaqgwcxhcqgmly.msit-datawarehouse.fabric.microsoft.com", "WH456", [Query="select * from SALES"]),
    AllVersions = Table.Combine({Dev, QA})
in
    AllVersions

If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.

Regards,
Chaithra

[sevenhills]

I'm not sure if you had a chance to fully read my original post—I’ve already tried the approach using individual server connections and queries. It is already done! 

What I’m specifically looking for is guidance on using functions within Azure SQL Server and dynamic connections.

I mentioned that I can already use functions with on-premise SQL Servers, so the goal here is to understand any viable options or limitations in the Azure context.

I appreciate your effort and sample file to solve my issue. Please don’t take this the wrong way, but I’m not quite sure how your latest response addresses my question.

I'd prefer to keep the thread open in case others have insights—either now or in the future.

Regards

[v-echaithra]

Hi @sevenhills ,

Please try the below workaround for Dynamic connection & Query Execution across multiple databases in Azure SQL Server.
Rather than relying on dynamic connections within Power BI Desktop, leverage Azure Data Factory or Fabric Pipelines to extract the version table from each database, store the results in a staging table within a consolidated SQL database or Lakehouse, and then direct Power BI to that centralized table. This approach eliminates cross-database dynamic connections within Power BI, aligning seamlessly with a governed enterprise architecture.

If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.

Regards,
Chaithra

[sevenhills]

Sorry to say that I am interested only in the dynamic connections using functions and not the other solutions for now.

Like I said we have multiple Power BI workspaces and data flows, but we don’t want to use them for this—we need a real-time solution. That approach isn’t suitable for this version’s metadata comparison exercise.

As per you, you dont have a solution. I understand that it is not available per you. 

I will keep the thread as open and see others reploy or other solutions. 
Thank you.

[v-echaithra]

Hi @sevenhills ,

I meant defining a function that allows you to pass database names dynamically as parameters to retrieve data from multiple databases.
Loop through the database names dynamically and apply the function to fetch and append data across all databases.

On-prem, you can dynamically switch connections using functions, but in Azure SQL, this approach faces limitations due to Security and isolation, cross-database querying require explicit definitions, sometimes also because of privacy and connector behavior in Power Query.

You can try by Parameterized Power Query approach by defining parameters for database names and dynamically referencing them while ensuring privacy levels, set to ignore in Power BI.

Other possible workaround is by using Dataflows:
Create separate queries for each database and consolidate them using Power BI Dataflows.

If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.

Regards,
Chaithra

[sevenhills]

So, in other words, this isn’t feasible in Azure SQL Server. Hmm... Interesting.

As I mentioned, I implemented this on-prem, and it has been running without issues for quite some time. If you have an example that demonstrates a workaround using Azure SQL Server, I’d be happy to take a look.

We have multiple Power BI workspaces and data flows, but we don’t want to use them for this—we need a real-time solution. That approach isn’t suitable for this version’s metadata comparison exercise.

Thanks

[v-echaithra]

Hi @sevenhills ,

The issue is that Power Query’s data privacy and connector behaviors differ between on-prem and cloud sources, particularly with dynamic data source references. This is mostly because Power Query’s strict data privacy levels, which try to prevent accidental data leakage across sources, Native Query folding limitations with dynamic parameters in cloud sources.

Try by using Power Query with Parameterized Connections

While dynamic connection switching works on-premises, Azure SQL restricts direct dynamic connection adjustments.

Instead, you can create a parameterized function in Power Query that accepts database names and dynamically retrieves data, also ensure that Privacy Levels in Power BI are set to Ignore to allow cross-database queries.
Reference: Azure SQL Database with DirectQuery - Power BI | Microsoft Learn

If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.

Regards,
Chaithra

[sevenhills]

I did the same way for on-premise and it works! 

Let us go with your input/reply.  What do you mean by?
" Instead, you can create a parameterized function in Power Query that accepts database names and dynamically retrieves data, also ensure that Privacy Levels in Power BI are set to Ignore to allow cross-database queries. "

Do you have example with tested for azure sql servers ? Thanks in advance!