Power BI to Azure SQL MI Connection via Power Query Parameters

[Mahee]

Hi All,

Good Day!

We have a Power Query parameter for Server (Azure SQL MI) and Database. Once we create and try to save it with credentials, It is throwing an error. But the same server and Database can be accessed via SSMS with the same username/pwd.

I am just trying to understand is there any limitation in Power BI for this scenario when connecting to Azure SQL MI via parameters. Pls be noted that the same scnaio is working when we trying to connect with onpremise server.

Kindly advise.

Thanks!

[HarishKM]

@Mahee Hey,
Try below method 

Azure SQL requires your client IP to be whitelisted in the Azure SQL server firewall settings.

Even if credentials are correct, connection will fail if IP is not allowed.

Double-check that the machine running Power BI Desktop or the gateway is allowed through firewall rules.

Thanks

Harish M

Please accept this as a solution if this solves your problem and give kudos as well.

[v-sgandrathi]

Hi @Mahee,

Thank you for using Microsoft Fabric Communtiy Forum.

As correctly pointed out by both @rohit1991  and @anilelmastasi Power BI currently does not support storing credentials or scheduled refresh when server and database names are passed as dynamic parameters in the connection string (e.g., Sql.Database(ServerParam, DatabaseParam)), especially for Azure SQL Managed Instance.

This behavior is by design, and is related to how Power BI handles dynamic data source references, which can’t be securely bound to credentials unless a Gateway is configured with fixed metadata.

As a recommendation:
1. Use static values for server and database in the connection string:

let
    Source = Sql.Database("your-server.database.windows.net", "your-db")
in
    Source

2. Use parameters only after the source is established  for filtering tables, schema, or queries.

3. If a Gateway is used, ensure the data source in the Gateway matches the actual server/database names (static).

4. If you need to change the server/database, do it in Power BI Desktop, update the parameter values, then re-publish and re-authenticate.

I hope this helped! Feel free to ask any further questions.

Thank you.

[v-sgandrathi]

Hi @Mahee,

Just checking in -- have you had a chance to review and try the provided solution? Kindly share the status whenever you get a chance.

Looking forward to your response
Thank you.

[v-sgandrathi]

Hi @Mahee,

.Just a quick check-in! Has your issue been resolved with the information we shared? We’d be delighted to help further if needed.

Thank you.

[rohit1991]

Hi @Mahee 

Yes, this is a known limitation in Power BI. When using Power Query parameters for Azure SQL MI (like passing server or database name dynamically), Power BI can't bind credentials properly  that's why the connection fails. It works in SSMS because it doesn’t have this restriction.

To confirm, try hardcoding the server and database  if that works, the issue is with dynamic parameters. As a workaround, avoid passing parameters directly into Sql.Database() for cloud sources like Azure SQL MI.

[anilelmastasi · Accepted solution]

Hello @Mahee ,

Power BI does not allow credentials to be saved for dynamic data source references (i.e., server/database coming from parameters), unless you're using an on-prem gateway. This is why it works for on-prem SQL (via Gateway) but not for Azure SQL MI without one.

When you use Power Query parameters directly in your SQL Server connector like this:
Sql.Database(ServerParam, DatabaseParam)

Power BI considers this a "dynamic data source", and dynamic data sources prevent credential storage or scheduled refresh in many cases.

Limitation: Power BI cannot associate credentials with a dynamic value for server/database unless it's marked as a gateway allowed data source with fixed metadata.

To avoid dynamic binding issues:

Instead of:
Sql.Database(ServerParam, DatabaseParam)

Use:
let
Source = Sql.Database("your-server.database.windows.net", "your-db-name")
in
Source

Then use parameters only within queries (e.g., for schema/table filtering), not for the source connection string.

 If this solved your issue, please mark it as the accepted solution. ✅