One of the most common laments I hear from overworked administrators is, "I need to quickly create a large number of user accounts in my Windows Server 2003 domain, and I think I'd wear out my mouse button if I used the Microsoft Management Console (MMC) Active Directory Users and Computers snap-in to complete the task. Is there a better way?"
Actually, there are many better ways. For example, both Windows 2003 and Windows XP ship with the Dsadd Users utility, a command-line tool that creates user accounts. A little copy-and-paste work in Notepad can create a batch file for creating any number of users in a flash. Windows 2000 Server and later ships with createusers.vbs, a VBScript script in Support Tools that creates a user account and even includes an /I: option that lets you feed it a list of usernames to create. The Net Users username /Add command has been around, if memory serves, since the Windows NT 4.0 days (and perhaps even earlier), offering another command-line user-creation tool that, again, you can transform into a powerful batch tool in Notepad.
My personal favorite is Addusers (addusers.exe), an old Microsoft Windows NT Server 4.0 Resource Kit tool that lets you export SAM and Active Directory (AD) files to simple ASCII comma-delimited (CSV) files, then import those CSV files to create users in bulk. Unfortunately, Microsoft has replaced Addusers with two other bulk tools: Comma Separated Value Data Exchange (CSVDE) and Lightweight Data Interchange Format, Data Exchange (LDIFDE). Both have their strengths and weaknesses. This month, I want to talk about CSVDE.
CSVDE Nuts and Bolts
Like Addusers, CSVDE lets you export the AD contents of a source domain to a comma-separated value ASCII file, then import a CSV file to create user accounts en masse in a target domain. But whereas Addusers understands only NT 4.0–type accounts, CSVDE understands the new user characteristics of AD users. For example, Addusers doesn't let you specify a user principal name for a user, but CSVDE does.
You can use the simplest of CSVDE commands to export the contents of AD:
csvde -f export.csv
Doing so produces a file called export.csv, which shows the current objects in the source domain's AD database. Why would you want to export from an existing domain? Because an exported CSVDE file is in roughly the format CSVDE needs to see as input for an imported file, so it can help guide you in troubleshooting CSVDE import problems. If you're having trouble creating a usable file for CSVDE and you keep getting errors because the file isn't correctly formatted, looking at a file exported from an existing domain provides a working example of what CSVDE wants to see. You can look at the exported file in Notepad, but CSV-format files are better viewed in Microsoft Excel, which presents the file's fields in orderly columns.
For optimum viewing in Excel, press Ctrl+A to select the entire file, then Format, Column, Autofit Selection to widen the viewing area for columns that have a lot of data or shrink columns that contain little or no data. A quick view of an exported AD file reveals that it's huge. Because CSVDE dumps all AD objects—Group Policy Objects (GPOs), machine accounts, OUs, the domain itself—you get a lot of records that you really don't care about if you just want to create user accounts. Therefore, use the -r ("restrict") option to tell CSVDE to export only the user objects:
csvde -f export.csv -r "(objectClass=User)"
The resulting file will have many columns, most of which you won't need when you're creating a suitable-for-import CSV file. The fields you'll probably find useful are DN (distinguished name), objectClass (user), name (user's full name), cn (same as the full name), displayName (same as the full name), givenName, sn (surname), profilePath, mail, sAMAccountName (your "pre-Windows 2000 logon name") and userPrincipalName. If you've used the Active Directory Users and Computers snap-in to populate fields—such as Manager, Office and so on—you might have others. You'll have to experiment to determine exactly which fields work beyond the ones that I've listed.
CSVDE Limitations
Now, you'd think that you could pump that CSV file back into CSVDE as an input and obtain a brand-new AD instance containing the same objects—as Addusers lets you do—but unfortunately that process won't work, for a few reasons. The primary reason is that CSVDE also exports things such as SIDs ("objectSID"), which are exportable but not importable. Trying to feed CSVDE an import file with the objectSID field (which an export offers) will result in the error message
Add error on line 2: Unwilling To Perform
The server-side error is Access to the attribute is not permitted because the attribute is owned by the Security Accounts Manager (SAM).
Don't try to understand that message from the text. It's true meaning is, "There are some things that CSVDE just plain can't do." If you restrict yourself to the fields I named earlier, you won't see the SAM error message. Also, if you create a CSV file from scratch, you must be careful to spell the field names exactly as a CSV export shows them and as I've written them here—CSVDE is case-sensitive. You can, by the way, use the -m option to tell CSVDE to skip any fields that would give it heartburn, as in the command
csvde -f export.csv -m -r "(objectClass=User)"
After you have a suitable-for-importing file, use the -i option to import it:
csvde -f filetoimport.csv -i
CSVDE has a few other limitations. First, you can't use it to set passwords. Therefore, after you create a pile of users, you'll need to set their passwords some other way—in my next column, I'll show you how to do so easily and automatically with a VBScript script. Second, all accounts that you create through CSVDE lack passwords and are disabled, but Windows 2003 domains require complex passwords by default. You'll need to enable those accounts after setting their passwords. Finally, and incomprehensibly, CSVDE can't assign user accounts to security groups. You can use the tool to create a bunch of users, but if you try to use the Member Of attribute to put them into the Accountants group, you'll get the SAM error message.
Stay Tuned...
Overall, CSVDE is a mildly disappointing Addusers successor. You can work around some of its flaws by using a bit of scripting, which I'll talk about next time.