Forum Discussion
Gateway datasource limit breaking ISV use case
Thanks lbendlin for the feedback.
I also found the personal gateway thing weird. You can't even create a cluster of gateways in personal mode, as far as I tried.
Be aware that if your databases are all on the same instance, and your account has access to all databases then you can use a single connection, and then override that in the custom query by connecting to a different database.
How does this exactly work? You bind multiple datasets to the same gateway datasource? I tried that, but it gives me a credentials error (ModelRefreshFailed_CredentialsNotSpecified) when I try to refresh the second dataset. Both datasets connect to different databases on the same MySQL database server but the user making the connection has rights on both databases.
Hi Piloos , Thank you for reaching out to the Microsoft Community Forum.
In Power BI, if multiple databases are present on the same MySQL instance, you can link multiple datasets to a single gateway datasource by using dynamic database switching in the query. This is possible if the gateway datasource is set to point to the MySQL instance rather than a specific database, and the Power Query (M) or SQL statement dynamically references the necessary database.
Please try the following steps:
- In Power BI Service, go to Manage Gateways and create one gateway datasource that connects to the MySQL server instance. Ensure the credentials used have access to all target databases.
- Instead of directly connecting to a specific database, modify your Power Query (M) script to dynamically specify the database.
- The ModelRefreshFailed_CredentialsNotSpecified error occurs because Power BI stores credentials at the datasource level. If different datasets override the database dynamically, Power BI may not automatically pass credentials. To resolve this, Go to Power BI Service -> Settings -> Dataset Settings. Locate the dataset that fails to refresh. Ensure it’s using the same credentials as the gateway datasource. Set the authentication method to Basic or OAuth (depending on your setup). Save and apply the changes.
- If Power BI still fails to refresh, consider using DirectQuery mode instead of Import mode. DirectQuery allows Power BI to send queries dynamically without needing separate refresh credentials for each dataset. If your data volumes are manageable, switching to DirectQuery may eliminate the need for individual refreshes.
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.Hi v-hashadapu
In Power BI, if multiple databases are present on the same MySQL instance, you can link multiple datasets to a single gateway datasource by using dynamic database switching in the query. This is possible if the gateway datasource is set to point to the MySQL instance rather than a specific database, and the Power Query (M) or SQL statement dynamically references the necessary database.
Sounds like a plan. I tried it out, but I couldn't manage to create a MySQL datasource without specifying the database. Any clue how to achieve this? Before I start doing this using the REST API, I first want to validate the solution manually in ther service. If I create a gateway datasource there, the database is a required field.
Could it have something to do with the Power Query statement?
Source = MySQL.Database(db_server, db_name, [ReturnSingleDatabase=true, CreateNavigationProperties=false]),Hi Piloos , Thank you for reaching out to the Microsoft Community Forum.
Please try this:
- When setting up the gateway data source, specify any valid database as a placeholder. Ensure that the user has privileges to access all required databases on the MySQL instance.
- Modify the Power Query (M) script to dynamically switch databases. Example:
let
db_server = "your-mysql-server",
db_name = "dynamic_db_name", // Replace dynamically
Source = MySQL.Database(db_server, db_name, [ReturnSingleDatabase=true, CreateNavigationProperties=false])
in
Source
- Ensure the dataset credentials match those set in the gateway.
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.
