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

Last updated at 2025-04-14Posted at 2025-04-14

概要

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

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

※詳しい全体構成については、【第0回】Azureで社内システム再現（オンプレ編）｜構成図と動作の流れで紹介しています。

システム構成（今回の対象範囲）

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

この記事では、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 に設定しています。

作成されたユーザーは、Active Directory の GUI 管理ツールからも以下のように確認できます。
説明欄に社員番号が正しく入力されていることがわかります。

PowerShell で AD ユーザー情報を CSV に出力する

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

# 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 ファイルとして保存します。

スクリプト実行結果の確認

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

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

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

PowerShell で CSV を SQL Server に取り込む

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

# =============================
# 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
}