The Power in Powershell: Creating AD Users From a SQL Database

Part of my current job has become building automation processes for online student services. One of our goals was to get our Active Directory services populated with student data from our student information system, which uses a Microsoft SQL database. After talking with contacts from other schools that use the same system, I was introduced to Powershell.

At a recent conference I attended a presenter referred to Powershell as “Microsoft’s version of Linux bash scripting”. I would wholeheartedly agree with this description. It takes the power of .Net and brings it to the Windows command prompt.

Another nice thing about Powershell is that people have already developed Powershell scripts to interact with AD and SQL that are free to use. The script I am going to demonstrate uses Idera’s Powershell Scripts to do some of the heavy lifting.

I’ve searched the net for examples of scripts that already do this, but didn’t find anything complete enough for my particular situation, so I thought I would include my own solution.

Here is a breakdown of the script:

."C:\path to script\New-IADUser.ps1"

This is an include statement pointing to Idera’s New-IADUser script. This will allow you to later on call the function that creates a user in Active Directory.

$connString = "data source=database location;Initial catalog=database name;uid=user name;pwd=password;"
$QueryText = "SELECT * FROM user table name WHERE this='that'"

Then I’ve defined the strings for connecting to the database. The first string defines the database to connect to and the login information. The second Querytext string contains your query for selecting the desired user information from the database.

$SqlConnection = new-object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $connString
$SqlCommand = $SqlConnection.CreateCommand()
$SqlCommand.CommandText = $QueryText

This section creates a connection to the database and queries using the strings defined from the last example.

$DataAdapter = new-object System.Data.SqlClient.SqlDataAdapter $SqlCommand
$dataset = new-object System.Data.Dataset
$DataAdapter.Fill($dataset)
$data = $dataset.Tables[0]

Then we create a data table object and fill it with information returned from the database. Lastly, I assigned the table data information to a value.

foreach ($data_item in $data.Rows) {
  $name = $data_item[2] + " " + $data_item[3]
  $response = [ADSI]::Exists("LDAP://CN=" + $name + ",OU=user organizational unit,DC=mydomain,DC=com")
  if($response -ne "false") {
    New-IADUser -Name $name -sAMAccountname $data_item[0] -ParentContainer 'OU=user organizational unit,DC=mydomain,DC=com' -Password $data_item[1] -Mail $data_item[4] -EnableAccount -UserMustChangePassword
  }
}

This foreach statement loops through the various rows in the table object that we created. The $data_item values returned are assuming that the table rows look like this: id number | password | first name | last name | email. The values may vary depending on how you query your database. The third line uses ADSI to check if the user with the name in the row is the same. The next line is a conditional statement that will run the New-IADUser function if the user is not already in Active Directory.