Creation / maintenance of AD users from an SQL database

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:

####################
# New AD user of SQL tables
####################
# global variables
$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 = "Create new user from ERP"
$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"
# Delete the log file
del $logFile -ErrorAction Ignore
# Connect to SQL and start 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_FIRSTNAME
Description="This user is NEW - Sync pending"
GivenName=$Item.PERS_FIRSTNAME
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 'Archiving'
Enable-Mailbox -Identity $Item.PERS_NR -Archive -ArchiveDatabase 'ADB01'
Start-Sleep -s 20
Enable-UMMailbox -Identity $Item.PERS_NR -UMMailboxPolicy 'Default policy for UM' -Extensions $Item.PERS_TEL_DW -SIPResourceIdentifier $Item.PERS_EMAIL
Write-Host "UserID $($newUserID.sAMAccountName) created!" -ForegroundColor green
"the user with the UserID: $($newUserID.sAMAccountName) has been created!" >> $logFile
$_ >> $logFile
}
Catch{
Write-Host "There was a problem creating the user: UserID $($newUserID.sAMAccountName). The account was not created!" -ForegroundColor Red
"There was a problem creating the user: $newUserID.$samAccountName" >> $logFile
$_ >> $logFile
}
}
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.

Since some information is still missing for the user (wrong OU, address, telephone, superior, organization, picture,...) a sync script has to be built. I copy the variable part from the previous script, as this contains all the information. I have removed the restriction to a single user:

####################
# Update AD of SQL tables
####################
# global variables
$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 = "Error during AD sync from ERP"
$logFile = "C:\scripts\log.txt"
$ADPwd = "YourDefaultPassword"
$ADphotobasepath = "\\domain\NETLOGON\media\ADuserphoto\"
$Blank = ""
$OfficePhone = "+xx xxxx xxxx"
# Delete the log file
del $logFile -ErrorAction Ignore
# Connect to SQL and start 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()
# import data from SQL
foreach($Item in $Table)
{
# AD General
$givenName = $Item.PERS_FIRSTNAME
$initials = $Item.PERS_KBEZ
$sn = $Item.PERS_NAME
$displayName = $Item.PERS_NAME + " " + $Item.PERS_FIRSTNAME
$Description = $null
$wWWHomePage = "http://www.domain.tld"
# AD Address
$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 = "Austria"
$c = "AT"
# AD Account
$samAccountName = $Item.PERS_NR
$accountExpires ="[PERS_DTAENDG]"
# AD Profile
# AD phone number
$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 Organization
$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 attributes extended
$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_FIRSTNAME + '.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 with the defined information
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
# Move user to correct OU
Get-ADUser $sAMAccountName | Move-ADObject -TargetPath $ADTargetOU
#Error detection
If (!$?)
#Error during sync
{ "User:[" + $sAMAccountName + "] sync with errors" >> $logFile }
Else
#no error during sync
{ }
}
}
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.