How to login using AAD with MySQL Workbench?

Question

I am trying to figure out how to login to Azure Database for MySQL using AAD with MySQL Workbench, but this document, although it says it has been tested with Workbench, does not actually provide any details on how to do so: https://learn.microsoft.com/en-us/azure/mysql/howto-configure-sign-in-azure-ad-authentication

On a side note, I'm also trying to figure out how to add an AAD user/group to Azure Database for MySQL using Workbench, but again, I can't find any documentation on how to do so.

Any help would be very much appreciated.

Answer 1

@Michael MacGregor Thank you for responding back to me. I have followed the link Use Azure Active Directory for authentication with MySQL.

Using MySQL Workbench
Launch MySQL Workbench and Click the Database option, then click "Connect to database"
In the hostname field, enter the MySQL FQDN eg. mydb.mysql.database.azure.com
In the username field, enter the MySQL Azure Active Directory administrator name and append this with MySQL server name, not the FQDN e.g. user@tenant .onmicrosoft.com@mydb
In the password field, click "Store in Vault" and paste in the access token from file e.g. C:\temp\MySQLAccessToken.txt
Click the advanced tab and ensure that you check "Enable Cleartext Authentication Plugin"
Click OK to connect to the database

Using the command line the access token can be retrieved dynamically, but how is that done when using Workbench?
You can get that by typing the command az account get-access-token --resource https://ossrdbms-aad.database.windows.net in Azure cloud shell using CLI (bash)

Then you can use note pad to copy the Access token then save it to c drive and name it My access token example: C:\temp\MySQLAccessToken.txt
Also do not forget to Click the advanced tab and ensure that you check "Enable Cleartext Authentication Plugin" on Mysql Workbench

Please let me know if you are still not able to connect

Regards,
Oury

Answer 2

@Oury Ba-MSFT

Thanks for the answer, we did actually manage to figure that out.

Now we're trying to figure out how to add an AAD user using Workbench. I can do it using the mysql CLI, but not Workbench. To be clear we only want to use AAD Groups not individual users. In the CLI it's easy CREATE AADUSER 'QA';

If I try to specify the user as 'QA' and select aad_auth under Authentication Type, it throws an error. I've tried with quotes, with single quotes, without quotes, and nothing works. Any help with this would be appreciated.

Answer 3

Hi @Michael MacGregor

Adding this as it will be useful for other community members with the same issue.
This is a limitation on workbench and Azure creating AAD users. This is because workbench is not forced to implement that way for creating users and Microsoft is not forced too to create the AAD users as a normal USER as workbench does. We suggest using Azure CLI and SQL add users.

Regards,
Oury

Answer 4

NOTE: Azure AD user and group names are case sensitive

Also if you are getting error while getting access token, make sure to run get access token command from Azure CLI of windows (i.e. from your machine).

This solved problem for me.