Use Microsoft Entra to connect Azure Database for MySQL via Function App
TOC
Why we use it
Architecture
How to use it
References
Why we use it
An increasing number of security vulnerabilities are caused by sensitive information in the source code being leaked or accidentally uploaded to open repositories GitHub, leading to the exposure of a large amount of user privacy. This information includes, but is not limited to, database credentials, app keys, etc. Traditional MySQL databases use usernames and passwords for login operations, naturally leading to the inclusion of these credentials in project configuration files. Even with proper safeguards, there remain unpredictable risks, such as hacking.
This tutorial will introduce how to integrate Microsoft Entra with Azure Database for MySQL to avoid using fixed usernames and passwords. By utilizing system-assigned managed identities and user-assigned managed identities as a programmatic bridge, it becomes easier for Azure-related PaaS services (such as Function App or App Services) to communicate with the database without storing connection information in plain text.
Architecture
I will introduce each service or component and their configurations in subsequent chapters according to the order of A-E:
A: The company’s account administrator needs to create or designate a user as the database administrator. This role can only be assigned to one person within the database and is responsible for basic configuration and the creation and maintenance of other database users. It is not intended for development or actual system operations.
B: The company’s development department needs to create a Function App (or other service) as the basic unit of the business system. Programmers within this unit will write business logic (e.g., accessing the database) and deploy it here.
C: The company’s development department needs to create and manage a system-assigned managed identity for the Function App. In the future, the Function App will issue access requests to the database under its own identity.
D: The company’s security department needs to create one or more user-assigned managed identities. In the future, the Function App will issue access requests to the database under different user identities.
E: The company’s data department needs to create or maintain a database and designate Microsoft Entra as the only login method, eliminating other fixed username/password combinations.
For steps C and D, you can choose to implement one method or both, depending on your business scenarios.
How to use it
A: As this article does not dive into the detailed configuration of Microsoft Entra, it will only outline the process. The company’s account administrator needs to create or designate a user as the database administrator. In this example, we will call this user “cch,” and the account, “cch@thexxxxxxxxxxxx” will be used in subsequent steps.
B-1: In this example, we can create a Function App with any SKU/region. For the development language (stack), we choose Python as a demonstration, though other languages also support the same functionality. Since this article does not cover the detailed network configuration or other specifics of the Function App, we will use the default values for other settings. Simply click on “Review + Create,” and then click on “Create” to complete the process.
C-1: Meanwhile, in the Function App, navigate to the “Identity” option. We need to enable its system-assigned managed identity and make a note of its Object ID for use in next steps.
C-2: To get the detailed information of this system-assigned managed identity, please navigate to Microsoft Entra. Click on “Enterprise Applications,” then search for the name of your Function App in the search bar, and make sure to disable the application type filter.
After selecting this application, you will be able to obtain the application ID of the system-assigned managed identity from its page. Please make a note of this ID, as it will be used in subsequent steps.
D-1: Please open Azure Cloud Shell in the PowerShell mode and enter a command similar to the one below to create a user-assigned managed identity. You will need to change the <text> to follow your company’s naming conventions. Once the creation is successful, please note the returned values for “clientid” and “id” as these will be used in subsequent steps.
az identity create –resource-group <YOUR_RG_NAME> –name <YOUR_IDENTITY_NAME>
D-2: Staying in PowerShell mode, you will then need to use the “id” obtained in step D-1 to construct a command that will bind this user-assigned managed identity to the Function App.
az functionapp identity assign –resource-group <YOUR_RG_NAME> –name <YOUR_FUNCTION_APP_NAME> –identities <ID_IN_STEP_D1>
E-1: Create a database. During this process, you need to specify the user created in Step A as the database administrator and specify the user-assigned managed identity created in Step D as the entity that will actually operate the database. Please note that the MySQL version needs to be at least 5.7 or higher, and for the authentication method, select “Microsoft Entra authentication only.” In this mode, the username/password will no longer be used. Then, click on “Next: Networking.”
Since this article does not cover the detailed network configuration of the database, temporarily allow public access during the tutorial. Use the default values for other settings, click on “Review + Create,” and then click “Create” to finish the setup.
E-2: After the database is created, you can log in using the identity “cch@thexxxxxxxxxxxx” you’ve get from Step A which is the database administrator. Log in to the Azure portal using the “cch” account, open an Azure Cloud Shell, and switch to Bash mode. Then, enter the following command to log in to MySQL. You will need to change the <text> to follow your company’s naming conventions.
mysql -h <YOUR_DB_NAME>.mysql.database.azure.com –user <YOUR_FULL_USER_EMAIL> –enable-cleartext-plugin –password=`az account get-access-token –resource-type oss-rdbms –output tsv –query accessToken`
Here, we additionally introduce the method for logging in using a GUI-related MySQL client. In the previous command, you may notice a nested command enclosed within backticks “. This is used to get an access token for database access as the “cch” user. This token can be used as the password when logging into MySQL clients (e.g., DataGrip, HeidiSQL).
az account get-access-token –resource-type oss-rdbms –output tsv –query accessToken
Returning to the MySQL console, we will now create user accounts in MySQL for the managed identities setup from Step C and Step D. First, we will introduce the method for the user-assigned managed identity. Please enter the following command.
SET aad_auth_validate_oids_in_tenant = OFF;
CREATE AADUSER ‘<SPECIFY_A_PROPER_NAME>’ IDENTIFIED BY ‘<YOUR_CLIENT_ID_FROM_D1>’;
The purpose of these two commands is to create a MySQL user account specifically for the managed identity. You can freely name the account; in this example, I have named it “miuser”.
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON *.* TO ‘<SPECIFY_A_PROPER_NAME>’@’%’ WITH GRANT OPTION;
FLUSH PRIVILEGES;
The purpose of the next two commands is to grant database-related operational permissions to the newly created user. This is just an example. In actual scenarios, you should follow your company’s security policies and make the necessary adjustments accordingly.
Once you are familiar with the method, you can proceed to perform the same operations for the system-assigned managed identity.
SET aad_auth_validate_oids_in_tenant = OFF;
CREATE AADUSER ‘<SPECIFY_A_PROPER_NAME>’ IDENTIFIED BY ‘<YOUR_APP_ID_FROM_C2>’;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON *.* TO ‘<SPECIFY_A_PROPER_NAME>’@’%’ WITH GRANT OPTION;
FLUSH PRIVILEGES;
For testing purposes in later steps, we will create a test database, a test table, and insert some data. Please put this data schema in the mysql console.
CREATE DATABASE test;
USE test;
CREATE TABLE test.welcome_test(
id int AUTO_INCREMENT PRIMARY KEY,
test VARCHAR(4) NOT NULL
) ENGINE=InnoDB;
INSERT INTO test.welcome_test(test) VALUES (‘asdf’);
B-2: Programmer can now deploy the code to the Function App. In this tutorial, we use Python to complete the example and utilize “Programming Model v2”. Other languages also have their respective MySQL connectors and follow the same principles.
In requirements.txt, in addition to the existing azure-functions, please add the following two packages: azure-identity and mysql-connector-python.
azure-functions
azure-identity
mysql-connector-python
In function_app.py, include the following example content:
import azure.functions as func
import logging
import mysql.connector
from mysql.connector import errorcode
import azure.identity
from azure.identity import DefaultAzureCredential
from azure.identity import ManagedIdentityCredential
app = func.FunctionApp(http_auth_level=func.AuthLevel.ANONYMOUS)
@app.route(route=”mysqltest”)
def mysqltest(req: func.HttpRequest) -> func.HttpResponse:
ret = “”
# —————————-
logging.info(‘Get token from system assigned identity’)
smi = DefaultAzureCredential()
stoken = smi.get_token(“https://ossrdbms-aad.database.windows.net”).token
stoken = str(stoken).strip()
sconn = mysql.connector.connect(**{
‘host’:’mimysql-db.mysql.database.azure.com’, # change to your server
‘user’:’smiuser’, # change to your username
‘password’:stoken,
‘auth_plugin’:’mysql_clear_password’,
‘client_flags’: [mysql.connector.ClientFlag.SSL],
‘database’:’test’ # change to your database
})
scursor = sconn.cursor()
scursor.execute(“SELECT * FROM welcome_test;”) # change to your table
srows = scursor.fetchall()
sret = “”
for srow in srows:
sret += str(srow[1])
sconn.commit()
scursor.close()
sconn.close()
# —————————-
logging.info(‘Get token from user assigned identity’)
umi = ManagedIdentityCredential(client_id=”1ddc2a7e-xxxx-xxxx-xxxx-xxxxxxxxxxxx”) # change to your client id from Step D1
utoken = umi.get_token(“https://ossrdbms-aad.database.windows.net”).token
utoken = str(utoken).strip()
uconn = mysql.connector.connect(**{
‘host’:’mimysql-db.mysql.database.azure.com’, # change to your server
‘user’:’miuser’, # change to your username
‘password’:utoken,
‘auth_plugin’:’mysql_clear_password’,
‘client_flags’: [mysql.connector.ClientFlag.SSL],
‘database’:’test’ # change to your database
})
ucursor = uconn.cursor()
ucursor.execute(“SELECT * FROM welcome_test;”) # change to your table
urows = ucursor.fetchall()
uret = “”
for urow in urows:
uret += str(urow[1])
uconn.commit()
ucursor.close()
uconn.close()
# —————————-
return func.HttpResponse(sret + uret)
Please note that the code I provided in this tutorial is only suitable for the testing phase. Its purpose is to verify usability and it is not intended for production use. Ultimately, please make the corresponding modifications based on the business functionality and security guidelines of your own environment.
Once the deployment is complete, you can proceed with testing. We can observe that the Function App will call the authentication endpoint in the background to get an access token. It will then use this token to interact with the database and subsequently print out the queried data.
An important thing in the code is the following snippet.
‘auth_plugin’:’mysql_clear_password’,
Referencing Step E-2, when “cch” used the MySQL console during the connection process, the –enable-cleartext-plugin parameter was used. This parameter means that the client can send the password to the server in clear text because the password is already in the form of an encrypted token. Unlike traditional plain-text passwords that need to be encrypted on the client side before transmission, it can be sent directly in plain text to the server. Different languages and different MySQL connectors may require similar configurations, so this should be noted carefully.
References
Active Directory authentication – Azure Database for MySQL | Microsoft Learn
Use Microsoft Entra ID – Azure Database for MySQL | Microsoft Learn
How to create users for Azure Database for MySQL | Microsoft Learn
Connect with Managed Identity – Azure Database for MySQL | Microsoft Learn
Set up Microsoft Entra authentication – Azure Database for MySQL – Flexible Server | Microsoft Learn
Azure AD authentication for Azure MySQL! (microsoft.com)
Microsoft Tech Community – Latest Blogs –Read More
