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

本記事は [Microsoft Azure Tech Advent Calendar 2022 19 日目](https://qiita.com/advent-calendar/2022/microsoft-azure-tech) の記事です。


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

マネージド ID を使用したアプリケーションからの接続は、[こちらのチュートリアル](https://learn.microsoft.com/ja-jp/azure/active-directory/managed-identities-azure-resources/tutorial-windows-vm-access-sql) を参考にしています。

## 2. 構成

#### ■ 事前準備
1. https://learn.microsoft.com/ja-jp/azure/azure-sql/database/single-database-create-quickstart?view=azuresql&tabs=azure-portal を参考にAzure SQL Database を作成。 
2. 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 のプロパティからも有効にできます。
![enabledmanagedid.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2987996/f390307a-7774-7297-7702-7a95cb0e58c9.jpeg)


#### ■ Azure SQL Database 内のデータベースに対するアクセス権を VM に付与する
Azure SQL Server で側の構成は 2 STEP。まずは Azure Active Directory ユーザーを Azure SQL データベースに追加して Azure AD 認証を有効にします。
![enabledaadadmin.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2987996/4759eebc-3a43-a935-eaa0-e2c71b8a1e0a.jpeg)

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 プロパティに設定します。
```PowerShell
$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 を指定するだけで利用可能です。

```cs
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

![checksession.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2987996/04597d33-ef38-a533-32cb-ad1bd07b7a19.jpeg)