Avatar of Ackles
AcklesFlag for Switzerland

asked on 

New-Aduser from SQL

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
PowershellActive Directory

Avatar of undefined
Last Comment
Chris Dent
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Ackles
Ackles
Flag of Switzerland image

ASKER

Sorry Chris,
But what do you mean I haven't assigned the value?
It's the same as other variables...
ASKER CERTIFIED SOLUTION
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Ackles
Ackles
Flag of Switzerland image

ASKER

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
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

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

ASKER

Yes,  I have double checked it...
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Ackles
Ackles
Flag of Switzerland image

ASKER

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

Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

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

Avatar of Ackles
Ackles
Flag of Switzerland image

ASKER

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?
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

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

Avatar of Ackles
Ackles
Flag of Switzerland image

ASKER

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
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Ackles
Ackles
Flag of Switzerland image

ASKER

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

but still doesn't take Country???
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Ackles
Ackles
Flag of Switzerland image

ASKER

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?
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

Country (via the parameter) is setting the attribute "c", or it should be.
Avatar of Ackles
Ackles
Flag of Switzerland image

ASKER

c is 2 character code, which works fine, but Country is the real name & doesn't accept
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Ackles
Ackles
Flag of Switzerland image

ASKER

i understand, let me try for a while, but as for the question asked, you have answered it.
Thanks.
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Ackles
Ackles
Flag of Switzerland image

ASKER

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!
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Ackles
Ackles
Flag of Switzerland image

ASKER

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

Avatar of Ackles
Ackles
Flag of Switzerland image

ASKER

Thanks Chris!
Avatar of Ackles
Ackles
Flag of Switzerland image

ASKER

I managed it.
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

Oh good work :)
Active Directory
Active Directory

Active Directory (AD) is a Microsoft brand for identity-related capabilities. In the on-premises world, Windows Server AD provides a set of identity capabilities and services, and is hugely popular (88% of Fortune 1000 and 95% of enterprises use AD). This topic includes all things Active Directory including DNS, Group Policy, DFS, troubleshooting, ADFS, and all other topics under the Microsoft AD and identity umbrella.

86K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo