Ackles
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
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()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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))
To this:$value = $dr.GetValue($i)
if ($null -ne $value) {
$otherAttributes.Add($attribute, $value)
}
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.
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
}
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.
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.
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.
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?
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?
ASKER
Chris,
So you are saying that I only leave the Name attribute & delete all the rest?
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.
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.
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?
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.
ASKER
So 4 Attributes:
Office as physicalDeliveryOfficeName ,
MobilePhone as mobile,
OfficePhone as telephoneNumber,
Title as Title,
These are the faulty attributes, they were empty....
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)
}
The extra quotes around $value in the test might be sufficient to prevent it adding those fields to attributes.
ASKER
Is there a way,
if value is empty, ignore & don't throw error....
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.
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)"
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".
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,
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.
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.
ASKER
Sorry, I'm lost now...
it's Title.
it's Title.
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)"
That might help chase down constraints...
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".
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
Create branch executed for tfi
What if: Performing operation "New" on Target "CN=Test First,OU=TestUser,OU=test,
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 :)).
ASKER
I have exactly same values in the fields where it work :(
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?
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?
If that trace is from one of the accounts that's creating correctly, what about one that isn't?
ASKER
this was what was not creating
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=te st,DC=com" .
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
Create branch executed for ten
What if: Performing operation "New" on Target "CN=Test Now1\ ,OU=TestUser,OU=test,DC=te
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).
ASKER
Removing both UPN & co still doesn't create the account...
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.
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.
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
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
ASKER
well Title works
ASKER
Finally, it's only MobilePhone
There we go, that's better (subjectively).
Does the number contain any special characters?
Does the number contain any special characters?
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....
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.
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)
}
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.....
Very Interesting fact is that out of the 4, in 2 it made Country & 2 not?
Let's figure this out.....
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
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
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
Name: telephoneNumber; Value: +41 45 string isNull? False
Name: mobile; Value: string isNull? False
Create branch executed for tno
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
& YOU ARE THE MAN!!!
AWESOME!!!
AWESOME!!!
Phew :)
ASKER
Thanks Chris, Again ;)
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
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.
ASKER
Thanks!
This is the URL: https://www.experts-exchange.com/questions/29014111/How-to-use-Powershell-data-from-SQL.html
This is the URL: https://www.experts-exchange.com/questions/29014111/How-to-use-Powershell-data-from-SQL.html
ASKER
That seemed to work!
I have to check other issues, but have to run out.... sorry
Will update later.
Thanks,
A