troubleshooting Question

New-Aduser from SQL

Avatar of Ackles
AcklesFlag for Switzerland asked on
Active DirectoryPowershell
27 Comments1 Solution191 ViewsLast Modified:
Hello,
I got a problem with the attached script:
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+'@test.com' as userPrincipalName,
					PostalCode as postalcode,
				 MobilePhone as mobile,
				 OfficePhone as telephoneNumber,
				 Department as department,
				 Title as Title,
				 Office as physicalDeliveryOfficeName,
				 Country as co,
				 'Abc-123456' as Password
from GetActiveDirectoryUsers where Action = 'yes' "

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

$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")

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
	
	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
			default
			{
				$otherAttributes.Add($attribute, $dr.GetValue($i))
			}
		}
	}
	# Create Active Directory User Account
	New-ADUser -sAMAccountName $sAMAccountName -Name $DisplayName -Path $path -otherAttributes $otherAttributes -Enable $true -AccountPassword $password
	
}

$dr.Close()
$cn.Close()

The issue is in -Name, when I give the name manually it works, however the variable doesn't,  there is a space in the variable in SQL.
Other issue is Country value is not populated, when I run Get-Aduser -Filter tno -properties * (as example) for another user, it shows that the co stands for country.

Please help....

Regards,
A
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 27 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 27 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros