Solved

Powershell - Create Active Directory Users From SQL

Posted on 2013-12-05
1
376 Views
Last Modified: 2014-01-29
I need to write a Powershell script to perform the following steps.

1. Pull user information from two separate tables in a SQL database (database1)
    a. Pull FirstName, LastName, Nickname, and GovernmentID fields from Table1
    b. Pull LastTerm and CampusName from Table2
    Note: Table1 and Table2 are related via an EmployeeID field that is the PK in both    tables

2. Create new AD username by concatenating the "FirstName" + "." + "LastName" fields.  
3. Create new AD passwords for each user by concatenating the first 4 characters of "FirstName" + "." + "LastName" + "." + "GovernmentID".


Additional requirements:
1. Only create accounts if an account with the same username does not already exist in AD.
2. Only create accounts if the LastTerm field is less than 1 year ago (measured in standard years)
3. If two accounts exist in SQL that have the same FirstName.LastName, but different EmployeeID's, I need to substitute the Nickname for the FirstName in both username and password generators, using the same First 4 rule in the password
4. Create each account in an OU that bears the name of the CampusName field


What would a template for this type of script look like?
0
Comment
Question by:marrj
1 Comment
 
LVL 35

Accepted Solution

by:
YZlat earned 500 total points
ID: 39701417
$conn.ConnectionString = "Server=ServerName;Database=DatabaseName;User Id=user;Password=pwd;"

$sql = "SELECT t1.FirstName, t1.LastName, t1.FirstName " '.' + t1.LastName As [UserName],  t1.Nickname, t1.GovernmentID, t2.LastTerm, t2.CampusName FROM Table1 as t1 
INNER JOIN Table2 as t2 ON t1.EmployeeID=t2.EmployeeID"
$conn.Open()

$cmd = New-Object Data.SqlClient.SqlCommand($sql, $conn)
$Reader = $cmd.ExecuteReader()

$dt = New-Object System.Data.DataTable 
$dt.Load($Reader)  

#loop through each row of the datatable
$dt.Rows | foreach {
	#for each username check if it already exists
	$User = Get-ADUser -LDAPFilter "(sAMAccountName=$_.UserName)"
 	if ($User -eq $null) {
		#user does not exist
		#check if LastTerm is less than a year ago
		$datediff = new-TimeSpan $_.LastTerm $(Get-Date);
		if ($datediff -lt 365){
			New-ADUser -Name $_.UserName
		}
		
	}
 
}

Open in new window

0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now