Power BI Service Connection Issue with Azure MySQL

[KeyurPatel14]

Hello Experts,

We hope you are doing well.

Issue:

We need your guidance on the error we are facing related to connecting the Azure MySQL database to the Power BI service. We have an Azure MySQL server:

xyz-mysql-server-1.mysql.database.azure.com

We were not able to connect and pull data from the server earlier when our IPs were not whitelisted by the client. Later, we successfully connected this server to Power BI Desktop and fetched the required data. 

However, after publishing the report to Power BI Service, the cloud connection fails with the below error.

Steps we took to investigate it:

To troubleshoot and check the version of MySQL, we ran the following query in Azure Data Studio:

SELECT @@hostname, @@version_comment, @@version;

We received the output as below:

Want your assistance on:

As per our analysis on the version 8.0.40-azure, we assume that it is hosted on Azure. So, we are unsure how to proceed to ensure a successful connection in Power BI Service, and are struggling to decide if we need a gateway or not, or is there any firewall rule applied to the server, and if yes, then how can we add Power BI IPs to it?

Could you please guide us on the necessary steps or configurations required to fix this issue? 

Waiting for your responses!!

Thank you!!

[v-hashadapu]

Hi @KeyurPatel14 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @KeyurPatel14 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @KeyurPatel14 , Thank you for reaching out to the Microsoft Community Forum.

Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @KeyurPatel14 , Thank you for reaching out to the Microsoft Community Forum.

I agree with @Akash_Varuna , your Azure MySQL database requires IP whitelisting, which means Power BI Service needs its own IP ranges added to the firewall. Since the database is accessible over the internet (with firewall controls), you do not need an on-premises data gateway.

You can download Microsoft’s latest Azure IP Ranges and Service Tags from Azure IP Ranges and Service Tags – Public Cloud . Locate the IPs for your specific region, add these IPs in Azure Database for MySQL -> Networking -> Firewall rules. Since Microsoft updates this file weekly, check periodically for changes.

Power BI Service enforces SSL for MySQL connections unless explicitly disabled. Ensure SSL is enabled in Power BI’s connection settings. Ensure the credentials used in Power BI Service have read access to the database.

Thank you @Akash_Varuna , for your continious efforts in helping the community.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[Akash_Varuna · Accepted solution]

HI @KeyurPatel14 Could you please check for this

• Gateway: If the Azure MySQL database is publicly accessible, you don't need a gateway. Otherwise, use an on-premises data gateway.

• Firewall: Add Power BI Service IP ranges for your Azure region to the MySQL firewall. Check Microsoft's documentation for IP ranges.

• SSL: Ensure SSL is enabled in Power BI's connection settings.

• Permissions: Confirm credentials have the necessary access.

[KeyurPatel14]

Hello @Akash_Varuna ,

• Gateway: If the Azure MySQL database is publicly accessible, you don't need a gateway. Otherwise, use an on-premises data gateway.

Could you please confirm what you mean by publicly accessible?
By publicly accessible, I understand that anyone can pull the data from the server without any issue(IP whitelisting, firewall rules management, etc.). If my understanding is correct, then, the server is not publicly accessible as we have whitelisted our IPs, as we were also not able to connect to it earlier. 

Looking forward to your response.

[Akash_Varuna]

Hi @KeyurPatel14 The Azure MySQL database can be reached over the internet without private network restrictions. Since your server requires IP whitelisting, it is not broadly public but has controlled internet exposure.
I have already posted the reply for the JSON one and Ip whitelisting

[KeyurPatel14]

Hello @Akash_Varuna,

I appreciate your response.

As per our analysis, Azure MySQL is on the cloud and publicly accessible. However, as I mentioned in the post, it was inaccessible earlier when our IPs were not whitelisted. So, I feel that though it is on the cloud, there might be some firewall rules applied on Azure. But I am not fully sure.
I am facing issues on adding the Power BI service's IPs in the firewall rule, if any rules exist for the server.

[Akash_Varuna]

Hi @KeyurPatel14 I think that is being updated regularly did you check the docummnetation for Power BI IP Address so refer to Microsoft's Azure IP Ranges and Service Tags for the latest list.

[KeyurPatel14]

Hello @Akash_Varuna ,
I have also checked this list. While downloading this JSON file, they mentioned that it would be updated weekly.
So, I just want to make sure whether they are talking about updating the IPs or just adding IPs for new regions?

Additionally, I want to confirm one thing here, let's assume that the IPs for one specific region remain the same, so to access the server, we will need to add IPs of the client's region(PowerBI.CanadaEast, etc.) to the firewall, right?

Please confirm if you have come across such a scenario.

[Akash_Varuna]

Hi @KeyurPatel14 The JSON update is likely to be updating existing IP addresses with the new one.For accessing a server, you will need to whitelist the specific Power BI region's IPs (e.g., PowerBI.CanadaEast) in the firewall based on the client’s region.This ensures integartion with Power BI services in that region
For more information please check this post : https://community.fabric.microsoft.com/t5/Service/API-requires-Power-Bi-Ip-address-range-to-Whitelis...
If this post helped please do give a kudos and accept this as a solution
Thanks In Advance