The following example shows how to set up users in the Active Directory whose name is saved in a SQL-database with the help of PowerShell.
The result of the bulk import will then be sent via mail.
The script could for example create new AD-accounts from an HR database once per night.
I intentionally designed the script simple to make its functions clearly visible.
Requirements for the execution are:
- Powershell since version 2.0 (no ‘special modules‘ needed)
- Access to database
- Permission to create AD user objects in the specified user-container
Index
Script: Bulk import AD users from SQL server table
Here is the PowerShell script with comments on how to do a mass import of Active Directory users from SQL server tables:
$mail = user@domain.com <# Sender- and recipient-mailbox for import-result #>
$smtpServer = “servername” <# SNMP-server (has to be adapted) #>
$con = “Data Source=localhost\SQLEXPRESS;Initial Catalog=UserDB;Integrated Security=True” <# SQL-connection character sequence#>
<# The Active-Directory container under which the new accounts are to be created #>
$containerDN = “LDAP://OU=Users,OU=DEN01,OU=US,DC=domain,DC=net”
<# SQL-query against the database… #>
$query = “SELECT LogonName FROM LogonNames WHERE … “
$adapter = new-object system.data.sqlclient.sqlDataAdapter($query,$con)
$table = new-object System.data.dataTable
$adapter.Fill($table) <# The actual query is run here… #>
<# Create reference to the AD-user-container… #>
$userContainer = New-Object System.DirectoryServices.DirectoryEntry($containerDN)
del $logFile -ErrorAction Ignore <# Deleting of the logfiles #>
<# Run for all lines of the result table… #>
foreach($row in $table.Rows)
{
$logonName = $row[“LogonName”]
<# Read value for user name from column „LogonName“… #>
try
{
<# Creation of the Active Directory user accounts #>
$newUser = $userContainer.Children.Add(“cn=$logonName“,”User”)
$newUser.Properties[“samAccountName”].Value=$logonName
$newUser.CommitChanges()
$newUser.Path >> $logFile <#AD-path of the new account in logfile #>
}
catch [Exception]
{
<# Trouble shooting #>
“Error creating user $logonName” >> $logFile
$_ >> $logFile
}
}
<# Sent logfile to mailbox #>
Send-MailMessage -SmtpServer $smtpServer -From $mail -To $mail -Subject User-Import -Attachments $logFile
Possible expansion
In this example the user is only set up with the necessary attributes ‘samAccountName’ and ‘cn’. The result is a deactivated user account without password.
In case further properties from a database should be used, the following changes are necessary:
$query = “select LogonName, Attribute1 from LogonNames”
After the line:
$newUser.Properties[“samAccountName”].Value=$logonName
a new line has to be added:
$newUser.Properties[“ldapAttribute1”].Value=$row[“Attribute1”]
Important Notice:
You are directly accessing and writing to the Active Directory!
Therefore the script should be tested carefully before you are using it in a productive environment.
The author does not assume liability for loss of data, unwanted side effects or other guarantees.
The operator himself carriers the full risk from use or service of the script.
- Powershell AD SQL Mail
Leave a Reply
<p>Your email is safe with us.<br/>Information about our <a href="https://activedirectoryfaq.com/contact-us/">data protection policies</a></p>