We help IT Professionals succeed at work.

New podcast episode! Our very own Community Manager, Rob Jurd, gives his insight on the value of an online community. Listen Now!

x

New-Aduser from SQL

Ackles
Ackles asked
on
163 Views
Last Modified: 2017-03-29
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()

Open in new window


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
Comment
Watch Question

Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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.

Author

Commented:
Sorry Chris,
But what do you mean I haven't assigned the value?
It's the same as other variables...
PowerShell Developer
CERTIFIED EXPERT
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

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

Author

Commented:
Yes,  I have double checked it...
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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

Open in new window

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

Author

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

Open in new window

Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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)"

Open in new window

Author

Commented:
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?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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))
			}
		}

Open in new window

You could change that part to:
if ($attribute -notin 'Password', 'SamAccountName', 'Country') {
    $otherAttributes.Add($attribute, $dr.GetValue($i))
}

Open in new window

Author

Commented:
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
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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.

Author

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

but still doesn't take Country???
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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.

Author

Commented:
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?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

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

Author

Commented:
c is 2 character code, which works fine, but Country is the real name & doesn't accept
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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.

Author

Commented:
i understand, let me try for a while, but as for the question asked, you have answered it.
Thanks.
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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.

Author

Commented:
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!
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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.

Author

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

Open in new window

Author

Commented:
Thanks Chris!

Author

Commented:
I managed it.
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Oh good work :)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.