?
Solved

Powershell - Create Active Directory Users From SQL

Posted on 2013-12-05
1
Medium Priority
?
387 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 35

Accepted Solution

by:
YZlat earned 2000 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Uncontrolled local administrators groups within any organization pose a huge security risk. Because these groups are locally managed it becomes difficult to audit and maintain them.
Here's a look at newsworthy articles and community happenings during the last month.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Suggested Courses

765 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