Problems creating account in AD with Powershell, data from SQL

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()

Select allOpen in new window