Zenn
💭

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

に公開
Azure
Windows
自動化
初心者向け
クラウド
tech

概要

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

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

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

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

azure_onpre_joutai＿連携_タスクスケジュール.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

作成されたユーザーは、Active Directory の GUI 管理ツールからも以下のように確認できます。
説明 欄に社員番号が正しく入力されていることがわかります。
スクリーンショット 2025-03-27 21.25.32.png

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

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

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

スクリーンショット 2025-04-08 12.45.51.png

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

スクリーンショット 2025-04-08 12.45.57.png

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
}

スクリプトの解説

  • 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

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

Test-NetConnection による接続確認（失敗）

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

スクリーンショット 2025-03-29 11.45.33.png

netstat でポートのリッスン状態を確認（未リッスン）

DB-VM1 上で netstat を実行しても、ポート 1433 のリッスン状態が確認できませんでした。SQL Server 側でポート 1433 が開いていない可能性が高い。。
スクリーンショット 2025-03-29 11.44.33.png

SQL Server 構成マネージャーで確認したところ、TCP/IP が無効化されていた

SQL Server の構成マネージャーを開いてみると、TCP/IP がデフォルトで無効になっていることがわかりました。
スクリーンショット 2025-03-29 11.51.00.png

そのため、TCP/IP の設定を開いて、各 IP アドレスに対して「有効＝はい」に変更します。
スクリーンショット 2025-03-29 11.46.38.png
有効になりました。
スクリーンショット 2025-03-29 11.51.25.png
ポートがリッスン状態になりました。
スクリーンショット 2025-03-29 11.52.14.png

ファイアウォールに受信ルールを追加

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

そのため、自分で受信規則を追加しました。
スクリーンショット 2025-03-29 12.02.18.png

再度 Test-NetConnection を実行したところ、
TcpTestSucceeded が True になり、ポート 1433 が正常に開通していることを確認できました。
スクリーンショット 2025-03-29 11.59.24.png

スクリプトを再度実行した結果

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

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

スクリーンショット 2025-03-29 12.03.03.png

タスクスケジューラで定期実行を自動化

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

Export_Employee_CSV_From_AD

Active Directory から社員情報を CSV に出力するスクリプトです。
このタスクは 毎日 2:00 に実行されるように設定しています。
スクリーンショット 2025-04-08 13.54.16.png

Sync_EmployeeCSV_to_SQL

CSV から SQL Server にデータを取り込むスクリプトです。
こちらは 毎日 2:30 に実行するように設定しています。
スクリーンショット 2025-04-08 13.55.03.png

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

Discussion