Failed to publish Power BI Report (DirectQuery + Gateway)

[Sue777]

The issue: 

1. When I'm attempting to publish the .pbix file (data load with direct query) through the Azure DevOps pipeline, the Power BI Service returns a BadRequest error. This error occurs even after updating the SQL Server and database parameters to the client’s specified gateway-compatible values.

2. I also notice that, the .pbix still reference the on-prem servers that I used to test locally, they exist in the data models. The thing is I cannot connect to client's SQL server from my device, so PowerBI desktop throws error that wouldn't let me apply the changes, save without apply didn't work. The old connections remain in the file. 

I tried with one BI report, where I change all the direct query into import mode, then it works and successfully published. But I'd better keep the direct query bc in some reports the dataset can be very large. Is there any way that I can update the Sql server / database parameters withouth actually connecting? 

Thank you for your time and attention. 

[v-bmanikante]

Hi @Sue777,

we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
B Manikanteswara Reddy

[v-bmanikante]

Hi @Sue777,

we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
B Manikanteswara Reddy

[v-bmanikante · Accepted solution]

Hi @Sue777 ,

Please ensure that you have access to the client server. Without appropriate access, you may not be able to modify the gateway or update parameters as expected.

If you do not have access, kindly reach out to the client and request the necessary permissions. Without access, we will not be able to make changes to the server details on the on-premises data gateway.

Since you cannot connect to the client's SQL server from your device, updating the SQL server/database parameters directly in Power BI Desktop is challenging. Here are some potential solutions:

-->Create a parameter table in your Power BI model where you can store the SQL server and database parameters. Use these parameters in your queries instead of hardcoding the server names. This way, you can update the parameters without connecting to the SQL server.

If these workarounds don't help, I suggest reaching out to Microsoft Support by raising a ticket. Microsoft will analyze backend logs and provide a resolution

Below is the link to create Microsoft support ticket:
https://learn.microsoft.com/en-us/power-bi/support/create-support-ticket

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
B Manikanteswara Reddy

[Sue777]

Hey, sorry for the late reply! The issue is still not solved yet. We decided to seek an alternative way. Thank you. 

[v-bmanikante]

Hi @Sue777,

we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
B Manikanteswara Reddy

[Anonymous]

Hi @Sue777,

Thank you for reaching out to Microsoft Fabric Community Forum.

It looks like the issue due to outdated SQL Server Connections

Try to update the Source(SQL Server and Database) parameters in Power Query only. Then save and publish via Azure DevOps Pipeline.

If you are still not able to getting the correct parameters, try to change the connection in Power BI service by updating the server and database to match the client’s gateway settings. Refresh the dataset and check if it works.

If Power BI Desktop doesn’t let you save without applying changes, use Tabular Editor or PBI Tools CLI to manually update the data source settings in the .pbix file

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Vinay Pabbu

[Sue777]

Hi, thank you for the reply. I tried with install Tabular and use it as an externel tool in my power BI desktop, but it seems that I cannot edit the data source, and it's empty. 

I looked into Shared Expressions, SQL Server and Database in the expression editor are like below:  even if I have already editted in the power query (transform data). 

"still old version" meta [IsParameterQuery=true, List={"still old version"}, DefaultValue="still old version", Type="Text", IsParameterQueryRequired=true]

I tried to edit in the expression editor in Tabular but it cannot be saved. The Options are grey. 

Any ideas? 

Thank you so much.