PowerQuery Firewall Error

[WishAskedSooner]

I need someone to talk me off the ledge. MICROSOFT!!!!!! Why is this so badly broken??????

Ever since I started building PowerBI dashboards, I will occasionally encounter the dreaded "Firewall Error" in PowerQuery that tells you to rebuild your query. I have done endless research on how to overcome this error without results.

Anyway, I recently built a dashboard that was pointed to an on-premise SQL Server database. Now, that development is ready for deployment, I am trying to point it to an Azure database. However, I keep getting the "Firewall Error." I am at my wits end on what to do because it seems like the only solution is to rebuild the entire data model from scratch which I obviously don't want to do.

Please someone tell me, is there a solution to this?

[hnguy71 · Accepted solution]

Hi @WishAskedSooner 

Sorry to hear that! Do you mind posting a screenshot of your error?

Also, a few things to consider:

1. There's a firewall setting within Azure. Make sure that you have your IP whitelisted from there.

2. If your queries are complex and have multiple references or buffers, try to fold everything back into the SQL database. 

3. Try changing your privacy settings. Check both Global and Report level settings and set them both to "ignore privacy levels"

4. And, lastly, Power BI does not like dynamic parameters. If you are calling an API, try to call it a different way to make it more "static"

hope that at least helps you a bit!

[Anonymous]

Your solution is so great hnguy71 and lbendlin 

Hi, @WishAskedSooner 

If you're having firewall issues connecting to Azure SQL Server, you need to configure Azure's firewall so that you can access it from your computer or IP. To do this, I've found several articles that focus on how to connect to Azure in Power BI, which covers Azure's firewall configuration:

Create Power BI Connection to Azure SQL Database - MSSQLTips.com

Best Regards

Jianpeng Li

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

[lbendlin · Accepted solution]

Go through Ben Gribaudo's Primer.  Go through this  https://learn.microsoft.com/en-us/power-query/data-privacy-firewall

Use single partitions.  Avoid merges. Hide dynamic queries as @hnguy71 suggested.

[hnguy71 · Accepted solution]

Hi @WishAskedSooner 

Sorry to hear that! Do you mind posting a screenshot of your error?

Also, a few things to consider:

1. There's a firewall setting within Azure. Make sure that you have your IP whitelisted from there.

2. If your queries are complex and have multiple references or buffers, try to fold everything back into the SQL database. 

3. Try changing your privacy settings. Check both Global and Report level settings and set them both to "ignore privacy levels"

4. And, lastly, Power BI does not like dynamic parameters. If you are calling an API, try to call it a different way to make it more "static"

hope that at least helps you a bit!