Erstellung / Wartung der AD Benutzer aus einer SQL Datenbank

by

In unserem Unternehmen haben wir vor kurzem ein neues ERP eingeführt, welches unter anderem auch die komplette Personalverwaltung beinhaltet. Die Software nutzt als Datenbank MS-SQL Server. Da unser Prozess in der EDV für einen neuen Benutzer sehr komplex ist, und oft Kleinigkeiten vergessen werden, habe ich versucht die Erstellung so gut wie möglich zu automatisieren. Das geht ja dank Powershell recht gut. Ich habe mir dazu einen View im SQL Studio gebaut, der alle Informationen aus der Personalverwaltung enthält:

SQL

SQL

Da ich nicht automatisch alle Benutzer aus dem Personalstamm im AD anlegen möchte (ist aber auch denkbar) habe ich im Script für das Anlegen eines neuen Benutzers den SQL Select auf eine einzelne Personalnummer eingeschränkt. Das Script dazu schaut wie folgt aus:

####################
# Neuer AD Benutzer von SQL Tabellen
####################

# globale Variablen
$newuser = "TEST"

$SQLServer = "DBSRV\INSTANZ"
$SQLDBName = "YOURDB"
$SQLsecurity = "Integrated Security = True"
$SQLText = "SELECT  * FROM dbo.PERSSTAMM WHERE PERS_NR = '" + $newuser + "'"

$EXServer = "server.domain.tld"
$EXSession = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri http://$EXServer/PowerShell/ -Authentication Kerberos

$SMTPServer = "server.domain.tld"
$logMailrecipient = "empfänger@mail.info"
$logMailsender = "absender@mail.info"
$logMailSubject = "Neuer Benutzer aus ERP ersellen"

$logFile = "C:\scripts\log.txt"

$ADTargetOU="OU=user,DC=DOMAIN,DC=TLD"
$ADPwd = "YourDefaultPassword"
$ADphoto = [byte[]](Get-Content "\\domain\NETLOGON\media\ADuserphoto\default.jpg" -Encoding byte) 

$Blank = ""
$OfficePhone = "+xx xxxx xxxx"

# Löschen des Logfiles
del $logFile -ErrorAction Ignore 

# Verbinde zu SQL und starte QUERY
$SqlCon = New-Object System.Data.SqlClient.SqlConnection
$SqlCon.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; $SQLsecurity"
$SqlCon.Open()

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlCon
$SqlCmd = $SqlCon.CreateCommand()
$SqlCmd.CommandText = $SQLText

$Result = $SQLCmd.ExecuteReader()
$Table = New-Object System.Data.DataTable
$Table.Load($Result)
$SqlCon.Close()

foreach($Item in $Table)
{
    $newUserID=@{
        Name=$Item.PERS_NAME+" "+$Item.PERS_VORNAME
        Description="Dieser User ist NEU - Sync ausständig"
        GivenName=$Item.PERS_VORNAME
        Surname=$Item.PERS_NAME
        DisplayName=$Item.PERS_NAME+" "+$Item.PERS_VORNAME
        UserPrincipalName=$Item.PERS_EMAIL
        sAMAccountName=$Item.PERS_NR
        homeDrive = "Z:"
        homeDirectory = "\\netshare\userdata\home\" + $Item.PERS_NR
     
    }
          

    Try{
        $newUserID
        New-ADUser @newUserID -Path $ADTargetOU -ErrorAction Stop -AccountPassword (ConvertTo-SecureString $ADPwd -AsPlainText -Force) -Passthru
        Enable-ADAccount -Identity $newUserID.sAMAccountName
        Set-ADUser -Identity $newUserID.sAMAccountName -ChangePasswordAtLogon $true -Replace @{thumbnailPhoto=$ADphoto}
		Import-PSSession $EXSession
        Enable-Mailbox -Identity $Item.PERS_NR -Alias $Item.PERS_EMAIL_ALIAS -Database 'MDB01' -AddressBookPolicy 'Default' -RetentionPolicy 'Archivierung'
        Enable-Mailbox -Identity $Item.PERS_NR -Archive -ArchiveDatabase 'ADB01'
		Start-Sleep -s 20
        Enable-UMMailbox -Identity $Item.PERS_NR -UMMailboxPolicy 'Standardrichtlinie für UM' -Extensions $Item.PERS_TEL_DW -SIPResourceIdentifier $Item.PERS_EMAIL
        Write-Host "UserID $($newUserID.sAMAccountName) erstellt!" -ForegroundColor green
		"der User mit der UserID: $($newUserID.sAMAccountName) wurde erstellt!" >>  $logFile
             $_ >> $logFile
       }
    Catch{
        Write-Host "Es gab ein Problem mit dem erstellen des Users: UserID $($newUserID.sAMAccountName). Der Accoutn wurde nicht erstellt!" -ForegroundColor Red
        "Es gab ein Problem mit dem erstellen des Users: $newUserID.$samAccountName" >>  $logFile
             $_ >> $logFile
    }
}
<# Versenden des Logfiles an eine Mailbox #>
If (Test-Path $logFile)
{ Send-MailMessage -From $logMailsender -Subject $logMailSubject -To $logMailrecipient -Attachments $logFile -SmtpServer $SMTPServer }
Else
{ }

Es wird der Benutzer mit den Basisinformationen angelegt und ein E-Mail Postfach erstellt. Ein Log wird erstellt und versandt.

AD

Da nun aber noch einige Informationen beim Benutzer fehlen, (Falsche OU, Adresse, Telefon, Vorgesetzter, Organisation,Bild,…) muss noch ein Syncscript gebaut werden. Den Variablenteil kopiere ich aus dem vorigen Script, da dieser alle Informationen enthält. die Einschränkung auf einen einzelnen User habe ich entfernt:

####################
# Update AD von SQL Tabellen
####################

# globale Variablen
$newuser = "TEST"

$SQLServer = "DBSRV\INSTANZ"
$SQLDBName = "YOURDB"
$SQLsecurity = "Integrated Security = True"
$SQLText = "SELECT  * FROM dbo.PERSSTAMM"

$SMTPServer = "server.domain.tld"
$logMailrecipient = "empfänger@mail.info"
$logMailsender = "absender@mail.info"
$logMailSubject = "Fehler bei AD-Sync aus ERP"

$logFile = "C:\scripts\log.txt"

$ADPwd = "YourDefaultPassword"
$ADphotobasepath = "\\domain\NETLOGON\media\ADuserphoto\"

$Blank = ""
$OfficePhone = "+xx xxxx xxxx"

# Löschen des Logfiles
del $logFile -ErrorAction Ignore 

# Verbinde zu SQL und starte QUERY
$SqlCon = New-Object System.Data.SqlClient.SqlConnection
$SqlCon.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; $SQLsecurity"
$SqlCon.Open()

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlCon
$SqlCmd = $SqlCon.CreateCommand()
$SQLCmd.CommandText = $SQLText

$Result = $SQLCmd.ExecuteReader()
$Table = New-Object System.Data.DataTable
$table.Load($Result)
$SqlCon.Close()

# importiere Daten von SQL

foreach($Item in $Table)
{
# AD Allgemein
$givenName = $Item.PERS_VORNAME
$initials = $Item.PERS_KBEZ
$sn = $Item.PERS_NAME
$displayName = $Item.PERS_NAME + " " + $Item.PERS_VORNAME
$Description = $null
$wWWHomePage ="http://www.domain.tld"

# AD Adresse
$streetAddress = IF([string]::IsNullOrWhiteSpace($Item.PERS_ADRESSE1)) { $null } Else { $Item.PERS_ADRESSE1 }
$l = IF([string]::IsNullOrWhiteSpace($Item.PERS_ORT)) { $null } Else { $Item.PERS_ORT }
$st = "Tirol"
$postalCode = IF([string]::IsNullOrWhiteSpace($Item.PLZ_CD)) { $null } Else { $Item.PLZ_CD }
$co = "Österreich"
$c = "AT"

# AD Konto
$samAccountName = $Item.PERS_NR
$accountExpires ="[PERS_DTAENDG]"

# AD Profil

# AD Rufnummer
$telephoneNumber = IF([string]::IsNullOrWhiteSpace($Item.PERS_TEL)) { $Office } Else { $Item.PERS_TEL }
$mobile = IF([string]::IsNullOrWhiteSpace($Item.PERS_MOBIL)) { $null } Else { $Item.PERS_MOBIL }
$facsimileTelephoneNumber = IF([string]::IsNullOrWhiteSpace($Item.PERS_FAX)) { $null } Else { $Item.PERS_FAX }

# AD Organistation
$title = IF([string]::IsNullOrWhiteSpace($Item.PERS_FELD5_BEZ)) { $null } Else { $Item.PERS_FELD5_BEZ }
$department = IF([string]::IsNullOrWhiteSpace($Item.PERS_KSTNR_BEZ)) { $null } Else { $Item.PERS_KSTNR_BEZ }
$company = IF([string]::IsNullOrWhiteSpace($Item.PERS_CD2_BEZ)) { $null } Else { $Item.PERS_CD2_BEZ }
$manager = IF([string]::IsNullOrWhiteSpace($Item.USER_CD_VORG2)) { $null } Else { $Item.USER_CD_VORG2 }

# AD Attribute erweitert
$EmployeeID = $Item.PERS_NR
$extensionAttribute9 = IF([string]::IsNullOrWhiteSpace($Item.PERS_FELD5_BEZ2)) { '' } Else { $Item.PERS_FELD5_BEZ2 }
$extensionAttribute10 = IF([string]::IsNullOrWhiteSpace($Item.PERS_AUSWEISNR)) { '' } Else { $Item.PERS_AUSWEISNR }
$extensionAttribute12 = IF([string]::IsNullOrWhiteSpace($Item.PERS_FELD4_BEZ2)) { '' } Else { ', ' + $Item.PERS_FELD4_BEZ2 }
$extensionAttribute14 = IF([string]::IsNullOrWhiteSpace($Item.PERS_FAX_OL)) { '' } Else { $Item.PERS_FAX_OL }
$extensionAttribute15 = IF([string]::IsNullOrWhiteSpace($Item.PERS_FELD4_BEZ)) { '' } Else { $Item.PERS_FELD4_BEZ + ' ' }
$ADphotopath = $ADphotobasepath + $Item.PERS_NAME + " " + $Item.PERS_VORNAME + '.jpg'
IF (Test-Path $ADphotopath ) 
	{ $thumbnailPhoto = [byte[]](Get-Content $ADphotopath -Encoding byte) } 
	Else { $thumbnailPhoto = [byte[]](Get-Content "\\domain\NETLOGON\media\ADuserphoto\default.jpg" -Encoding byte) }

$active = IF ($Item.PERS_STATUS -eq '0') { $true } Else { $false }

$ADTargetOU = "OU=users,OU=" + $Item.PERS_KSTNR_BEZ + ",OU=" + $Item.PERS_CD2 + "," + "OU=user,DC=DOMAIN,DC=TLD"

if([string]::isNullOrEmpty($sAMAccountName.description))
{ "modifying $($sAMAccountName)"
# Update AD mit den definierten Informationen
Set-ADUser -Identity $sAMAccountName -GivenName $givenName -Initials $initials -Surname $sn -DisplayName $displayName -Description $Description -HomePage $wWWHomePage -StreetAddress $streetAddress -City $l -State $st -PostalCode $postalCode -Country $c -OfficePhone $telephoneNumber -MobilePhone $mobile -Fax $facsimileTelephoneNumber -Title $title -Department $department -Company $company -Manager $manager -EmployeeID $EmployeeID -Replace @{extensionAttribute9 = $extensionAttribute9; extensionAttribute10 = $extensionAttribute10; extensionAttribute12 = $extensionAttribute12; extensionAttribute14 = $extensionAttribute14; extensionAttribute15 = $extensionAttribute15; thumbnailPhoto = $thumbnailPhoto} -Enabled $active 
# Verschiebe User in richtige OU
Get-ADUser $sAMAccountName | Move-ADObject -TargetPath $ADTargetOU

#Fehler Ermittlung 
If (!$?)
#Fehler beim Sync
{ "User:[" + $sAMAccountName + "] sync mit Fehlern" >> $logFile }
Else 
#kein Fehler beim Sync
{ } 
}
}
<# Versenden des Logfiles an eine Mailbox #>
If (Test-Path $logFile)
{ Send-MailMessage -From $logMailsender -Subject $logMailSubject -To $logMailrecipient -Attachments $logFile -SmtpServer $SMTPServer }
Else
{ }

Ich aktualisiere nun alle Benutzer die im Personalstamm gefunden werden. Die Informationen im AD werden ergänzt, der Benutzer in die richtige OU verschoben und falls vorhanden das Standardfoto gegen das korrekte Foto vom NETShare getauscht. Sollte der User im Personalstamm noch deaktiviert sein, wird er auch im AD deaktiviert. Im Anschluss wird ein Log gesendet. Als Task kann das Script nun alle x Stunden/Tage eingebunden werden.

Aufruf via Web GUI

Da ich die Anlage und den Sync eines Users dem Helpdesk übergeben wollte, der aber nicht das Script abändern sollte habe ich dann noch eine Möglichkeit gesucht das ganze über einen Webaufruf zu verarbeiten. Das Tool Powershell Server 2016 (gibt es in einer freien Variante) ermöglicht dies und auch Parameter können über eine HTML Website übergeben werden.

PowerShell Server

PowerShell Server

Ein Beitrag von Alexander Christanell.

6 thoughts on “Erstellung / Wartung der AD Benutzer aus einer SQL Datenbank”

  2. Hallo Alexander,
    vielen Dank für den sehr guten Artikel!!!
    Wir stehen gerade vor einem ähnlichen Problem. Die Lösung mit dem Powershellserver ist natürlich dafür genial.
    Vielleicht könntest Du in einem extra Artikel nochmal auf den Powershellserver und dessen Einrichtung bzw. Scripte eingehen.

    Reply

  6. Hi!
    Sehr gut umgesetzt und sehr professionell. Außerdem gutes Skripting.
    Der Tipp mit Webaufruf schützt ja sogar noch das Coding in einer ps-Datei.
    Lg

    Reply

Leave a Comment