Unknown Host when connecting to MySQL Azure from Azure VM in PHP

Question

I have an Azure Windows Server 2016 VM and a database hosted in MySQL Azure. I have the connection information from the Azure portal, and can connect to the database from MySQL workbench on the VM, as well as through powershell. However, I'm struggling to get PHP to connect to the database when running via IIS. PHP from the command line is fine.

My web pages get the error:

PHP Warning: odbc_connect(): SQL error: [MySQL][ODBC 5.3(w) Driver]Unknown MySQL server host xxxx.mysql.database.azure.com' (0), SQL state S1000 in SQLConnect in C:\Program Files\xxxx\xyz.php on line 72

If I change my app pool user to the account I log into the VM as (a local admin account) then the above connect works fine. Its only when I use a non-admin account that I have this problem. I'm trying to narrow down what permissions and/or group memberships the app pool user account needs to successfully resolve the MySQL host name.

Does anyone have any thoughts?

Thanks,
Al.

Answer 1

@Zagato36 - I have currently had to put my app pool user in the local admins group on my web server, otherwise my web pages cannot connect to Azure for MySQL. That's not an ideal situation. I don't mind giving the account certain permissions it needs, but I'm at a loss as to what those might be.

I'm not using SSL to connect to MySQL - I'm porting an existing application onto the Azure infrastructure and want to do so with as few changes as possible. So I don't believe I'm using a certificate to connect to the database. My connection string is:

DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=xxxx.mysql.database.azure.com;DATABASE=mydatabase

I'm using odbc_connect in PHP to connect to the database.

It feels like the account that needs to connect to the Azure MySQL database needs permissions to look up the host name and connect to the internet, but I'm not sure how much sense that makes.

Any help and advice you could offer would be greatly appreciated.

Thanks,
Al.