New-Aduser from SQL

You haven't assigned a value to $DisplayName (which is feeding the -name parameter) in your script.

For Country, you'd have to debug it to see what value is being passed for that attribute.

Sorry Chris,
But what do you mean I haven't assigned the value?
It's the same as other variables...

Thanks,
I did it like this:
# 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
      $DisplayName = $dr.GetValue($DisplayNameOrdinal)

& seems to work, but how do I get to country?
Sorry

I can't see anything wrong with your approach to "co". Are you certain you're getting a value from the database?

Yes,  I have double checked it...

You're hitting an AD limitation on that attribute.

I'd start by trying the "Country" parameter of New-ADUser. That sets the "c" attribute. c, co and countryCode are all related to each other.

New-ADUser -sAMAccountName $sAMAccountName -Name $DisplayName -Path $path -otherAttributes $otherAttributes -Enable $true -AccountPassword $password -Country $Country

Select allOpen in new window

That means you'll have to create a variable for Country as well.

Can you please have a look at the code & see where I messed up....

<#	
	
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 Country,
				 '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")
$DisplayNameOrdinal = $dr.GetOrdinal("DisplayName")
$CountryOrdinal = $dr.GetOrdinal("Country")

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
	$DisplayName = $dr.GetValue($DisplayNameOrdinal)
	$Country = $dr.GetValue($Country)
	
	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
	

	try
	{
		
		New-ADUser -sAMAccountName $sAMAccountName -Name $DisplayName -Path $path -otherAttributes $otherAttributes -Enable $true -AccountPassword $password -ChangePasswordAtLogon $true -Country $Country
		
		Write-Host "UserID $($DisplayName) created!"
	}
	catch
	{
		Write-Host "There was a problem creating UserID $($DisplayName). The account was not created!"
	}
}

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

Select allOpen in new window

Looks good, but change your catch block a bit if it's not playing:

		Write-Host "There was a problem creating UserID $($DisplayName). The account was not created!"
                Write-Host "Error message: $($_.Exception.Message)"

Select allOpen in new window

Thanks for that, now it shows:

There was a problem creating UserID Test Now. The account was not created!
Error message: The specified directory service attribute or value does not exist
Parameter name: Country

Can you please see what am I missing?

Ahh it's still being added to your hashtable which is filling otherAttributes.

		switch ($attribute)
		{
			"Password"{ } #Ignore
			"SAMAccountName" { } #Ignore
			"Country" { } # ignore
			default
			{
				$otherAttributes.Add($attribute, $dr.GetValue($i))
			}
		}

Select allOpen in new window

You could change that part to:

if ($attribute -notin 'Password', 'SamAccountName', 'Country') {
    $otherAttributes.Add($attribute, $dr.GetValue($i))
}

Select allOpen in new window

So I made these changes:

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))
                  }
            } #>
            
            if ($attribute -notin 'Password', 'SamAccountName', 'Country')
            {
                  $otherAttributes.Add($attribute, $dr.GetValue($i))
            }
      }
      # Create Active Directory User Account
      

& I got error:

There was a problem creating UserID Test Now. The account was not created!
Error message: A value for the attribute was not in the acceptable range of values

Ouch, no luck there then. It's expecting a 2-character country code. I guess you just have a country name?

You'll potentially need a lookup table to convert names to ISO country codes.

I tried that, it accepts
 c as c,
CountryCode as co,

but still doesn't take Country???

You mean it's been accepting them in otherAttributes? This is likely true, but you also said it wasn't filling them in, right?

Country is validating, either against a list of ISO country codes, or that it's a 2-character code.

The problem is that if i query aduser in powershell it shows it has accepted the 2.
If I give country explicitly then it shows in GUI, but when I pass country in other attributes, it doesn't accept?

Country (via the parameter) is setting the attribute "c", or it should be.

c is 2 character code, which works fine, but Country is the real name & doesn't accept

The "co" parameter, Text-Country, is as you say, a string. But it has rules tied to updates. Unfortunately I'm not in a position where I can test the rules. No AD domain to play with.

There's never been anything wrong with your code with respect to updating those. The problem is purely one of what the directory is willing to accept and without a system to play with I can't give you strict answers about the boundaries.

i understand, let me try for a while, but as for the question asked, you have answered it.
Thanks.

Sorry I can't give you something more definite. I'm just a regular user at the moment, it's been a while since I had write access to Active Directory.

Chris,
Is it possible that after the accounts are created, the script sets the value of Column "Action" to blank instead of Yes?
If you see in the code I posted above, I am pulling the values for entries where Action is Yes..

from GetActiveDirectoryUsers where Action = 'yes' "

I want this to be at the end of the script, so that next time I don't have to worry about already created accounts.

Thanks!

I'll be an SQL update, you can do that with the client. You might want to restructure slightly so you only update the database if you successfully created the user. I'll try and take a look later on today.

That would be just Awesome if you could have a look!!!

Here is the complete code as of now, it does create a user, I added some code at the bottom to set the value for Action to be blank, but I get an error "There is already an open DataReader associated with this command"

I'm marking the Question Answered, however, if you could help me on this, I would be Really Grateful!
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+'@test.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,
						
				 'Abc-123456' as Password
from GetActiveDirectoryUsers where Action = 'yes' "
#Country as co,	
###########################

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


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
	$DisplayName = $dr.GetValue($DisplayNameOrdinal)
#	$Country = $dr.GetValue($Country)
	
	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
	try
	{
		
		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)"
	}
	
	$updateqry = "update dbo.GetActiveDirectoryUsers set Action = ' ' where Action = 'Yes';"
	$cmd.CommandText = $updateqry
	$dr = $cmd.ExecuteNonQuery()
	
}


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

Select allOpen in new window

Thanks Chris!

I managed it.

Oh good work :)