【第9回】Azureで社内システム再現（オンプレ編）｜ADからSQLへ社員情報を自動連携する

## 概要

「Azureで社内システム再現（オンプレ編）」では、  
Azure の IaaS サービスを使って簡単な社内システムを再現します。

機能としては、社員番号を入力して検索ボタンを押すと、  
対応する名前を表示するだけのシンプルなものです。

※詳しい全体構成については、[【第0回】Azureで社内システム再現（オンプレ編）｜構成図と動作の流れ](https://qiita.com/saba_infra/items/a5e58ded68973e4551ee) で紹介しています。

---

## システム構成（今回の対象範囲）
![azure_onpre_joutai＿連携_タスクスケジュール.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4037248/74272885-d785-44f0-820b-bef6f1e4608f.png)

上図の赤枠で囲った部分が、今回の記事で扱う範囲です。

この記事では、Active Directory から取得した社員情報を SQL Server に格納し、
それを定期的に自動で更新する仕組みを構築します。

具体的には、以下の作業を行います：

- PowerShell で AD から社員情報（社員番号・名前）を CSV 形式で抽出するスクリプトを作成

- PowerShell で CSV ファイルを SQL Server にインポートするスクリプトを作成

- 上記 2 つのスクリプトを、タスクスケジューラで定期実行できるように設定

最終的に、社員情報が毎日自動で更新されるようにします。

---

## Active Directory 上の社員情報について

本記事では、Active Directory に登録されているユーザー情報のうち、
社員番号と名前を SQL Server に連携させる仕組みを構築していきます。

しかし、Active Directory の標準プロパティには「社員番号」に該当する属性が存在しないため、
今回は代替として、説明（description） 属性に社員番号を登録する方針としています。

事前に、employees というOUに対して、以下のような PowerShell スクリプトを使用して
複数のユーザーを一括で作成しました。

社員の名前と社員番号をセットで Name と description に設定しています。
![スクリーンショット 2025-03-27 21.24.45.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4037248/42c6333b-099b-4fbb-b38b-1bcd1a4dd478.png)


作成されたユーザーは、Active Directory の GUI 管理ツールからも以下のように確認できます。
説明 欄に社員番号が正しく入力されていることがわかります。
![スクリーンショット 2025-03-27 21.25.32.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4037248/54c9aee6-350f-45b3-b4b7-5927feafa322.png)

---

## PowerShell で AD ユーザー情報を CSV に出力する
以下の PowerShell スクリプトを使用して、Active Directory に登録されたユーザー情報（社員番号と名前）を CSV ファイルとして出力します。

```powershell
# 1. 出力先のCSVファイルを指定
$outputPath = "C:\temp\employee_data.csv"

# 2. Active DirectoryのOUを指定（OU=employees,DC=domain,DC=local）
$ou = "OU=employees,DC=domain,DC=local"

# 3. OU=employees に所属するユーザーを取得（description=社員番号）
$users = Get-ADUser -SearchBase $ou -Filter * -Properties description

# 4. 必要な情報だけ抽出（名前と社員番号）
$employeeData = $users | Where-Object { $_.Description } | ForEach-Object {
    [PSCustomObject]@{
        employee_id = $_.Description
        name        = $_.Name
    }
}

# 5. CSVに出力（UTF8）
$employeeData | Export-Csv -Path $outputPath -NoTypeInformation -Encoding UTF8

Write-Host "CSV出力が完了しました：$outputPath"
```

### スクリプトの解説
- 出力ファイルのパスを指定
出力結果を保存する CSV ファイルの場所（C:\temp\employee_data.csv）を変数に設定します。

- 対象のOUを指定
社員ユーザーが登録されている OU（今回は employees）を対象にします。

- ユーザー情報を取得
Get-ADUser を使って、指定した OU 内のすべてのユーザーを取得し、description 属性（＝社員番号）も含めて取り出します。

- 必要な情報だけ整形
Name（氏名）と Description（社員番号）を抽出し、PSCustomObject で employee_id と name に変換します。

- CSV形式で保存
抽出したデータを UTF-8 エンコーディングで CSV ファイルとして保存します。

### スクリプト実行結果の確認
以下は、スクリプト実行後に出力された結果です。

![スクリーンショット 2025-04-08 12.45.51.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4037248/f573aa0c-ef86-483f-9479-8d0fb7fb3d89.png)


あらかじめ指定していた C:\temp\employee_data.csv に、CSVファイルが正しく出力されていることを確認できました。

![スクリーンショット 2025-04-08 12.45.57.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4037248/c616bedf-cbac-47b0-8229-e6f0e271a220.png)



CSVファイルの中には、社員番号（employee_id）と氏名（name）が意図通りの形式で出力されていました。

---
## PowerShell で CSV を SQL Server に取り込む
以下の PowerShell スクリプトを使用して、CSVファイル（社員番号と名前）を SQL Server に取り込みます。

```powershell
# =============================
# CSV → SQLインポート処理 ＋ ログ記録
# =============================

# ログの記録を開始
$logPath = "C:\temp\ad_sql_sync_log.txt"
Start-Transcript -Path $logPath -Append

try {
    # CSVファイルのパス
    $csvPath = "C:\temp\employee_data.csv"

    # SQL Server接続情報
    $server = "DB-VM1"
    $database = "EmployeeDB"
    $table = "employee_data"

    # 接続文字列（Windows認証）
    $connectionString = "Server=$server;Database=$database;Integrated Security=True;"
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString
    $connection.Open()

    Write-Host "SQL Serverに接続しました。"

    # ① 既存データの削除
    $deleteCommand = $connection.CreateCommand()
    $deleteCommand.CommandText = "DELETE FROM $table"
    $deleteCommand.ExecuteNonQuery()
    Write-Host "既存データを削除しました。"

    # ② CSVの読み込み
    if (Test-Path $csvPath) {
        $csvData = Import-Csv -Path $csvPath
        Write-Host "CSVファイルを読み込みました：$csvPath"

        # ③ データ挿入
        foreach ($row in $csvData) {
            $employeeId = $row.employee_id
            $name = $row.name

            if (-not [string]::IsNullOrWhiteSpace($employeeId) -and -not [string]::IsNullOrWhiteSpace($name)) {
                $insertQuery = "INSERT INTO $table (employee_id, name) VALUES ('$employeeId', N'$name')"
                $command = $connection.CreateCommand()
                $command.CommandText = $insertQuery
                $command.ExecuteNonQuery()
                Write-Host "INSERT成功：$employeeId / $name"
            } else {
                Write-Warning "スキップ：不完全なデータ（社員番号または名前が空）"
            }
        }

        Write-Host "全データをインポートしました。"
    }
    else {
        Write-Error "CSVファイルが存在しません：$csvPath"
    }

    $connection.Close()
    Write-Host "SQL接続を終了しました。"
}
catch {
    Write-Error "エラーが発生しました：$($_.Exception.Message)"
}
finally {
    # ログの記録を終了
    Stop-Transcript
}

```
### スクリプトの解説
- CSVファイルの読み込み
　C:\temp\employee_data.csv に保存された社員情報（employee_id, name）を読み込みます。

- SQL Serverへの接続
　Windows認証を使用して、DB-VM1 上の EmployeeDB に接続します。

- 既存データの削除
　毎回最新の状態にするため、テーブル内のデータを全件削除します。

- データの挿入
　CSVの各行を1件ずつ INSERT。

---

### スクリプト実行結果の確認
PowerShell スクリプトから SQL Server に接続しようとしたところ、以下のような接続エラーが発生しました。

![スクリーンショット 2025-04-08 12.49.33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4037248/605b312b-f5f5-45a3-87f8-f3a25ae96205.png)

>SQL Server に接続しようとしたけど、サーバーが見つからないか、アクセスできなかったという内容。


#### Test-NetConnection による接続確認（失敗）
まず、AD-VM1 から DB-VM1 に対してポート 1433 の接続確認を行ったところ、失敗しました。名前解決は成功しているが、ポート 1433 への TCP 接続は失敗している状態です。

![スクリーンショット 2025-03-29 11.45.33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4037248/3d431485-1270-4fb2-85ca-1114981b5d61.png)

#### netstat でポートのリッスン状態を確認（未リッスン）
DB-VM1 上で netstat を実行しても、ポート 1433 のリッスン状態が確認できませんでした。SQL Server 側でポート 1433 が開いていない可能性が高い。。
![スクリーンショット 2025-03-29 11.44.33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4037248/6917c24c-4717-4bf8-a27b-4f27a871a5bd.png)

#### SQL Server 構成マネージャーで確認したところ、TCP/IP が無効化されていた
SQL Server の構成マネージャーを開いてみると、TCP/IP がデフォルトで無効になっていることがわかりました。
![スクリーンショット 2025-03-29 11.51.00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4037248/886a3f27-a891-4ce3-8e16-8e6887ee082f.png)

そのため、TCP/IP の設定を開いて、各 IP アドレスに対して「有効＝はい」に変更します。
![スクリーンショット 2025-03-29 11.46.38.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4037248/d7e55703-f353-4c02-9d9c-3667ea1e1701.png)
有効になりました。
![スクリーンショット 2025-03-29 11.51.25.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4037248/1f6ebe26-2684-46a6-a1b1-5f65b6750174.png)
ポートがリッスン状態になりました。
![スクリーンショット 2025-03-29 11.52.14.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4037248/1b6fa11a-c140-45f6-803e-7317a7e8aad4.png)

#### ファイアウォールに受信ルールを追加
次に、ファイアウォールの受信規則を確認したところ、ポート 1433（SQL Server の既定ポート）に関するルールが存在しませんでした。

そのため、自分で受信規則を追加しました。
![スクリーンショット 2025-03-29 12.02.18.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4037248/17095b84-bcef-4028-ae07-0d44e7ae16ee.png)

再度 Test-NetConnection を実行したところ、
TcpTestSucceeded が True になり、ポート 1433 が正常に開通していることを確認できました。
![スクリーンショット 2025-03-29 11.59.24.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4037248/f3c55134-e682-491f-b15f-b3d95fb7f93a.png)


### スクリプトを再度実行した結果
PowerShellスクリプトを再度実行し、
SQL Server 上の employee_data テーブルの中身を確認しました。


SELECT * FROM employee_data; を実行すると、社員番号と氏名が問題なくインポートされていることが確認できました。

![スクリーンショット 2025-03-29 12.03.03.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4037248/2f95231e-1ce6-4428-ac5e-11411f097fe7.png)


## タスクスケジューラで定期実行を自動化
最後に、作成した2つの PowerShell スクリプトをWindows タスクスケジューラに登録し、
毎日自動で実行されるように設定しました。

---

### Export_Employee_CSV_From_AD
Active Directory から社員情報を CSV に出力するスクリプトです。
このタスクは 毎日 2:00 に実行されるように設定しています。
![スクリーンショット 2025-04-08 13.54.16.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4037248/e981e730-bd2a-46da-93ad-9405e1de1e12.png)




### Sync_EmployeeCSV_to_SQL
CSV から SQL Server にデータを取り込むスクリプトです。
こちらは 毎日 2:30 に実行するように設定しています。
![スクリーンショット 2025-04-08 13.55.03.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4037248/a458a88a-2634-4f51-8c06-a9de7af195a6.png)



これで、Active Directory 上の社員情報が
毎日自動で SQL Server に反映される仕組みが完成しました。