Solved

Powershell - Create Active Directory Users From SQL

Posted on 2013-12-05
1
378 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article runs through the process of deploying a single EXE application selectively to a group of user.
This article explains the steps required to use the default Photos screensaver to display branding/corporate images
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

809 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