本記事は 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
