Use Microsoft Entra ID for authentication with MySQL

Article
06/20/2022

APPLIES TO: Azure Database for MySQL - Single Server

Important

Azure Database for MySQL single server is on the retirement path. We strongly recommend that you upgrade to Azure Database for MySQL flexible server. For more information about migrating to Azure Database for MySQL flexible server, see What's happening to Azure Database for MySQL Single Server?

This article will walk you through the steps how to configure Microsoft Entra ID access with Azure Database for MySQL, and how to connect using a Microsoft Entra token.

Important

Microsoft Entra authentication is only available for MySQL 5.7 and newer.

Setting the Microsoft Entra Admin user

Only a Microsoft Entra Admin user can create/enable users for Microsoft Entra ID-based authentication. To create a Microsoft Entra Admin user, please follow the following steps

In the Azure portal, select the instance of Azure Database for MySQL that you want to enable for Microsoft Entra ID.
Under Settings, select Active Directory Admin.
Select a valid Microsoft Entra user in the customer tenant to be Microsoft Entra administrator.

Important

When setting the administrator, a new user is added to the Azure Database for MySQL server with full administrator permissions.

Only one Microsoft Entra admin can be created per MySQL server and selection of another one will overwrite the existing Microsoft Entra admin configured for the server.

After configuring the administrator, you can now sign in:

Connecting to Azure Database for MySQL using Microsoft Entra ID

We’ve designed the Microsoft Entra integration to work with common MySQL tools like the mysql CLI, which are not Microsoft Entra aware and only support specifying username and password when connecting to MySQL. We pass the Microsoft Entra token as the password.

We currently have tested the following clients:

MySQLWorkbench
MySQL CLI

We have also tested most common application drivers, you can see details at the end of this page.

These are the steps that a user/application will need to do authenticate with Microsoft Entra ID described below:

Prerequisites

You can follow along in Azure Cloud Shell, an Azure VM, or on your local machine. Make sure you have the Azure CLI installed.

Step 1: Authenticate with Microsoft Entra ID

Start by authenticating with Microsoft Entra ID using the Azure CLI tool. This step is not required in Azure Cloud Shell.

az login

The command will launch a browser window to the Microsoft Entra authentication page. It requires you to give your Microsoft Entra user ID and the password.

Step 2: Retrieve Microsoft Entra access token

Invoke the Azure CLI tool to acquire an access token for the Microsoft Entra authenticated user from step 1 to access Azure Database for MySQL.

Example (for Public Cloud):

az account get-access-token --resource https://ossrdbms-aad.database.windows.net

The above resource value must be specified exactly as shown. For other clouds, the resource value can be looked up using:

az cloud show

For Azure CLI version 2.0.71 and later, the command can be specified in the following more convenient version for all clouds:

az account get-access-token --resource-type oss-rdbms

Using PowerShell, you can use the following command to acquire access token:

$accessToken = Get-AzAccessToken -ResourceUrl https://ossrdbms-aad.database.windows.net
$accessToken.Token | out-file C:\temp\MySQLAccessToken.txt

After authentication is successful, Microsoft Entra ID will return an access token:

{
  "accessToken": "TOKEN",
  "expiresOn": "...",
  "subscription": "...",
  "tenant": "...",
  "tokenType": "Bearer"
}

The token is a Base 64 string that encodes all the information about the authenticated user, and which is targeted to the Azure Database for MySQL service.

The access token validity is anywhere between 5 minutes to 60 minutes. We recommend you get the access token just before initiating the login to Azure Database for MySQL. You can use the following PowerShell command to see the token validity.

$accessToken.ExpiresOn.DateTime

Step 3: Use token as password for logging in with MySQL

When connecting you need to use the access token as the MySQL user password. When using GUI clients such as MySQLWorkbench, you can use the method described above to retrieve the token.

Using MySQL CLI

When using the CLI, you can use this short-hand to connect:

Example (Linux/macOS):

mysql -h mydb.mysql.database.azure.com \ 
  --user user@tenant.onmicrosoft.com@mydb \ 
  --enable-cleartext-plugin \ 
  --password=`az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken`

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 Microsoft Entra 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

Important considerations when connecting:

user@tenant.onmicrosoft.com is the name of the Microsoft Entra user or group you are trying to connect as
Always append the server name after the Microsoft Entra user/group name (e.g. @mydb)
Make sure to use the exact way the Microsoft Entra user or group name is spelled
Microsoft Entra user and group names are case sensitive
When connecting as a group, use only the group name (e.g. GroupName@mydb)
If the name contains spaces, use \ before each space to escape it

Note the “enable-cleartext-plugin” setting – you need to use a similar configuration with other clients to make sure the token gets sent to the server without being hashed.

You are now authenticated to your MySQL server using Microsoft Entra authentication.

Creating Microsoft Entra users in Azure Database for MySQL

To add a Microsoft Entra user to your Azure Database for MySQL database, perform the following steps after connecting (see later section on how to connect):

First ensure that the Microsoft Entra user <user>@yourtenant.onmicrosoft.com is a valid user in Microsoft Entra tenant.
Sign in to your Azure Database for MySQL instance as the Microsoft Entra Admin user.
Create user <user>@yourtenant.onmicrosoft.com in Azure Database for MySQL.

Example:

CREATE AADUSER 'user1@yourtenant.onmicrosoft.com';

For user names that exceed 32 characters, it is recommended you use an alias instead, to be used when connecting:

Example:

CREATE AADUSER 'userWithLongName@yourtenant.onmicrosoft.com' as 'userDefinedShortName';

Note

MySQL ignores leading and trailing spaces so user name should not have any leading or trailing spaces.
Authenticating a user through Microsoft Entra ID does not give the user any permissions to access objects within the Azure Database for MySQL database. You must grant the user the required permissions manually.

Creating Microsoft Entra groups in Azure Database for MySQL

To enable a Microsoft Entra group for access to your database, use the same mechanism as for users, but instead specify the group name:

Example:

CREATE AADUSER 'Prod_DB_Readonly';

When logging in, members of the group will use their personal access tokens, but sign with the group name specified as the username.

Token Validation

Microsoft Entra authentication in Azure Database for MySQL ensures that the user exists in the MySQL server, and it checks the validity of the token by validating the contents of the token. The following token validation steps are performed:

Token is signed by Microsoft Entra ID and has not been tampered with
Token was issued by Microsoft Entra ID for the tenant associated with the server
Token has not expired
Token is for the Azure Database for MySQL resource (and not another Azure resource)

Compatibility with application drivers

Most drivers are supported, however make sure to use the settings for sending the password in clear-text, so the token gets sent without modification.

C/C++
- libmysqlclient: Supported
- mysql-connector-c++: Supported
Java
- Connector/J (mysql-connector-java): Supported, must utilize useSSL setting
Python
- Connector/Python: Supported
Ruby
- mysql2: Supported
.NET
- mysql-connector-net: Supported, need to add plugin for mysql_clear_password
- mysql-net/MySqlConnector: Supported
Node.js
- mysqljs: Not supported (does not send token in cleartext without patch)
- node-mysql2: Supported
Perl
- DBD::mysql: Supported
- Net::MySQL: Not supported
Go
- go-sql-driver: Supported, add ?tls=true&allowCleartextPasswords=true to connection string

Next steps

Review the overall concepts for Microsoft Entra authentication with Azure Database for MySQL

Share via

Use Microsoft Entra ID for authentication with MySQL

Setting the Microsoft Entra Admin user

Connecting to Azure Database for MySQL using Microsoft Entra ID

Prerequisites

Step 1: Authenticate with Microsoft Entra ID

Step 2: Retrieve Microsoft Entra access token

Step 3: Use token as password for logging in with MySQL

Using MySQL CLI

Using MySQL Workbench

Important considerations when connecting:

Creating Microsoft Entra users in Azure Database for MySQL

Creating Microsoft Entra groups in Azure Database for MySQL

Token Validation

Compatibility with application drivers

Next steps

Additional resources