Use Data Stored in a SQL Server Database to Create Active Directory User Accounts with PowerShell

I need a few Active Directory users created in my mikefrobbins.com test environment so I thought why come up with fake information when I could use information that I already have in a SQL Server database? The Employees table in the Northwind database looks like an easy enough candidate since all the data I need is in one table. This is about the concept and not about seeing how complicated I can make this process. Here’s the type of information I’ll pull out of this database to use for the Active Directory user accounts:

Import-Module SQLPS
Invoke-Sqlcmd -ServerInstance sql01 -Database NORTHWIND -Query `
"select top 1 FirstName, LastName, Extension, Title, Address, Region,
City, PostalCode, HomePhone from Employees"

northwind-adusers1.jpg

Here’s the PowerShell script to create the AD Users based on what’s in the Employee table in the Northwind SQL Server database. I’m running SQL Server 2012 which uses a PowerShell module. The same thing can be accomplished with SQL Server 2008 R2, but it uses a PowerShell Snap-in.

Import-Module ActiveDirectory
Import-Module SQLPS
Invoke-Sqlcmd -ServerInstance sql01 -Database NORTHWIND -Query `
"select FirstName, LastName, Extension, Title, Address, Region,
City, PostalCode, HomePhone from Employees" |
select @{l='Name';e={$_.firstname+" "+$_.lastname}},
@{l='SamAccountName';e={$_.firstname.tolower().substring(0,1)+$_.lastname.tolower()}},
@{l='UserPrincipalName';e={$_.firstname.tolower().substring(0,1)+$_.lastname.tolower(
)+"@mikefrobbins.com"}},
@{l='DisplayName';e={$_.firstname+" "+$_.lastname}},
@{l='GivenName';e={$_.firstname}},
@{l='Surname';e={$_.lastname}},
@{l='OfficePhone';e={$_.extension}},
@{l='StreetAddress';e={$_.address}},
@{l='State';e={$_.region}},
title, homephone, city, postalcode |
New-ADUser -Path "OU=NorthwindUsers,DC=mikefrobbins,DC=com" -PassThru |
select Name, SamAccountName, UserPrincipalName | ft -auto

northwind-adusers2.jpg

Let’s validate the first user listed above in Active Directory:

Import-Module ActiveDirectory
Get-ADUser -Identity 'ndavolio' | fl *

northwind-adusers31.jpg

The Get-ADUser PowerShell cmdlet is what I call “pre-filtered”. You can see in the previous screenshot that the property count is 10, so what happened to all the other properties I set? In order to see all of the properties, you have to use the -properties parameter and specify an “*":

Import-Module ActiveDirectory
Get-ADUser -Identity 'ndavolio' -Properties *

northwind-adusers4.jpg

As you can see there are a ton of properties. Let’s select only the ones we set when creating the user account:

Import-Module ActiveDirectory
Get-ADUser -Identity 'ndavolio' -Properties Name, SamAccountName, UserPrincipalName, `
DisplayName, GivenName, Surname, OfficePhone, StreetAddress, State, title, `
homephone, city, postalcode

northwind-adusers5.jpg

Now anytime I need some test Active Directory users created, I can create the Northwind ones in less than a second:

Measure-Command {
Import-Module ActiveDirectory
Import-Module SQLPS
Invoke-Sqlcmd -ServerInstance sql01 -Database NORTHWIND -Query `
"select FirstName, LastName, Extension, Title, Address, Region,
City, PostalCode, HomePhone from Employees" |
select @{l='Name';e={$_.firstname+" "+$_.lastname}},
@{l='SamAccountName';e={$_.firstname.tolower().substring(0,1)+$_.lastname.tolower()}},
@{l='UserPrincipalName';e={$_.firstname.tolower().substring(0,1)+$_.lastname.tolower(
)+"@mikefrobbins.com"}},
@{l='DisplayName';e={$_.firstname+" "+$_.lastname}},
@{l='GivenName';e={$_.firstname}},
@{l='Surname';e={$_.lastname}},
@{l='OfficePhone';e={$_.extension}},
@{l='StreetAddress';e={$_.address}},
@{l='State';e={$_.region}},
title, homephone, city, postalcode |
New-ADUser -Path "OU=NorthwindUsers,DC=mikefrobbins,DC=com"
}

northwind-adusers61.jpg

ยต