如何使用交叉引用表创建SQL视图？MIM 2016同步场景技术问询

Hey MarcGel, let's break this down step by step—since you're solid with PowerShell/AD but new to SQL, this will lean into your existing skills while adding the SQL/MIM pieces you need:

First, replace your two CSVs with a SQL table to store the department → code → manual mapping. This makes cross-referencing easy and maintainable.

Create the mapping table with this SQL script:

CREATE TABLE DepartmentManualMap (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    DepartmentName VARCHAR(100) NOT NULL UNIQUE, -- Match AD's Department attribute values exactly
    Code VARCHAR(10) NOT NULL,
    ManualName VARCHAR(200) NOT NULL
);

Insert your existing mapping data (adjust rows to match your actual values):

INSERT INTO DepartmentManualMap (DepartmentName, Code, ManualName)
VALUES 
('Finance', 'A', 'Revenue Integrity'),
('Engineering', 'B', 'DevOps Playbook'),
('HR', 'C', 'Employee Compliance Guide');

Use your PowerShell/AD skills to pull user data into a SQL table—this acts as the bridge between AD and MIM.

First, create a table to store AD user data:

CREATE TABLE ADUserSync (
    SamAccountName VARCHAR(50) PRIMARY KEY, -- Unique identifier for users
    Department VARCHAR(100),
    DistinguishedName VARCHAR(255),
    LastSync DATETIME DEFAULT GETDATE() -- Track when data was updated
);

Then use this PowerShell script to sync AD users to SQL (adjust the OU filter and SQL server details):

Import-Module ActiveDirectory

# Get AD users (narrow down to your target OU if needed)
$adUsers = Get-ADUser -Filter * -Properties Department, DistinguishedName -SearchBase "OU=CorporateUsers,DC=yourdomain,DC=com"

# Connect to SQL Server
$sqlConn = New-Object System.Data.SqlClient.SqlConnection("Server=YOUR_SQL_SERVER;Database=YOUR_DB_NAME;Integrated Security=True;")
$sqlConn.Open()

# Upsert logic: update existing users, insert new ones
$mergeCmd = @"
MERGE INTO ADUserSync AS Target
USING (VALUES (@SamAccountName, @Department, @DistinguishedName)) AS Source (SamAccountName, Department, DistinguishedName)
ON Target.SamAccountName = Source.SamAccountName
WHEN MATCHED THEN
    UPDATE SET Department = Source.Department, DistinguishedName = Source.DistinguishedName, LastSync = GETDATE()
WHEN NOT MATCHED THEN
    INSERT (SamAccountName, Department, DistinguishedName)
    VALUES (Source.SamAccountName, Source.Department, Source.DistinguishedName);
"@

$command = New-Object System.Data.SqlClient.SqlCommand($mergeCmd, $sqlConn)
$command.Parameters.Add("@SamAccountName", [System.Data.SqlDbType]::Varchar, 50)
$command.Parameters.Add("@Department", [System.Data.SqlDbType]::Varchar, 100)
$command.Parameters.Add("@DistinguishedName", [System.Data.SqlDbType]::Varchar, 255)

# Process each user
foreach ($user in $adUsers) {
    $command.Parameters["@SamAccountName"].Value = $user.SamAccountName
    $command.Parameters["@Department"].Value = $user.Department ?? DBNull.Value # Handle null departments
    $command.Parameters["@DistinguishedName"].Value = $user.DistinguishedName
    $command.ExecuteNonQuery() | Out-Null
}

$sqlConn.Close()

Make a view that joins user data with the manual mapping—this gives you a clean, ready-to-use dataset for MIM:

CREATE VIEW UserManualAssignments AS
SELECT
    u.SamAccountName,
    u.Department,
    m.Code,
    m.ManualName
FROM ADUserSync u
LEFT JOIN DepartmentManualMap m ON u.Department = m.DepartmentName;

The LEFT JOIN ensures you retain users who don't have a matching department (you can handle these exceptions later in MIM or SQL).

Now wire this SQL data into MIM to create your mapping rules:

• Set up the SQL Management Agent

  1. Open MIM Synchronization Service Manager → Go to Agents → Create.
  2. Select SQL Server as the agent type, enter your SQL credentials, and select the UserManualAssignments view as the data source.
  3. Create run profiles for full import and delta import to keep MIM updated with fresh data.

• Build the Synchronization Rule

  1. Open the MIM Portal → Go to Synchronization Rules → New.
  2. Set the connected data source to your SQL agent, and target resource type to User (or your custom user resource).
  3. Add attribute mappings:
     • Map SamAccountName from SQL to MIM's AccountName (or your user identifier).
     • Map ManualName to a MIM attribute (e.g., a custom AssignedEnterpriseManual attribute or an unused extension attribute like ExtensionAttribute12).
  4. Set the rule precedence to run after your existing AD import rule (if you're already syncing AD users to MIM) to merge the manual assignment data correctly.

Set up a Windows Scheduled Task to run the PowerShell AD-to-SQL sync script daily (or as often as needed) to keep your SQL data fresh.

内容的提问来源于stack exchange，提问作者MarcGel