教學課程:使用 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 an Azure SQL 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 SQL 伺服器Grant your VM access to an Azure SQL server
  • 啟用 SQL Server 的 Azure AD 驗證Enable Azure AD authentication for the SQL server
  • 在資料庫中建立內含的使用者,以代表 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 an Azure SQL server

必要條件Prerequisites

將您的 VM 存取權授與 Azure SQL 伺服器中的資料庫Grant your VM access to a database in an Azure SQL server

若要為 Azure SQL Server 中的資料庫授與您的 VM 存取權,您可以使用現有的 SQL 伺服器,或建立新的伺服器。To grant your VM access to a database in an Azure SQL Server, you can use an existing SQL server or 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. 啟用 SQL Server 的 Azure AD 驗證。Enable Azure AD authentication for the SQL server.
  2. 在資料庫中建立內含的使用者,以代表 VM 系統指派的身分識別。Create a contained user in the database that represents the VM's system-assigned identity.

啟用 SQL Server 的 Azure AD 驗證Enable Azure AD authentication for the SQL server

使用下列步驟為 SQL 伺服器設定 Azure AD 驗證Configure Azure AD authentication for the SQL server using the following steps:

  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.

在資料庫中建立內含的使用者,以代表 VM 系統指派的身分識別Create a contained user in the database that represents the VM's system assigned identity

在這一個步驟中,您需要 Microsoft SQL Server Management Studio (SSMS)。For this next step, you will 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 DB 需要唯一的 AAD 顯示名稱。SQL DB 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 DB 會在 T-SQL 建立這類使用者時檢查 AAD 顯示名稱,如果這不是唯一的名稱,命令就無法要求為指定的帳戶提供唯一的 AAD 顯示名稱。SQL DB 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.

  1. 啟動 SQL Server Management Studio。Start SQL Server Management Studio.

  2. 在 [連線到伺服器] 對話方塊中,在 [伺服器名稱] 欄位中輸入您的 SQL 伺服器名稱。In the Connect to Server dialog, Enter your SQL server name in the Server name field.

  3. 在 [驗證] 欄位中,選取 [具 MFA 支援的 Active Directory - 通用] 。In the Authentication field, select Active Directory - Universal with MFA support.

  4. 在 [使用者名稱] 欄位中,輸入您設為伺服器系統管理員的 Azure AD 帳戶名稱,例如 helen@woodgroveonline.com。In 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 中執行的程式碼現在可以使用其系統指派的受控識別取得權杖,並使用此權杖向 SQL 伺服器進行驗證。Code running in the VM can now get a token using its system-assigned managed identity and use the token to authenticate to the SQL server.

使用 VM 系統指派的受控識別來取得存取權杖,以用來呼叫 Azure SQLGet 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. 您使用 access token 方法來建立 SQL 連線。You use the access token method of creating a connection to SQL. 這是 Azure AD 與 Azure SQL 整合的一部分,與在連接字串上提供認證不同。This is part of Azure SQL's integration with Azure AD, and is different from supplying credentials on the connection string.

以下是使用存取權杖來開啟與 SQL 之連線的 .NET 程式碼範例。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 Framework 4.6 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 的資源識別碼是 https://database.windows.net/Note the resource ID for Azure SQL is https://database.windows.net/.

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.windows.net/");
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 SQL 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();
}

或者,另一個測試端對端安裝程式,而不需要在 VM 上撰寫和部署應用程式的快速方法,是使用 PowerShell。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. 現在您已經建立虛擬機器的遠端桌面連線,請在遠端工作階段中開啟 PowerShellNow 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.windows.net%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. 開啟 SQL伺服器連線。Open a connection to the SQL 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.

後續步驟Next steps

在本教學課程中,您已了解如何使用系統指派的受控識別,來存取 Azure SQL 伺服器。In this tutorial, you learned how to use a system-assigned managed identity to access Azure SQL server. 若要深入了解 Azure SQL Server,請參閱:To learn more about Azure SQL Server see: