Site icon Franky's Web

Creation / maintenance of AD users from an SQL database

Alexander Christanell

We have recently introduced a new ERP in our company, which also includes the complete personnel administration. The software uses MS-SQL Server as a database. As our IT process is very complex for a new user and little things are often forgotten, I have tried to automate the creation as much as possible. This works quite well thanks to Powershell. I built a view in SQL Studio that contains all the information from the personnel administration:

Since I do not want to automatically create all users from the personnel master in the AD (but this is also conceivable), I have restricted the SQL select to a single personnel number in the script for creating a new user. The script looks like this:

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

The user is created with the basic information and an e-mail inbox is created. A log is created and sent.

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
{ }

I am now updating all users found in the HR master data. The information in the AD is added, the user is moved to the correct OU and, if available, the default photo is replaced with the correct photo from NETShare. If the user is still deactivated in the HR master data, it is also deactivated in the AD. A log is then sent. The script can now be integrated as a task every x hours/days.

Call via Web GUI

Since I wanted to hand over the creation and sync of a user to the helpdesk, but did not want them to change the script, I looked for a way to process the whole thing via a web call. The tool Powershell Server 2016 (available in a free version) makes this possible and parameters can also be transferred via an HTML website.

A contribution by Alexander Christanell.

Exit mobile version