使用托管标识连接到 Azure Database for MySQLConnect with Managed Identity to Azure Database for MySQL

本文介绍如何使用 Azure 虚拟机 (VM) 的用户分配标识来访问 Azure Database for MySQL 服务器。This article shows you how to use a user-assigned identity for an Azure Virtual Machine (VM) to access an Azure Database for MySQL server. 托管服务标识由 Azure 自动管理,可用于向支持 Azure AD 身份验证的服务进行身份验证,这样就无需在代码中插入凭据了。Managed Service Identities are automatically managed by Azure and enable you to authenticate to services that support Azure AD authentication, without needing to insert credentials into your code.

学习如何:You learn how to:

  • 授予 VM 对 Azure Database for MySQL 服务器的访问权限Grant your VM access to an Azure Database for MySQL server
  • 在数据库中创建一个代表 VM 用户分配标识的用户Create a user in the database that represents the VM's user-assigned identity
  • 使用 VM 标识获取访问令牌,并使用它查询 Azure Database for MySQL 服务器Get an access token using the VM identity and use it to query an Azure Database for MySQL server
  • 在 C# 示例应用程序中实现令牌检索Implement the token retrieval in a C# example application

重要

使用托管标识进行连接仅适用于 MySQL 5.7 及更高版本。Connecting with Managed Identity is only available for MySQL 5.7 and newer.

先决条件Prerequisites

为 VM 创建用户分配托管标识Creating a user-assigned managed identity for your VM

使用 az identity create 命令在订阅中创建标识。Create an identity in your subscription using the az identity create command. 可以使用虚拟机所在的相同资源组,也可以使用其他资源组。You can use the same resource group that your virtual machine runs in, or a different one.

az identity create --resource-group myResourceGroup --name myManagedIdentity

要在以下步骤中配置标识,请使用 az identity show 命令将标识的资源 ID 和客户端 ID 存储在变量中。To configure the identity in the following steps, use the az identity show command to store the identity's resource ID and client ID in variables.

# Get resource ID of the user-assigned identity
resourceID=$(az identity show --resource-group myResourceGroup --name myManagedIdentity --query id --output tsv)

# Get client ID of the user-assigned identity
clientID=$(az identity show --resource-group myResourceGroup --name myManagedIdentity --query clientId --output tsv)

现在,我们使用 az vm identity assign 命令将用户分配标识分配给 VM:We can now assign the user-assigned identity to the VM with the az vm identity assign command:

az vm identity assign --resource-group myResourceGroup --name myVM --identities $resourceID

要完成设置,请显示客户端 ID 的值,在接下来的几个步骤中你将需要该值:To finish setup, show the value of the Client ID, which you'll need in the next few steps:

echo $clientID

为托管标识创建 MySQL 用户Creating a MySQL user for your Managed Identity

现在,作为 Azure AD 管理员用户连接到 MySQL 数据库,并运行以下 SQL 语句:Now, connect as the Azure AD administrator user to your MySQL database, and run the following SQL statements:

SET aad_auth_validate_oids_in_tenant = OFF;
CREATE AADUSER 'myuser' IDENTIFIED BY 'CLIENT_ID';

使用用户名 myuser(替换为你选择的名称)进行身份验证时,托管标识现在具有访问权限。The managed identity now has access when authenticating with the username myuser (replace with a name of your choice).

从 Azure 实例元数据服务检索访问令牌Retrieving the access token from Azure Instance Metadata service

应用程序现在可以从 Azure 实例元数据服务检索访问令牌,并将其用于向数据库进行身份验证。Your application can now retrieve an access token from the Azure Instance Metadata service and use it for authenticating with the database.

此令牌检索是通过向 http://169.254.169.254/metadata/identity/oauth2/token 发出 HTTP 请求并传递以下参数来完成的:This token retrieval is done by making an HTTP request to http://169.254.169.254/metadata/identity/oauth2/token and passing the following parameters:

  • api-version = 2018-02-01
  • resource = https://ossrdbms-aad.database.chinacloudapi.cn
  • client_id = CLIENT_ID(之前检索到的)client_id = CLIENT_ID (that you retrieved earlier)

将返回包含 access_token 字段的 JSON 结果 - 此长文本值是托管标识访问令牌,在连接到数据库时应将其用作密码。You'll get back a JSON result that contains an access_token field - this long text value is the Managed Identity access token, that you should use as the password when connecting to the database.

出于测试目的,可以在 shell 中运行以下命令。For testing purposes, you can run the following commands in your shell. 请注意,需要安装 curljqmysql 客户端。Note you need curl, jq, and the mysql client installed.

# Retrieve the access token
accessToken=$(curl -s 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fossrdbms-aad.database.chinacloudapi.cn&client_id=CLIENT_ID' -H Metadata:true | jq -r .access_token)

# Connect to the database
mysql -h SERVER --user USER@SERVER --enable-cleartext-plugin --password=$accessToken

你现在已连接到之前配置的数据库。You are now connected to the database you've configured earlier.

在 C# 中使用托管标识进行连接Connecting using Managed Identity in C#

本部分介绍如何使用 VM 用户分配托管标识获取访问令牌,并使用它来调用 Azure Database for MySQL。This section shows how to get an access token using the VM's user-assigned managed identity and use it to call Azure Database for MySQL. Azure Database for MySQL 以原生方式支持 Azure AD 身份验证,因此可以直接接受使用 Azure 资源托管标识获取的访问令牌。Azure Database for MySQL natively supports Azure AD authentication, so it can directly accept access tokens obtained using managed identities for Azure resources. 创建到 MySQL 的连接时,在密码字段中传递访问令牌。When creating a connection to MySQL, you pass the access token in the password field.

以下 .NET 代码示例使用访问令牌来与 MySQL 建立连接。Here's a .NET code example of opening a connection to MySQL using an access token. 此代码必须在 VM 上运行,以访问 VM 用户分配托管标识的终结点。This code must run on the VM to access the VM's user-assigned managed identity's endpoint. 使用访问令牌方法需要 .NET Framework 4.6 或更高版本或者 .NET Core 2.2 或更高版本。.NET Framework 4.6 or higher or .NET Core 2.2 or higher is required to use the access token method. 替换 HOST、USER、DATABASE 和 CLIENT_ID 的值。Replace the values of HOST, USER, DATABASE, and CLIENT_ID.

using System;
using System.Net;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System.Text.Json;
using System.Text.Json.Serialization;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace Driver
{
    class Script
    {
        // Obtain connection string information from the portal
        //
        private static string Host = "HOST";
        private static string User = "USER";
        private static string Database = "DATABASE";
        private static string ClientId = "CLIENT_ID";

        static async Task Main(string[] args)
        {
            //
            // Get an access token for MySQL.
            //
            Console.Out.WriteLine("Getting access token from Azure Instance Metadata service...");

            // Azure AD resource ID for Azure Database for MySQL is https://ossrdbms-aad.database.chinacloudapi.cn/
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fossrdbms-aad.database.chinacloudapi.cn&client_id=" + ClientId);
            request.Headers["Metadata"] = "true";
            request.Method = "GET";
            string accessToken = null;

            try
            {
                // Call managed identities for Azure resources endpoint.
                HttpWebResponse response = (HttpWebResponse)request.GetResponse();

                // Pipe response Stream to a StreamReader and extract access token.
                StreamReader streamResponse = new StreamReader(response.GetResponseStream());
                string stringResponse = streamResponse.ReadToEnd();
                var list = JsonSerializer.Deserialize<Dictionary<string, string>>(stringResponse);
                accessToken = list["access_token"];
            }
            catch (Exception e)
            {
                Console.Out.WriteLine("{0} \n\n{1}", e.Message, e.InnerException != null ? e.InnerException.Message : "Acquire token failed");
                System.Environment.Exit(1);
            }

            //
            // Open a connection to the MySQL server using the access token.
            //
            var builder = new MySqlConnectionStringBuilder
            {
                Server = Host,
                Database = Database,
                UserID = User,
                Password = accessToken,
                SslMode = MySqlSslMode.Required,
            };

            using (var conn = new MySqlConnection(builder.ConnectionString))
            {
                Console.Out.WriteLine("Opening connection using access token...");
                await conn.OpenAsync();

                using (var command = conn.CreateCommand())
                {
                    command.CommandText = "SELECT VERSION()";

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            Console.WriteLine("\nConnected!\n\nMySQL version: {0}", reader.GetString(0));
                        }
                    }
                }
            }
        }
    }
}

运行时,此命令将返回如下所示的输出:When run, this command will give an output like this:

Getting access token from Azure Instance Metadata service...
Opening connection using access token...

Connected!

MySQL version: 5.7.27

后续步骤Next steps