Azure SQL Database の Microsoft Entra 認証を試してみた

Azure SQL Database の Microsoft Entra 認証を使用すると、マネージド ID のパスワードレス認証ができます。例えば、WebApp のマネージド ID で Azure SQL Database に対してパスワードレスで認証が可能になり、パスワード管理から解放されます。そこで今回は、Microsoft Entra 認証オンリーの Azure SQL Database を用意し、ローカル環境からテナントユーザーで接続できるか試してみました。

## Microsoft Entra 認証オンリーの Azure SQL Database を作成

`azuread_authentication_only = true` を設定します。

```tf:main.tf
provider "azurerm" {
  features {}
}

variable "prefix" {
  type    = string
  default = "mnrsqldev"
}

variable "region" {
  type    = string
  default = "japaneast"
}

data "azurerm_client_config" "current" {}

data "http" "myip" {
  url = "http://inet-ip.info/ip"
}

# Resource Group
resource "azurerm_resource_group" "rg" {
  name     = "${var.prefix}-rg"
  location = var.region
}

# SQL Server
resource "azurerm_mssql_server" "srv" {
  name                = "${var.prefix}-mssql"
  resource_group_name = azurerm_resource_group.rg.name
  location            = azurerm_resource_group.rg.location
  version             = "12.0"

  azuread_administrator {
    azuread_authentication_only = true
    login_username              = "test@example.jp"
    object_id                   = data.azurerm_client_config.current.object_id
  }
}

# SQL Firewall
resource "azurerm_mssql_firewall_rule" "fw" {
  name             = "myip"
  server_id        = azurerm_mssql_server.srv.id
  start_ip_address = data.http.myip.response_body
  end_ip_address   = data.http.myip.response_body
}

# SQL Database
resource "azurerm_mssql_database" "db" {
  name                 = "${var.prefix}-db"
  server_id            = azurerm_mssql_server.srv.id
  collation            = "Japanese_CI_AS"
  max_size_gb          = 2
  sku_name             = "Basic"
  storage_account_type = "Local"
}
```

```bash:bash
terraform init

terraform plan

terraform apply
```

## Terraform で作成された Azure リソース

![azure-mssql-entra-01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/675885/264fb8a5-b786-e97b-461f-b12416d0204c.png)

## 認証テストで使用するテナントユーザーを DB に作成

![azure-mssql-entra-02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/675885/b8b758e8-be2c-7408-a59b-fda581af79d2.png)

```sql:sql
CREATE USER [test@example.jp] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [test@example.jp];
GO
```

## 検証用のアプリを作成

```bash:bash
dotnet new web -o DotNetSQL

cd DotNetSQL

dotnet add package Microsoft.Data.SqlClient
```

`/now` にリクエストすると、DB から現在のタイムスタンプを返すコードを追加。

```cs:Program.cs
using Microsoft.Data.SqlClient;

var builder = WebApplication.CreateBuilder(args);
var app = builder.Build();

string connectionString = app.Configuration.GetConnectionString("AZURE_SQL_CONNECTIONSTRING")!;

app.MapGet("/", () => "Hello World!");

app.MapGet("/now", () => {
    var rows = new List<string>();

    using var conn = new SqlConnection(connectionString);
    conn.Open();

    var command = new SqlCommand("SELECT CURRENT_TIMESTAMP", conn);
    using SqlDataReader reader = command.ExecuteReader();

    if (reader.HasRows)
    {
        while (reader.Read())
        {
            rows.Add($"{reader.GetDateTime(0)}");
        }
    }

    return rows;
});

app.Run();
```

`ConnectionStrings` を追加。

```json:appsettings.Development.json
{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "AZURE_SQL_CONNECTIONSTRING": "Server=tcp:mnrsqldev-mssql.database.windows.net,1433;Initial Catalog=mnrsqldev-db;Persist Security Info=False;User ID=test@example.jp;Password='ExampleStr0ngP@@sw0rd';MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication='Active Directory Password';"
  }
}
```

## アプリの動作確認

```bash:bash
dotnet run
```

`/now` にアクセスして、下記のような結果が返ってくる。

```bash:bash
curl http://localhost:5162/now
[
    "2024/03/22 23:45:10"
]
```

## 参考

https://learn.microsoft.com/ja-jp/azure/azure-sql/database/authentication-aad-overview

https://learn.microsoft.com/ja-jp/azure/azure-sql/database/azure-sql-dotnet-quickstart