教程:使用 Windows VM 系统分配托管标识访问 Azure SQLTutorial: Use a Windows VM system-assigned managed identity to access Azure SQL

Azure 资源的托管标识是 Azure Active Directory 的一项功能。Managed identities for Azure resources is a feature of Azure Active Directory. 支持 Azure 资源的托管标识的每个 Azure 服务都受其自己的时间线限制。Each of the Azure services that support managed identities for Azure resources are subject to their own timeline. 在开始之前,请务必查看资源的托管标识的可用性状态以及已知问题Make sure you review the availability status of managed identities for your resource and known issues before you begin.

本教程介绍如何使用 Windows 虚拟机 (VM) 的系统分配标识访问 Azure SQL 数据库。This tutorial shows you how to use a system-assigned identity for a Windows virtual machine (VM) to access Azure SQL Database. 托管服务标识由 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 SQL 数据库的访问权限Grant your VM access to Azure SQL Database
  • 启用 Azure AD 身份验证Enable Azure AD authentication
  • 在数据库中创建一个代表 VM 的系统分配标识的包含用户Create a contained user in the database that represents the VM's system assigned identity
  • 使用 VM 标识获取访问令牌,并使用它查询 Azure SQL 数据库Get an access token using the VM identity and use it to query Azure SQL Database

先决条件Prerequisites

启用Enable

启用系统分配的托管标识只需单击一次即可。Enabling a system-assigned managed identity is a one-click experience. 可以在创建 VM 的过程中或在现有 VM 的属性中启用它。You can either enable it during the creation of a VM or in the properties of an existing VM.

新建存储帐户

若要在新 VM 上启用系统分配的托管标识,请执行以下操作:To enable a system-assigned managed identity on a new VM:

  1. 登录到 Azure 门户Sign in to Azure portal

  2. 创建启用了系统分配标识的虚拟机Create a virtual machine with system-assigned identity enabled

授予访问权限Grant access

若要授予 VM 对 Azure SQL 数据库中数据库的访问权限,可以创建新的数据库。To grant your VM access to a database in Azure SQL Database, you can create a new one. 若要使用 Azure 门户创建新的服务器和数据库,请遵循此 Azure SQL 快速入门To create a new server and database using the Azure portal, follow this Azure SQL quickstart. Azure SQL 文档中还提供了有关使用 Azure CLI 和 Azure PowerShell 执行这些操作的快速入门。There are also quickstarts that use the Azure CLI and Azure PowerShell in the Azure SQL documentation.

授予 VM 对数据库的访问权限需要执行两个步骤:There are two steps to granting your VM access to a database:

  1. 为服务器启用 Azure AD 身份验证。Enable Azure AD authentication for the server.
  2. 在数据库中创建一个代表 VM 的系统分配标识的包含用户Create a contained user in the database that represents the VM's system-assigned identity.

启用 Azure AD 身份验证Enable Azure AD authentication

配置 Azure AD 身份验证To configure Azure AD authentication:

  1. 在 Azure 门户的左侧导航栏中选择“SQL 服务器”。In the Azure portal, select SQL servers from the left-hand navigation.
  2. 单击要启用 Azure AD 身份验证的 SQL 服务器。Click the SQL server to be enabled for Azure AD authentication.
  3. 在边栏选项卡的“设置”部分中,单击“Active Directory 管理员”。 In the Settings section of the blade, click Active Directory admin.
  4. 在命令栏中单击“设置管理员”。In the command bar, click Set admin.
  5. 选择要设为服务器管理员的 Azure AD 用户帐户,单击“选择”。Select an Azure AD user account to be made an administrator of the server, and click Select.
  6. 在命令栏中,单击“保存”。In the command bar, click Save.

创建包含用户Create contained user

本部分介绍如何在数据库中创建一个表示 VM 的系统分配标识的包含用户。This section shows how to create a contained user in the database that represents the VM's system assigned identity. 在此步骤中,需要使用 Microsoft SQL Server Management Studio (SSMS)。For this step, you need Microsoft SQL Server Management Studio (SSMS). 在开始之前,查看以下文章了解有关 Azure AD 集成的背景知识可能也有帮助:Before beginning, it may also be helpful to review the following articles for background on Azure AD integration:

SQL 数据库需要唯一的 AAD 显示名称。SQL Database requires unique AAD display names. 因此,AAD 帐户(如用户、组和服务主体(应用程序))以及启用了托管标识的 VM 名称必须在 AAD 中针对其显示名称进行唯一定义。With this, the AAD accounts such as users, groups and Service Principals (applications) and VM names enabled for managed identity must be uniquely defined in AAD regarding their display names. SQL 数据库在使用 T-SQL 创建此类用户期间会检查 AAD 显示名称,如果它不唯一,则命令将无法请求为给定帐户提供唯一的 AAD 显示名称。SQL Database checks the AAD display name during T-SQL creation of such users and if it is not unique, the command fails requesting to provide a unique AAD display name for a given account.

若要创建包含用户,请执行以下操作:To create a contained user:

  1. 启动 SQL Server Management Studio。Start SQL Server Management Studio.

  2. 在“连接到服务器”对话框的“服务器名称”字段中,输入服务器名称 。In the Connect to Server dialog, Enter your server name in the Server name field.

  3. 在“身份验证”字段中,选择“Active Directory - 通用且具有 MFA 支持”。 In the Authentication field, select Active Directory - Universal with MFA support.

  4. 在“用户名”字段中,输入已设为服务器管理员的 Azure AD 帐户的名称,例如 helen@woodgroveonline.comIn the User name field, enter the name of the Azure AD account that you set as the server administrator, for example, helen@woodgroveonline.com

  5. 单击“选项” 。Click Options.

  6. 在“连接到数据库”字段中,输入要配置的非系统数据库的名称。In the Connect to database field, enter the name of the non-system database you want to configure.

  7. 单击“连接” 。Click Connect. 完成登录过程。Complete the sign-in process.

  8. 在“对象资源管理器”中,展开“数据库”文件夹。 In the Object Explorer, expand the Databases folder.

  9. 右键单击某个用户数据库,并单击“新建查询”。Right-click on a user database and click New query.

  10. 在查询窗口中输入以下行,在工具栏中单击“执行”:In the query window, enter the following line, and click Execute in the toolbar:

    备注

    以下命令中的 VMName 是在“先决条件”部分中对其启用系统分配标识的 VM 的名称。VMName in the following command is the name of the VM that you enabled system assigned identity on in the prerequsites section.

    CREATE USER [VMName] FROM EXTERNAL PROVIDER
    

    该命令应该成功完成,为 VM 的系统分配标识创建包含的用户。The command should complete successfully, creating the contained user for the VM's system-assigned identity.

  11. 清除查询窗口中的内容,输入以下行,在工具栏中单击“执行”:Clear the query window, enter the following line, and click Execute in the toolbar:

    备注

    以下命令中的 VMName 是在“先决条件”部分中对其启用系统分配标识的 VM 的名称。VMName in the following command is the name of the VM that you enabled system assigned identity on in the prerequsites section.

    ALTER ROLE db_datareader ADD MEMBER [VMName]
    

    命令应会成功完成,并授予包含的用户读取整个数据库的权限。The command should complete successfully, granting the contained user the ability to read the entire database.

VM 中运行的代码现在可使用其系统分配托管标识获取令牌,并使用该令牌在服务器中进行身份验证。Code running in the VM can now get a token using its system-assigned managed identity and use the token to authenticate to the server.

访问数据Access data

本部分介绍如何使用 VM 的系统分配托管标识获取访问令牌,并使用它调用 Azure SQL。This section shows how to get an access token using the VM's system-assigned managed identity and use it to call Azure SQL. Azure SQL 原本就支持 Azure AD 身份验证,因此可以直接接受使用 Azure 资源的托管标识获取的访问令牌。Azure SQL natively supports Azure AD authentication, so it can directly accept access tokens obtained using managed identities for Azure resources. 使用访问令牌方法来与 SQL 建立连接。You use the access token method of creating a connection to SQL. 在某种程度上,这是将 Azure SQL 与 Azure AD 集成,不同于在连接字符串中提供凭据。This is part of Azure SQL's integration with Azure AD, and is different from supplying credentials on the connection string.

以下 .NET 代码示例使用访问令牌来与 SQL 建立连接。Here's a .NET code example of opening a connection to SQL using an access token. 此代码必须在 VM 上运行才能访问 VM 的系统分配托管标识的终结点。This code must run on the VM to be able to access the VM's system-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. 相应地替换 AZURE-SQL-SERVERNAME 和 DATABASE 的值。Replace the values of AZURE-SQL-SERVERNAME and DATABASE accordingly. 请注意,Azure SQL 的资源 ID 为“https://database.chinacloudapi.cn/”。Note the resource ID for Azure SQL is https://database.chinacloudapi.cn/.

using System.Net;
using System.IO;
using System.Data.SqlClient;
using System.Web.Script.Serialization;

//
// Get an access token for SQL.
//
HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https://database.chinacloudapi.cn/");
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();
    JavaScriptSerializer j = new JavaScriptSerializer();
    Dictionary<string, string> list = (Dictionary<string, string>) j.Deserialize(stringResponse, typeof(Dictionary<string, string>));
    accessToken = list["access_token"];
}
catch (Exception e)
{
    string errorText = String.Format("{0} \n\n{1}", e.Message, e.InnerException != null ? e.InnerException.Message : "Acquire token failed");
}

//
// Open a connection to the server using the access token.
//
if (accessToken != null) {
    string connectionString = "Data Source=<AZURE-SQL-SERVERNAME>; Initial Catalog=<DATABASE>;";
    SqlConnection conn = new SqlConnection(connectionString);
    conn.AccessToken = accessToken;
    conn.Open();
}

或者,可以使用 PowerShell 快速测试端到端设置,而无需在 VM 上编写和部署应用。Alternatively, a quick way to test the end to end setup without having to write and deploy an app on the VM is using PowerShell.

  1. 在门户中,导航到“虚拟机”并转到 Windows 虚拟机,然后在“概述”中,单击“连接”。In the portal, navigate to Virtual Machines and go to your Windows virtual machine and in the Overview, click Connect.

  2. 输入创建 Windows VM 时添加的用户名和密码。Enter in your Username and Password for which you added when you created the Windows VM.

  3. 现在,已经创建了与虚拟机的远程桌面连接,请在远程会话中打开 PowerShell。Now that you have created a Remote Desktop Connection with the virtual machine, open PowerShell in the remote session.

  4. 使用 PowerShell 的 Invoke-WebRequest 向本地托管标识的终结点发出请求,以获取 Azure SQL 的访问令牌。Using PowerShell’s Invoke-WebRequest, make a request to the local managed identity's endpoint to get an access token for Azure SQL.

        $response = Invoke-WebRequest -Uri 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fdatabase.chinacloudapi.cn%2F' -Method GET -Headers @{Metadata="true"}
    

    将响应从 JSON 对象转换为 PowerShell 对象。Convert the response from a JSON object to a PowerShell object.

    $content = $response.Content | ConvertFrom-Json
    

    从响应中提取访问令牌。Extract the access token from the response.

    $AccessToken = $content.access_token
    
  5. 与服务器建立连接。Open a connection to the server. 请记得替换 AZURE-SQL-SERVERNAME 和 DATABASE 的值。Remember to replace the values for AZURE-SQL-SERVERNAME and DATABASE.

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Data Source = <AZURE-SQL-SERVERNAME>; Initial Catalog = <DATABASE>"
    $SqlConnection.AccessToken = $AccessToken
    $SqlConnection.Open()
    

    接下来,创建一个查询并将其发送到服务器。Next, create and send a query to the server. 请记得替换 TABLE 的值。Remember to replace the value for TABLE.

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = "SELECT * from <TABLE>;"
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    

检查 $DataSet.Tables[0] 的值,以查看查询结果。Examine the value of $DataSet.Tables[0] to view the results of the query.

禁用Disable

若要在 VM 上禁用系统分配的标识,请将系统分配的标识的状态设为“关” 。To disable the system-assigned identity on your VM, set the status of the system-assigned identity to Off.

新建存储帐户

后续步骤Next steps

本教程介绍了如何使用系统分配托管标识来访问 Azure SQL 数据库。In this tutorial, you learned how to use a system-assigned managed identity to access Azure SQL Database. 要了解有关 Azure SQL 数据库的详细信息,请参阅:To learn more about Azure SQL Database see: