Unable to Connect to a Third Party Remote MySql Database

[StephenCockram]

We have recently moved our ERP solution to a cloud provider and are trying to link to their MySQL database to our Power BI reporting.

We have successfully done this using Power BI Desktop using the MySQL connector (which we had to download seperately) but it required them to whitelist our IP Address.  We have then published this report to Power BI Services and get errors like

"Failed to update data source credentials: An error happened while reading data from the provider: 'One or more errors occurred.

We've been searching for a solution that will allow us to publish our PBI Desktop reports that use this 3rd party database but are not sure how to get this through.  We looked at whitelisting IP addresses but the list of IP addresses for Power BI seems extensive and constantly changing, so that seems impractical.  For our needs we have an On-Premises data gateway to access our SQL Server but we cannot get a link between them installing an On-Premises data gateway and our Power BI accounts as they sit on different domains/tenants, we believe the only solution to this would be to establish a trust relationship which our domain admins have no intention of doing.

Are there any solutions we may have overlooked?

[v-karpurapud]

Hi @StephenCockram 

Thanks for reaching out to Microsoft Fabric Community Forum.

Regarding your query about the connection to third party MySQL Database, We just wanted to confirm that the solution provided by @FarhanJeelani  is accurate and should help to resolve the issue you're facing.

Additionally ,here are  few more points to consider.

Using MySQL Connector in Power BI Service

For MySQL databases not hosted on-premises, consider installing a self-hosted data gateway in your network or cloud environment. This enables Power BI Service to connect to your MySQL database even if it's in a different domain or cloud provider.

Utilizing Dataflows

Another approach is to use Power BI Dataflows to transform data before publishing it to Power BI Service. Connect to the MySQL database using Power BI Desktop, transform the data, and then save it to a Power BI dataflow.

This method avoids the need for direct MySQL connections in Power BI Service by using a more flexible data storage option.

Alternative Authentication Options:

If your MySQL service supports OAuth or other authentication methods, configure Power BI to use those methods, potentially bypassing the IP whitelist issue.

If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.
Thank You.

[FarhanJeelani · Accepted solution]

Hi @StephenCockram ,

Problem Analysis:

1. Power BI Service requires a consistent and secure connection to data sources, unlike Power BI Desktop.
2. The challenge lies in the dynamic nature of Power BI Service IP addresses and the absence of domain/tenant trust between your setup and the third-party provider.

Potential Solutions:

1. Use an On-Premises Data Gateway (Recommended)

• Why? The gateway can act as a bridge between the third-party MySQL database and Power BI Service without needing direct IP whitelisting for Power BI Service.
• Steps:
  1. Install the On-Premises Data Gateway on a VM or machine that can connect to the MySQL database.
  2. Configure the gateway with the credentials to connect to the MySQL database.
  3. In Power BI Service, configure your dataset to use this gateway for scheduled refreshes.
• Considerations: Ensure the VM/machine hosting the gateway has a static IP, which can be whitelisted by the MySQL provider.

2. Leverage a Static Public IP via VPN or Bastion Host

• Why? A static public IP for the connection will ensure the IP is predictable for whitelisting by the third-party database provider.
• Steps:
  1. Set up a VPN or a bastion host to route traffic from Power BI Service via a static IP.
  2. Whitelist this IP with the third-party provider.
• Considerations: This setup requires network configuration expertise and possibly additional infrastructure costs.

3. Set Up a Proxy Server

• Why? A proxy can standardize and manage connections between Power BI Service and the MySQL database.
• Steps:
  1. Deploy a proxy server that can handle incoming requests from Power BI Service.
  2. Configure the proxy to use a whitelisted static IP for the MySQL connection.
• Considerations: Proxies can add latency but are a robust solution for managing connections.

4. Explore Using Azure as a Middle Layer

• Why? Azure can serve as a cloud-based intermediary with static IPs, bridging Power BI Service and the MySQL database.
• Steps:
  1. Set up an Azure MySQL Database as a replica of the third-party database (if replication is possible).
  2. Use Power BI Service to connect to the Azure database.
• Considerations: This requires syncing data between the original database and Azure.

5. Direct Query with Custom IP Management

• If Power BI Service IP whitelisting is the only option:
  1. Monitor Power BI’s updated list of IP ranges: Power BI IP Ranges.
  2. Automate the whitelisting process via scripts to adjust IP ranges dynamically in the third-party database firewall.

Recommendation:

• The On-Premises Data Gateway is the most practical and secure solution for your scenario. It avoids dynamic IP issues while enabling scheduled refreshes seamlessly.
• If domain/tenant restrictions block this, consider the Azure middle layer approach for a cloud-native solution.

Please mark this as solution if it helps you. Appreciate Kudos.

[StephenCockram]

We had attempted solution 1 but it hadn't worked.  Upon your advise we revisted it and found that we needed to use the "On-Premises" connection type where we were trying to use the "Cloud" connection type.