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

calendar Aug 9, 2012 · Active Directory PowerShell SQL Server  ·
Share on: linkedin copy

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:

1Import-Module SQLPS
2Invoke-Sqlcmd -ServerInstance sql01 -Database NORTHWIND -Query `
3"select top 1 FirstName, LastName, Extension, Title, Address, Region,
4City, 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.

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

northwind-adusers2.jpg

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

1Import-Module ActiveDirectory
2Get-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 "*":

1Import-Module ActiveDirectory
2Get-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:

1Import-Module ActiveDirectory
2Get-ADUser -Identity 'ndavolio' -Properties Name, SamAccountName, UserPrincipalName, `
3DisplayName, GivenName, Surname, OfficePhone, StreetAddress, State, title, `
4homephone, 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:

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

northwind-adusers61.jpg

µ