マネージド ID を使用した Azure SQL Database への接続と接続元アプリの特定 #SQLServer

本記事は Microsoft Azure Tech Advent Calendar 2022 19 日目の記事です。

1. はじめに

本記事では、Azure Windows 仮想マシン (VM) 上の PowerSell やコンソールアプリケーションから、VM のシステム割り当て ID（System Assigned Managed Identity）を使用して、Microsoft.Data.SqlClient や System.Data.SqlClient で Azure SQL Database 内のデータベースへ接続する方法と SQL Database からアプリケーションを特定する方法について記載したいと思います。

マネージド ID を使用したアプリケーションからの接続は、こちらのチュートリアルを参考にしています。

2. 構成

■ 事前準備

https://learn.microsoft.com/ja-jp/azure/azure-sql/database/single-database-create-quickstart?view=azuresql&tabs=azure-portal を参考にAzure SQL Database を作成。
https://learn.microsoft.com/ja-jp/training/modules/create-windows-virtual-machine-in-azure/3-exercise-create-a-vm を参考にAzure で Windows 仮想マシンを作成。

■ Azure VM でシステム割り当てマネージド ID を有効にする

システム割り当てマネージド ID の有効化は VM の作成時にも指定できるし、既存の VM のプロパティからも有効にできます。

■ Azure SQL Database 内のデータベースに対するアクセス権を VM に付与する

Azure SQL Server で側の構成は 2 STEP。まずは Azure Active Directory ユーザーを Azure SQL データベースに追加して Azure AD 認証を有効にします。

SQL Server Management Studio(SSMS)を使用して先程の Azure Active Directory ユーザーで接続、データベースに対して以下のクエリを実行します。

CREATE USER [VMName] FROM EXTERNAL PROVIDER
GO

ALTER ROLE db_datareader ADD MEMBER [VMName]
GO

3. 実装 (データベースへアクセス)

まずは PowerShell から。ローカルのマネージド ID のエンドポイントに Azure SQL のアクセストークンを取得するよう要求し、取得したトークンを System.Data.SqlClient 名前空間の SqlConnection オブジェクトの AccessToken プロパティに設定します。

$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"}
$content = $response.Content | ConvertFrom-Json
$AccessToken = $content.access_token

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

$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]

続いて、C# で Microsoft.Data.SqlClient から。Microsoft.Data.SqlClient 2.1.0 以降ではドライバー内でマネージド ID を使用してアクセストークンを取得することがサポートされており、接続文字列で Active Directory Managed Identity を指定するだけで利用可能です。

using System;
using Microsoft.Data.SqlClient;

namespace ConsoleApp1
{
    internal class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Data Source = <AZURE-SQL-SERVERNAME>; Initial Catalog = <DATABASE>; Authentication=Active Directory Managed Identity; Encrypt=True";
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();

            Console.WriteLine("Press Enter to Finish:");
            Console.ReadLine();

        }
    }
}

4. 検証

SQL Server Management Studio から接続情報を SQL 側で確認。sys.dm_exec_sessions は、アクティブなユーザー接続を特定でき、クライアントバージョン、クライアントプログラム名、クライアントのログイン日時、ログインユーザー、現在のセッション設定などが確認できます。

SELECT session_id, login_time, host_name, program_name, host_process_id, login_name
FROM sys.dm_exec_sessions
WHERE host_name = [VMName]
GO