Link to home
Create AccountLog in
Avatar of Ackles
AcklesFlag for Switzerland

asked on

Problems creating account in AD with Powershell, data from SQL

Hello Experts,
I have a scenario where I have data in SQL & I'm using powershell to look into SQL in a particular column "Action",
1. if the value is create, then it should create account in AD, this should be for all the entries.
2. if the value is update, then it should do something else (my aim is to do set-aduser for existing users)
3. After any of the activities is done, it should set the value of the column for all these entries to Blank or NULL.

Currently it fails to create the account, except for only 1.
It also doesn't set the value to Blank.

Can someone please help?
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+'@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()

Open in new window

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

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Ackles

ASKER

Thanks Chris,
That seemed to work!
I have to check other issues, but have to run out.... sorry
Will update later.
Thanks,
A
Avatar of Ackles

ASKER

Just a quick one, out of the three accounts, 2 were created & 1 got error: Server is unwilling to process the request?
That would suggest one of the attribute values you're pushing is invalid. For example, if you were trying to push a blank attribute that might happen. If this hypothesis is true, you can counter it by changing this line:
$otherAttributes.Add($attribute, $dr.GetValue($i))

Open in new window

To this:
$value = $dr.GetValue($i)
if ($null -ne $value) {
    $otherAttributes.Add($attribute, $value)
}

Open in new window

Note: We have $null on the left hand side of the expression above to properly handle array values. And we don't use implicit boolean ("if ($value)") because 0 and $false are legitimate values in some cases.
Avatar of Ackles

ASKER

I did that, but still have the same error...
first of all I am not a powershell scriptor but I think you can do it through try and catch command


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;"
$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
| Foreach-Object {
try {$x = get-aduser "$($_.Old)"; $OldExists = $TRUE;} catch { $OldExists = $FALSE; }
#try {$x = get-adgroup "$($_.New)"; $NewExists = $TRUE;} catch  { $NewExists = $FALSE; }
If ($OldExists -eq $TRUE ) { Set-ADUser -Name $_.New -Path $path -PassThru } else { Write-Host "User already Exist" } |New-ADuser  -Name sAMAccountName -Path $path -PassThru
}

Open in new window

You could, but using exception handling to create what amounts to an if statement is a bad practice.

For example, in this case, if something goes wrong with the first of your commands (Get-ADUser) for some other reason (like a syntax error), you immediately flag the account for creation. This is not a good test at all.
> I did that, but still have the same error...

You'll have to drop all the attributes out to see exactly what you're pushing in. If there are any obvious problems those can be handled, One thing we didn't test for above is DBNULL. I seem to remember that doesn't present quite as cleanly to PowerShell as the test we've used above.
Avatar of Ackles

ASKER

Thanks Tahir,
Chris, I would like to resolve the issue with the existing script.
I have checked both the created & faulty accounts, they have exactly the same values.
Any thoughts?
Avatar of Ackles

ASKER

Chris,
So you are saying that I only leave the Name attribute & delete all the rest?
Constraint violation perhaps. Can you share the values it's pushing in?
> So you are saying that I only leave the Name attribute & delete all the rest?

Not really, but it's one approach. Reduce the number of fields you're pushing until the error goes away, then add them back one at a time.
Avatar of Ackles

ASKER

so, I removed -otherAttributes $otherAttributes from new-aduser & it worked & made all the accounts.
Now, how do I figure out where did I mess up?
Add the attributes back one at a time unless there's something really obvious in the set.
Avatar of Ackles

ASKER

So 4 Attributes:
Office as physicalDeliveryOfficeName,
 MobilePhone as mobile,
 OfficePhone as telephoneNumber,
Title as Title,

These are the faulty attributes, they were empty....
That might be the part that needs a DBNull check. Let's try...
$value = $dr.GetValue($i)
if ($null -ne "$value") {
    $otherAttributes.Add($attribute, $value)
}

Open in new window

The extra quotes around $value in the test might be sufficient to prevent it adding those fields to attributes.
Avatar of Ackles

ASKER

Is there a way,
if value is empty, ignore & don't throw error....
Not with the AD commands, we need to stop it getting added to the set in the first place. That's what the if statement above is attempting to do. We just need to tweak it to squash the DBNULL values. I've had this problem pulling from SQL (and pushing into AD) before.
Avatar of Ackles

ASKER

Sorry about delay, so I put extra quotes but the problem persists....
Can you have it tell us about the values please.
$value = $dr.GetValue($i)
Write-Host "Value: $value $($value.GetType()) isNull? $($null -eq $value)"

Open in new window

Avatar of Ackles

ASKER

Value: Test string isNull? False
Value: First string isNull? False
Value: Test First string isNull? False
Value: test.first@test.com string isNull? False
Value: Zürich string isNull? False
Value: Zürich string isNull? False
Value: CH string isNull? False
Value: 756        string isNull? False
Value: Team Z string isNull? False
Value:  5 string isNull? False
Value: tfi@test.com string isNull? False
Value: 8001 string isNull? False
Value:  string isNull? False
Value: +45 string isNull? False
Value: Team Z string isNull? False
Value: Manager string isNull? False
Value: xxx string isNull? False
Create branch executed for tfi
What if: Performing operation "New" on Target "CN=Test First,OU=TestUser,OU=test,DC=test,DC=com".
Avatar of Ackles

ASKER

It shows the values are not null, when I run -whatif
> Value: Manager string isNull? False

What exactly do you have in there? Because if it's a name (username, for example) you'll get an error. That value *must* be a distinguishedName.

Otherwise we could try killing the whitespace on some of the values, but they shouldn't break it.
Avatar of Ackles

ASKER

Sorry, I'm lost now...
it's Title.
Avatar of Ackles

ASKER

Question is, why they work for some & not for others....
Ack sorry, I should have read that a little more carefully. Or perhaps I should have included the attribute name in the output :-D
$value = $dr.GetValue($i)
Write-Host "Name: $attribute; Value: $value $($value.GetType()) isNull? $($null -eq $value)"

Open in new window

That might help chase down constraints...
Avatar of Ackles

ASKER

Name: GivenName; Value: Test string isNull? False
Name: sn; Value: First string isNull? False
Name: DisplayName; Value: Test First string isNull? False
Name: mail; Value: test.first@test.com string isNull? False
Name: l; Value: Zürich string isNull? False
Name: st; Value: Zürich string isNull? False
Name: c; Value: CH string isNull? False
Name: co; Value: 756        string isNull? False
Name: Department; Value: Team Z string isNull? False
Name: StreetAddress; Value: 5 string isNull? False
Name: userPrincipalName; Value: tfi@test.com string isNull? False
Name: postalcode; Value: 8001 string isNull? False
Name: mobile; Value:  string isNull? False
Name: telephoneNumber; Value: +445 string isNull? False
Name: department; Value: Team Z string isNull? False
Name: Title; Value: Manager string isNull? False
Name: physicalDeliveryOfficeName; Value: xxx string isNull? False
Create branch executed for tfi
What if: Performing operation "New" on Target "CN=Test First,OU=TestUser,OU=test,DC=test,DC=com".
Are any of those fields different for the account that works properly? The only thing that stands out is the trailing white space on the co field (that one again :)).
Avatar of Ackles

ASKER

I have exactly same values in the fields where it work :(
Avatar of Ackles

ASKER

when I removed the values to check, I never removed co, it was there & this account was made?
Oh hold on, is this throwing an error when calling New-ADUser because the account already exists? I thought you were deleting the account and testing again, perhaps I misunderstood?
Avatar of Ackles

ASKER

no, you understood correct, I am deleting the account & then running again
Damn that's a shame :)

If that trace is from one of the accounts that's creating correctly, what about one that isn't?
Avatar of Ackles

ASKER

this was what was not creating
Avatar of Ackles

ASKER

this is what is being created...

Name: GivenName; Value: Test string isNull? False
Name: sn; Value: Now1  string isNull? False
Name: DisplayName; Value: Test Now1  string isNull? False
Name: mail; Value: test.now1@test.com  string isNull? False
Name: l; Value: Zürich string isNull? False
Name: st; Value: Zürich string isNull? False
Name: c; Value: CH  string isNull? False
Name: co; Value: 756        string isNull? False
Name: Department; Value: Team A string isNull? False
Name: StreetAddress; Value:  5 string isNull? False
Name: userPrincipalName; Value: ten@test string isNull? False
Name: postalcode; Value: 8001 string isNull? False
Name: mobile; Value: +41  89 string isNull? False
Name: telephoneNumber; Value: +41 89 string isNull? False
Name: department; Value: Team A string isNull? False
Name: Title; Value: Manager string isNull? False
Name: physicalDeliveryOfficeName; Value: 2344 string isNull? False
Create branch executed for ten
What if: Performing operation "New" on Target "CN=Test Now1\ ,OU=TestUser,OU=test,DC=test,DC=com".
Okay, you'll have to start pulling attributes out of the set to identify those which fail. I'd pull userPrincipalName and co initially (just to test).
Avatar of Ackles

ASKER

Removing both UPN & co still doesn't create the account...
Avatar of Ackles

ASKER

there has to be a better way....
Set-ADUser and try each one in turn. I don't have a domain I can play with, I can't do it for you I'm afraid.

If it works without passing attributes, the attribute set is to blame. If none are obviously wrong it's something slightly more arcane. Obvious choices are values on constrained attributes (like userPrincipalName), but it could be something less obvious still.
Do bear in mind that this process of elimination should result in a permanent fix within the script. It's a one-time effort.
Avatar of Ackles

ASKER

Chris,
As i mentioned earlier,
4 Attributes:
Office as physicalDeliveryOfficeName,
 MobilePhone as mobile,
 OfficePhone as telephoneNumber,
Title as Title,

Problem is that I need these to be filled, when I remove them then script works, even if I fill them it still fails
Avatar of Ackles

ASKER

well Title works
Avatar of Ackles

ASKER

Finally, it's only MobilePhone
There we go, that's better (subjectively).

Does the number contain any special characters?
Avatar of Ackles

ASKER

In the two values it doesn't work, its Empty
Out of the 2 it works, it's also Empty in 1 ...

So I don't understand why it fails & why it works....
Is it really empty though? There's a difference between ' ' and ''. In theory if it were empty the trap we added to avoid pushing empty attributes should have prevented it going ahead.
Avatar of Ackles

ASKER

it's definitely empty
Let's try this then. I've tweaked the if statement, it might be a better comparison.
$value = $dr.GetValue($i)
if ([String]::IsNullOrEmpty("$value") -eq $false) {
    Write-Host "Name: $attribute; Value: $value $($value.GetType()) isNull? $($null -eq $value)"
    $otherAttributes.Add($attribute, $value)
}

Open in new window

Avatar of Ackles

ASKER

& now you My Friend ROCK!!!
Very Interesting fact is that out of the 4, in 2 it made Country & 2 not?
Let's figure this out.....
Avatar of Ackles

ASKER

This 1 works...

Name: GivenName; Value: Test string isNull? False
Name: sn; Value: First string isNull? False
Name: DisplayName; Value: Test First string isNull? False
Name: mail; Value: test.first@test.com string isNull? False
Name: l; Value: Zürich string isNull? False
Name: st; Value: Zürich string isNull? False
Name: c; Value: CH string isNull? False
Name: co; Value: 756        string isNull? False
Name: Department; Value: Team Z string isNull? False
Name: StreetAddress; Value:  5 string isNull? False
Name: userPrincipalName; Value: tfi@test.com string isNull? False
Name: postalcode; Value: 8001 string isNull? False
Name: Title; Value: Set string isNull? False
Name: department; Value: Team Z string isNull? False
Name: physicalDeliveryOfficeName; Value: xxx string isNull? False
Name: telephoneNumber; Value: +41  45 string isNull? False
Create branch executed for tfi

This Doesn't
Name: GivenName; Value: Test string isNull? False
Name: sn; Value: Now string isNull? False
Name: DisplayName; Value: Test Now string isNull? False
Name: mail; Value: test.now@test.com string isNull? False
Name: l; Value: Zürich string isNull? False
Name: st; Value: Zürich string isNull? False
Name: c; Value: CH  string isNull? False
Name: co; Value: 756        string isNull? False
Name: Department; Value: Team B string isNull? False
Name: StreetAddress; Value:  5 string isNull? False
Name: userPrincipalName; Value: tno@test.com string isNull? False
Name: postalcode; Value: 8001  string isNull? False
Name: Title; Value: Test string isNull? False
Name: department; Value: Team B string isNull? False
Name: physicalDeliveryOfficeName; Value: xxxx string isNull? False
Name: telephoneNumber; Value: +41  45  string isNull? False
Name: mobile; Value:   string isNull? False
Create branch executed for tno
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Ackles

ASKER

& YOU ARE THE MAN!!!
AWESOME!!!
Phew :)
Avatar of Ackles

ASKER

Thanks Chris, Again ;)
Avatar of Ackles

ASKER

Chris,
I have further questions to this project, would you be kind to help?
I will post the url of new question here if you agree?

Regards,
A
Sure.