Power BI cannot authenticate MySQL user granted from role

[omiajpy]

Hello,

As described in the subject, Power BI Desktop cannot authenticate if the user created was granted privileges from a role in MySQL.

After I granted the user privileges directly, Power BI authenticated right away.

Is this a limitation on the connector or on Power BI?

Is there a way for this to work with roles assignment?

Thanks!

[FarhanJeelani · Accepted solution]

Hi @omiajpy ,

This is a known limitation in Power BI’s MySQL connector. Power BI currently requires that database users have directly assigned privileges for authentication and data access, rather than inheriting them through MySQL roles.

MySQL roles are a relatively newer feature, and many client applications (including Power BI) may not fully support role-based privilege inheritance. Power BI’s MySQL connector checks for privileges directly assigned to the user during the connection process. If privileges are granted via roles, Power BI fails to authenticate.

Workarounds:

1. Direct Privilege Assignment (Recommended): As you discovered, granting privileges directly to the user resolves the issue.
2. Use a Proxy User: You can create a dedicated MySQL user with direct privileges solely for Power BI connections.
3. Scripted Solution (Not Ideal): Automate privilege reassignment from roles to users using MySQL scripts if necessary.

Is there a way for this to work with roles? Unfortunately, as of now, there is no built-in support in Power BI’s MySQL connector for privileges assigned via roles. You will need to assign permissions directly to the user.

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