Importing and modifying Active Directory users with AD Bulk Users
Importing users into Active Directory (CSV, Excel)
The import file can be formatted using the CSV (comma separated value) format or Excel XLS and XLSX.
The column headers in your file (CSV,XLS,XLSX) should contain the name of the Active Directory Attribute you want to add the data to. A list of column headers/attributes can be found below. The columns can be in any order and the column headers are not case sensitive but obviously must be spelt correctly for the program to recognize them, if it comes across a column it doesn’t recognize it will ignore that column.
In the example shown below I’m using Excel to construct the file and using wildcards to speed up the creation of the file. The first row (1) contains the column headers, these are the attribute names such as givenName (first name). You don’t need to use all the attributes names just those you need, the minimum required to create a new users are shown below. This example file would create 5 new users.
Using MS SQL, My SQL or Oracle as your data source
You can use a SQL Server database or an Oracle database as your data source, below are two example connection strings for connecting the AD Bulk Users to MS SQL or Oracle. Below are some example connection strings. Enter the connection strings by clicking Open Database in the Main ribbon or via the Scheduler.
Microsoft SQL Server Connection String
Server=mydbsrv\myInstanceName;Database=myDataBase;User Id=****;Password=****;MySQL
Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;Oracle Database Connection String
Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv.domain.com)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=PROD)));User Id=****;Password=****;Updating Existing Active Directory User Accounts
AD Bulk Users can be used to update/modify existing Active Directory Users. This is done by adding the column header ‘Modify’ to the import file and setting the value to ‘TRUE’.
To update the ‘description’ and ‘telephoneNumber’ attributes for 5 users you would use a file (saved as CSV or Excel) similar to the example below.
The sAMAccountName (username) is used to locate the user in Active Directory, the Modify column with a value of TRUE tells the program to update an existing user. The other columns\attributes are those to be updated, in this case description and telephoneNumber.
If you want to update many attributes at once you can, simply add the additional column headers to your import file. For example: sAMAccountName,Modify,description,telephoneNumber,initials,employeeID,password.
Command Line Arguments
AD Bulk Users contains a built-in scheduler so you don’t need to use the command line to automate imports but if you prefer to use the Windows Task Scheduler or have another reason to use the command line you can. Use ADBulkUsersCLI.exe from the command line.
You can find the syntax and command line examples for ADBulkUsersCLI.exe below.
ADBulkUsersCLI.exe /?
To save time working out which command line arguments to use, you can simply create a schedule via the GUI and set it to ‘Run Once’ and then execute the saved Schedule by using the command line below:
adbulkuserscli.exe /schedule:NameOfSchedule
Microsoft Terminal Services Support
Import and Modify Terminal Services Home Folder and Profile Path for users. See Screenshot
Microsoft Exchange Support
Create Exchange Server Mailbox for users. See Screenshot
Wildcards
To speed up the creation of your import file/table you can use wildcards throughout your CSV file or table. You can create your own wildcards by clicking Wildcards in the ribbon.
%username% (reads the value found in the sAMAccountName column)
%firstname% (reads the value found in the givenName column)
%lastname% (reads the value found in the sn column)
%givenName% (reads the value found in the givenName column)
%sn% (reads the value found in the sn column)
%sAMAccountName% (reads the value found in the sAMAccountName column)
%userPrincipalName% (reads the value found in the userPrincipalName column)
%initials% (reads the value found in the initials column)
%email% (reads the value found in the mail column)
%firstNamefirst% (reads the first letter of the value found in the givenName column)
%firstNamelast% (reads the last letter of the value found in the givenName column)
%lastNamefirst% (reads the first letter of the value found in the sn column)
%lastNamelast% (reads the last letter of the value found in the sn column)
To create a new wildcard click the ‘Add’ button then enter a wildcard name such as employeeID and then select a column that the wildcard will read. You can use a Regular Expression if you want to manipulate the value such as reading the first or last character.
Home Folders
To set the users Home Folder add the column header ‘homeDirectory’ to your import file, the value would be a UNC path e.g. \\server\share\%username% (the wildcard %username% will insert the sAMAccountName value), to set the drive letter to be mapped on logon use homeDrive with a value like H: or P:.
You can use a local folder such as “e:\User1” by using homeFolder column with the local path followed by a semicolon and the drive letter, e.g. E:\homedrives\;z:.
Creating the home folder
The program can create the home folder whilst creating the user, use the column createHomeDirectory with the value set to True, this requires the homeDirectory column. The program will create the home folder within the share specified in the homeDirectory value, for example if the value is \\servername\Users\%username% then the program will create the a folder named jsmith (username is jsmith) with the share ‘Users’. By default the user will have Modify permission to their home folder, you can change this to Full Control under Options\Home Folder.
Alternative method of creating the users home folder
An alternative method of creating home folders is to specify the path and drive letter under Options\Home Folder, this give you some additional options. Under Options\Home Folder you can specify the permissions used and the option to create the home folder as a share, if the folder already exists then the program can re-apply permissions if necessary.
To create the users home folder add the column ‘createHomeFolder’ to your import file and set the value to TRUE for each user.
Importing into multiple OU’s
To import users into multiple OU’s from the same import file add the property ‘destinationOU’ to your import file. An LDAP path placed in this field results in the user being imported into that OU. This overrides the ‘Active Directory Container’ OU found on the settings tab. If the user has a destinationOU value present in the import file then the user is imported into that OU otherwise the user is imported into the OU found on the settings tab.
Manager Property
Within Active Directory a users Manager (Organization tab) is stored using the distinguishedName of the manager for example:
CN=John Smith,OU=Managers,DC=Domain,DC=Com
To set a users manager using AD Bulk Users you can use the distinguishedName, sAMAccountName (username of the manger) or the employeeID of the manager (version 5.0.7.9 or later). Below are three examples:
sAMAccountName,Modify,manager
pjones,True,jsmith (using managers sAMAccountName)
sAMAccountName,Modify,manager
pjones,True,123987 (using managers employeeID)
sAMAccountName,Modify,manager
pjones,True,”CN=John Smith,OU=Managers,DC=Domain,DC=Com”
When using the managers sAMAccountName or employeeID the program will need to search AD for the managers distinguishedName so these lookups will be slower than using the distinguishedName.
Running PowerShell Scripts
You can run PowerShell scripts after a user is created, modified or deleted and also at the beginning and end of the import. To add a PowerShell script click the PowerShell Scripts button in the Settings ribbon.
To execute a script against a user, add a column named “PSScripts” to your file/table and enter the Script Name as the column value. To run multiple scripts separate each Script Name with a semi-colon. The script can contain wildcards. If an error occurs during script execution, the error will be shown in the log.
Custom or unlisted attributes
It is possible to add additional and custom attributes to the program. Click Attributes in the ribbon to add a unlisted or custom attribute.
Supported Attributes List
**Columns in your file can be in any order and the column headers are not case sensitive. Below you will see the ‘Displayed Name’ which is the label used in Active Directory, the attribute name which you use as a column header in your file and an example value. You can find some sample CSV and Excel files here.
| General Tab | ||
| Displayed Name | Attribute Name (column headers) | Example Value |
| First Name | givenName | John |
| Initials | initials | JS |
| Last Name | sn | Smith |
| Display Name | displayName | John Smith |
| Description | description | Sales Manager |
| Office | physicalDeliveryOfficeName | London Office |
| Telephone Number | telephoneNumber | 0123 456 789 |
| Telephone Number (Other) | otherTelephone | 0123 4457 89 |
| [email protected] | ||
| Web Page | wWWHomePage | www.johnsmith.com |
| Web Page (Other) | url | www.John.net;www.John.org |
| Password | password | JohnsPass321 |
| Destination OU | destinationOU | OU=Sales,DC=Domain,DC=Com |
| Common Name | cn | Wildcards can be used here. |
| Modify User if already exists | Modify | True or False |
| Delete User | Delete | True or False |
| Address Tab | ||
| Displayed Name | Attribute Name (column headers) | Example Value |
| Street | streetAddress | 10 Downing St;London (Use a semi-colon for carriage return) |
| PO Box | postOfficeBox | Po Box 1 |
| City | l (Lowercase L) | London |
| State/Province | st | New York |
| Zip/Postal Code | postalCode | 614415 |
| Country | c | GB – (Click here for country codes) |
| Group Tab | ||
| Displayed Name | Attribute Name (column headers) | Example Value |
| Group | memberOf | CN=ManagersGroup,DC=Domain,DC=Com |
| Group | AddToGroup | TestGroup (Add users to multiple groups) |
| Group | RemoveFromGroup | SampleGroup |
| Group | RemoveFromAllGroups | True |
| Account Tab | ||
| Displayed Name | Attribute Name (column headers) | Example Value |
| User Logon Name | userPrincipalName | [email protected] |
| User Logon Name (Pre W2K) | sAMAccountName | JSmith (Mandatory, you must include this) |
| User must change password at next logon | mustChangePassword | True or False |
| User cannot change password | userCannotChangePassword | True or False |
| Account is Enabled | accountEnabled | True or False |
| Account is Disabled | accountDisabled | True or False |
| Use DES encryption | useDES | True or False |
| Do not require kerberos preauthentication | notRequireKerberos | True or False |
| Password never expires | passwordNeverExpires | True or False |
| Account is trusted for delegation | trustedForDelegation | True or False |
| Store password using reversable encryption | passwordReversable | True or False |
| Smart card is required for interactive logon | smartCardRequired | True or False |
| Account is sensitive and cannot be delegated | sensitiveForDelegation | True or False |
| Account Expires (use same date format as server) | expires | 01/01/2020 |
| Profile Tab | ||
| Displayed Name | Attribute Name (column headers) | Example Value |
| Profile Path | profilePath | \\server\profiles |
| Login Script | scriptPath | logon.bat |
| Home Folder (UNC Path) | homeDirectory | \\server\homedir\Jsmith |
| Drive only | homeDrive | H: |
| Home Folder (local or UNC, see notes above, legacy) | homeFolder | \\server\homedir\Jsmith;z: |
| Create Home Folder and Set Permissions | createHomeDirectory | True |
| Telephones Tab | ||
| Displayed Name | Attribute Name (column headers) | Example Value |
| Home | homePhone | 660 123 122 |
| Home (Other) | otherHomePhone | 0661 123 122 |
| Pager | pager | 1234 |
| Pager (Other) | otherPager | 123 |
| Mobile | mobile | 120 456 789 |
| Mobile (Other) | otherMobile | 121 456 789 |
| Fax | facsimileTelephoneNumber | 122 456 789 |
| Fax (Other) | otherFacsimile TelephoneNumber | 0123 456 789 |
| IP Phone | ipPhone | 01IP |
| IP Phone (Other) | otherIpPhone | 432493809 |
| Notes | info | General information (Use a semi-colon for carriage return) |
| Organization Tab | ||
| Displayed Name | Attribute Name (column headers) | Example Value |
| Title | title | Manager |
| Department | department | Sales |
| Company | company | Big Corp |
| Manager | manager | CN=Ste Jobs,OU=Managers,DC=Domain,DC=Com |
| Employee ID | employeeID | |
| Employee Type | employeeType | |
| Employee Number | employeeNumber | |
| Car License | carLicense | |
| Division | division | |
| Middle Name | middleName | |
| Room Number | roomNumber | |
| Assistant | assistant | CN=Joe Blog,OU=Managers,DC=Domain,DC=Com |
| jpegPhoto | jpegPhoto | E:\photos\%username%.jpg (25KB or less, 200×200 pixels or less) |
| Exchange Tab | ||
| Displayed Name | Attribute Name (column headers) | Example Value |
| Create Mailbox for User | mailboxEnabled | True, Required when creating a mailbox. |
| Alias | mailNickname | Jsmith *Required |
| Email addresses | proxyAddresses | SMTP:[email protected] multiple addresses? |
| Automatically update based on recipient policy | AutoUpdateOnRecipientPolicy | True or False (True if omitted), can be used in place of proxyAddresses. |
| Simple Display Name | displayNamePrintable | Jsmith |
| Forward to | altRecipient | CN=User2,OU=Managers,DC=Domain,DC=Com |
| Deliver & Forward | deliverAndRedirect | True |
| Use MAPI Rich Text | mAPIRecipient | True or False |
| Hide From Address Lists | msExchHideFromAddressLists | True or False |
| Exchange Store | ExchangeDatabase | Store1 |
| Mail-Enable User | mailEnable* | True or False *Do not use with mailboxEnabled. |
| External Address | targetAddress | Required when mail-enabling a user. |
| Disable Mailbox | mailboxDisable | Remove the mailbox not the user. |
| Disable Mail | mailDisable | Remove a mail-enabled user. |
| Exchange Attributes | ||
| Displayed Name | Attribute Name (column headers) | Example Value |
| extensionAttribute1 | extensionAttribute1 | |
| extensionAttribute2 | extensionAttribute2 | |
| extensionAttribute3 | extensionAttribute3 | |
| extensionAttribute4 | extensionAttribute4 | |
| extensionAttribute5 | extensionAttribute5 | |
| extensionAttribute6 | extensionAttribute6 | |
| extensionAttribute7 | extensionAttribute7 | |
| extensionAttribute8 | extensionAttribute8 | |
| extensionAttribute9 | extensionAttribute9 | |
| extensionAttribute10 | extensionAttribute10 | |
| extensionAttribute11 | extensionAttribute11 | |
| extensionAttribute12 | extensionAttribute12 | |
| extensionAttribute13 | extensionAttribute13 | |
| extensionAttribute14 | extensionAttribute14 | |
| extensionAttribute15 | extensionAttribute15 | |
| Terminal Services Tab | ||
| Displayed Name | Attribute Name (column headers) | Example Value |
| Profile Path | TSProfilePath | \\server\profiles\%username% |
| Home Folder | TSHomeFolder | \\server\users\%username%;z: |
| Create Home Folder | TSCreateHomeDirectory | True or False |
| Deny Logon | TSDenyLogon | True or False |
| Dial-In Tab | ||
| Displayed Name | Attribute Name (column headers) | Example Value |
| Allow Dial-In Access | msNPAllowDialin | True or False |
| Remote Control Tab | ||
| Displayed Name | Attribute Name (column headers) | Example Value |
| Remote Control | EnableRemoteControl | 0 = Disable Remote Control, 1 = Require user’s permission, 2 = Do not require user’s permission, 3 = Require user’s permission, View Only, 4 = Do not require user’s permission, View Only |
| Other | PSScripts | Name of the script |