Connect to a MySql In App database in Azure successfully
Sunday, April 17, 2022
TL;DR: The default connection string provided by Azure MySQL In App is unusable from .NET. Save yourself some time and convert it before use.
New discovery: the port number for the MySQL-instance for your app may change at any moment when the App Service is upgraded or moved. So make sure you parse the environment variable MYSQLCONNSTR_localdb to obtain the current port number. I'm assuming the user name and password don't change, but maybe you shouldn't assume that, either.
For a while now, Azure supports "MySQL in App" for App Services. In a nutshell, it's a free MySQL instance with some limitations, but very usable for small sites. Setting it up is simple: enable it by setting a switch to On and Azure does the rest. You even get a phpAdmin-instance to configure your server! Sounds like a good deal. More information here.
To help you connect to this free MySQL instance, Azure defines an environment variable called MYSQLCONNSTR_localdb that you're supposed to be able to use. Spoiler: it's not.
The connection string set in MYSQLCONNSTR_localdb has the following format:
Data Source=localdb;Server=127.0.0.1:49321;User Id=azure;Password=some_password
If you try to Open() a MySqlConnection using this connection string will get an error message "No such host". I tried for hours to change my code to get it to work but it won't ever. The correct connection string is:
Server=127.0.0.1;Port=49321;Database=localdb;Uid=azure;Pwd=some_password
In other words:
- Don't use Data Source=, but Server=
- Split the port number (here: 49312) and the colon off into Port=
- Change UserId= into Uid= and Password= into Pwd=
Maybe only some of these steps are absolutely necessary, but this particular transformation works for me ;-)
You're welcome.
Update: I've found that User ID and Password work just fine, so I've resorted to changing Data Source=127.0.0.1: to Server=127.0.0.1;Port=