Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Problems creating account in AD with Powershell, data from SQL

Avatar of Ackles
AcklesFlag for Switzerland asked on
Microsoft SQL ServerActive DirectoryPowershell
57 Comments1 Solution461 ViewsLast Modified:
Hello Experts,
I have a scenario where I have data in SQL & I'm using powershell to look into SQL in a particular column "Action",
1. if the value is create, then it should create account in AD, this should be for all the entries.
2. if the value is update, then it should do something else (my aim is to do set-aduser for existing users)
3. After any of the activities is done, it should set the value of the column for all these entries to Blank or NULL.

Currently it fails to create the account, except for only 1.
It also doesn't set the value to Blank.

Can someone please help?
Regards,
A

Import-Module ActiveDirectory


# Path to create users in
$path = "OU=TestUser,OU=Test,DC=Test,DC=com"

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"

# Select statement to return new user accounts
# Needs to return "sAMAccountName" & "Password" columns
# Note: Other columns names should match AD attribute name
$sql = "Select FirstName as GivenName,
				 LastName as sn,
				 DisplayName as DisplayName,
				 samAccountName as sAMAccountName,
				 EmailAddress as mail,
				 City as l,
				 Department as Department,
				 StreetAddress as StreetAddress,
				 State as st,
				 samAccountName+'@wuestundpartner.com' as userPrincipalName,
				 PostalCode as postalcode,
				 MobilePhone as mobile,
				 OfficePhone as telephoneNumber,
				 Department as department,
				 Title as Title,
				 Office as physicalDeliveryOfficeName,
				 c as c,
				 CountryCode as co,
				 Action as Action,					
				 'Abc-123456' as Password
from GetActiveDirectoryUsers where Action = 'update' OR Action = 'create' "

###########################

$cn = new-object system.data.sqlclient.sqlconnection
$cn.ConnectionString = $connectionString
$cn.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = $sql
$cmd.connection = $cn
$dr = $cmd.ExecuteReader()

$colCount = $dr.FieldCount
$sAMAccountNameOrdinal = $dr.GetOrdinal("sAMAccountName")
$PasswordOrdinal = $dr.GetOrdinal("Password")
$DisplayNameOrdinal = $dr.GetOrdinal("DisplayName")
$ActionOrdinal = $dr.GetOrdinal("Action")

while ($dr.Read())
{
	# Get value of sAMAccountName column
	$sAMAccountName = $dr.GetValue($sAMAccountNameOrdinal)
	# Get value password column (converted to secure string for New-ADUser Cmdlet)
	$password = ConvertTo-SecureString -AsPlainText $dr.GetValue($PasswordOrdinal) -Force
	# Get value of DisplayName column
	$DisplayName = $dr.GetValue($DisplayNameOrdinal)
	$Action = $dr.GetValue($ActionOrdinal)
	
	#write-host "Creating user account..." $sAMAccountName
	
	$otherAttributes = New-Object System.Collections.HashTable
	
	# Create a hash table of attribute names and attribute values
	# Used to populate other attributes. 
	for ($i = 0; $i -le $colCount - 1; $i++)
	{
		$attribute = $dr.GetName($i)
		
		switch ($attribute)
		{
			"Password"{ } #Ignore
			"SAMAccountName" { } #Ignore
			"Action" { } #Ignore
			default
			{
				$otherAttributes.Add($attribute, $dr.GetValue($i))
			}
		}
		
	}
	# Capture Error for not resetting SQL Values in Action
	$Error.clear()
	# Create Active Directory User Account
	if ($Action -eq 'create')
	{
		try
		{
			Write-Host "Create branch executed for $sAMAccountName"
			New-ADUser -sAMAccountName $sAMAccountName -Name $DisplayName -Path $path -otherAttributes $otherAttributes -Enable $true -AccountPassword $password -PasswordNeverExpires $true 
			#Write-Host "UserID $($DisplayName) created!"
		}
		catch
		{
			Write-Host "There was a problem creating UserID $($DisplayName). The account was not created!"
			Write-Host "Error message: $($_.Exception.Message)"
		}
	}
	elseif ($Action -eq 'update')
	{
		Write-Host "Update is triggered for $sAMAccountName"
	}
}
$dr.Close()
# If the code runs successfully, then Clear the value from SQL in Action
if (!$Error)
{
	$updateqry = "update dbo.GetActiveDirectoryUsers set Action = ' ' where Action = 'Yes';"
	$cmd.CommandText = $updateqry
	$cmd.ExecuteNonQuery()
}
$cn.Close()
ASKER CERTIFIED SOLUTION
Avatar of Chris Dent
Chris DentFlag of United Kingdom of Great Britain and Northern Ireland imagePowerShell Developer
Commented:
This problem has been solved!
Unlock 1 Answer and 57 Comments.
See Answers