Connection string for Azure My SQL Database connection with power BI

[Shailesh]

How can we connect to Microsoft Azure My SQL Database?

[Anonymous · Accepted solution]

Hi @Shailesh,

I make a test using Power BI Desktop to connect to Azure MySQL database, we can directly connect to the database using Get Data->MySQL database option. Enter Server name and database name in the first screenshot, then choose Database authentication, enter username and password in the second screenshot.


You can check connection string(Server name, database name, username, password) of Azure MySQL database in Azure Portal following the instructions in this blog.

Thanks,
Lydia Zhang

[valterpcaldeira]

In this way, it's not possible to use schedule refresh right? I would like to use the schedule refresh without having the gateway because it's all Azure.

Best,

Valter

[Anonymous]

Hi Valter, I had a discussion with Microsoft support a while back about this subject and they told me it is not possible without the gateway connection even though it is all Azure. So you are correct on the scheduled refresh part indeed. This was very irritating for me as well, but basically, there are two options, at least that I know of.

Option 1: Setup an on-premises gateway compliant VM and tie your Azure MySQL into it using VNet. Possibly schedule the VM to only be turned on when you refresh. Then set up the connection through that. I have not used this implementation but I was told that it should be the workaround.

Option 2: Build your migration or data transfer to another database such as corresponding type Azure SQL Server, which supports the connection Power BI without having to use the gateway in between.

I had the choice of going with option 2, but in case you have a big infrastructure already working on top of that MySQL DB I would go with option 1 or building a pipeline from Azure MySQL to Azure SQL Server which is less than optimal. I don't know what could be the reason for this way of implementation, maybe something related to MySQL architecture, but never the less there is no option for direct connection at this stage, unfortunately.

[valterpcaldeira]

Thanks, Leeroy for the quick reply. 😉

Now I know. I also prefer the second solution (It make more sense in the long run). 

Thanks again for the help 😉

[VFXPro]

That is not the experience we're getting today.  I'm being prompted to install other components and constantly fail connecting to Azure for MySQL.  Very frustrating to connect to Azure for MySQL with Power BI or Dataflows at the moment...

The links to the components are broken, pointig to 32 bit instead of 64bit, the process is complex and messy... please fix.

[Anonymous]

I just tested this today, and this experience is still valid but in order for it to work you need to install Oracle MySQL Connector/NET --> https://dev.mysql.com/downloads/connector/net/
After this, you should be able to connect as described in this post.

If you are hesitant to use the link I provided it is also listed under Microsoft's own documentation here: https://docs.microsoft.com/en-us/power-query/connectors/mysqldatabase

[Shailesh]

Thanks @Anonymous Now I'm able to connect.

[Greg_Deckler]

I haven't used an Azure My SQL database but for an Azure SQL Database, in the Azure Portal you can get the Connection String information from there. Is this running as SaaS or IaaS?