Powershell Script issue

Trying to pull data from a SQL Database and add to AD as a contacts. The Display name comes over but nothing else. Any ideas on what's wrong with this script?

Import-Module ActiveDirectory
Import-Module SQLPS
Invoke-Sqlcmd -ServerInstance dataServer -Database Database1 -u "sa" -password "password" -Query `
 "select Top 10 FirstName, LastName, Title, Email, Phone from dbo.CustomerContacts" |
select @{l='Name';e={$_.firstname+" "+$_.lastname}},
 @{l='SamAccountName';e={$_.firstname.tolower().substring(0,1)+$_.lastname.tolower()}},
 @{l='UserPrincipalName';e={$_.firstname.tolower().substring(0,1)+$_.lastname.tolower()}},
 @{l='DisplayName';e={$_.firstname+" "+$_.lastname}},
 @{l='GivenName';e={$_.firstname}},
 @{l='Surname';e={$_.lastname}},
 @{l='telephoneNumber';e={$_.phone}},
 title, email |
New-ADObject -Type Contact -Path "OU=DealerContacts,OU=users - gpo,DC=korgusa,DC=com" -PassThru |
select Name, SamAccountName, UserPrincipalName, DisplayName, GivenName, Surname, telephoneNumber, title, email | ft -auto
jcroy727Network EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris DentPowerShell DeveloperCommented:
Because only DisplayName is acceptable down through the pipeline. You'll have to pass the rest as a HashTable using the OtherAttributes parameter.
Import-Module ActiveDirectory
Import-Module SQLPS

Invoke-Sqlcmd -ServerInstance dataServer -Database Database1 -u "sa" -password "password" -Query `
   "select Top 10 FirstName, LastName, Title, Email, Phone from dbo.CustomerContacts" |
  ForEach-Object {
    $OtherAttributes = @{
      Name              = "$($_.firstname) $($_.lastname)"
      SamAccountName    = "$($_.firstname.ToLower()[0])$($_.lastname.ToLower())"
      UserPrincipalName = "$($_.firstname.ToLower()[0])$($_.lastname.ToLower())@korgusa.com"
      GivenName         = $_.firstname
      sn                = $_.lastname
      telephoneNumber   = $_.phone
      title             = $_.title
      mail              = $_.email
    }
  
    New-ADObject -Type Contact -DisplayName $_.DisplayName -OtherAttributes $OtherAttributes -Path "OU=DealerContacts,OU=users - gpo,DC=korgusa,DC=com" -PassThru
  } |
  Select-Object Name, SamAccountName, UserPrincipalName, DisplayName, GivenName, sn, telephoneNumber, title, mail |
  ft -auto

Open in new window

I hvaen't tested it, can't here I'm afraid. It may need further modification but I'm sure it'll shout at you if it's not quite right.

Chris
0
jcroy727Network EngineerAuthor Commented:
Thanks for the reply. On the initial try, it is asking me to enter a name when I run the script. So there must be a syntax error in there somewhere. I will play around with it. Thanks again for the guidance. If you de see anything that stands out, please let me know. I pasted the output below so you can see it for yourself.

PS SQLSERVER:\> C:\Users\Administrator.KORGUSANT\Documents\NEW-CreateAD-User-Test.ps1
WARNING: The names of some imported commands from the module 'SQLPS' include unapproved verbs that might make them less discoverable. To find th
e commands with unapproved verbs, run the Import-Module command again with the Verbose parameter. For a list of approved verbs, type Get-Verb.
cmdlet New-ADObject at command pipeline position 1
Supply values for the following parameters:
Name:
0
Chris DentPowerShell DeveloperCommented:
Ahh we just need to move Name from the other attributes set back into the main CmdLet call:
Import-Module ActiveDirectory
Import-Module SQLPS

Invoke-Sqlcmd -ServerInstance dataServer -Database Database1 -u "sa" -password "password" -Query `
   "select Top 10 FirstName, LastName, Title, Email, Phone from dbo.CustomerContacts" |
  ForEach-Object {
    $OtherAttributes = @{
      SamAccountName    = "$($_.firstname.ToLower()[0])$($_.lastname.ToLower())"
      UserPrincipalName = "$($_.firstname.ToLower()[0])$($_.lastname.ToLower())@korgusa.com"
      GivenName         = $_.firstname
      sn                = $_.lastname
      telephoneNumber   = $_.phone
      title             = $_.title
      mail              = $_.email
    }
  
    New-ADObject -Name "$($_.firstname) $($_.lastname)" -Type Contact -DisplayName $_.DisplayName -OtherAttributes $OtherAttributes -Path "OU=DealerContacts,OU=users - gpo,DC=korgusa,DC=com" -PassThru
  } |
  Select-Object Name, SamAccountName, UserPrincipalName, DisplayName, GivenName, sn, telephoneNumber, title, mail |
  ft -auto

Open in new window

Chris
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
How the Cloud Can Help You as an MSSP

Today, every Managed Security Service Provider (MSSP) needs a platform to deliver effective and efficient security-as-a-service to their customers. Scale, elasticity and profitability are a few of the many features that a Cloud platform offers. Register today to learn more!

jcroy727Network EngineerAuthor Commented:
Now getting this message:

New-ADObject : Illegal modify operation. Some aspect of the modification is not permitted
At C:\Users\Administrator.KORGUSANT\Documents\NEW-CreateAD-User-Test.ps1:17 char:5
+     New-ADObject -Name "$($_.firstname) $($_.lastname)" -Type Contact -DisplayNa ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (cn=Alan Alan,OU...=korgusa,DC=com:String) [New-ADObject], ADIllegalModifyOperationException
    + FullyQualifiedErrorId : ActiveDirectoryServer:8311,Microsoft.ActiveDirectory.Management.Commands.NewADObject
0
Chris DentPowerShell DeveloperCommented:
Hmm drop the items out of the OtherAttributes list one at a time until it goes away?

We might be exceeding a maximum length, or writing an attribute which doesn't exist... which brings to mind SamAccountName and UserPrincipalName, are you sure you mean to create contacts?
0
jcroy727Network EngineerAuthor Commented:
Yes, Contacts. And you are correct. Did not those two. It still generates an error but the Contacts are created in AD with all the requested properties. The error is below if you are interested but I can't thank you enough for all your help!

New-ADObject : The server is unwilling to process the request
At C:\Users\Administrator.KORGUSANT\Documents\NEW-CreateAD-User-Test.ps1:16 char:5
+     New-ADObject -Name "$($_.firstname) $($_.lastname)" -Type Contact -DisplayNa ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (cn=Paul McDermo...=korgusa,DC=com:String) [New-ADObject], ADException
    + FullyQualifiedErrorId : ActiveDirectoryServer:0,Microsoft.ActiveDirectory.Management.Commands.NewADObject
0
Chris DentPowerShell DeveloperCommented:
Getting there :)

Things that can cause that error include:

Name conflicts (within the OU), empty or unexpected values, etc, etc.

Perhaps let's start with the latter, is anything in the CSV empty?

Chris
0
jcroy727Network EngineerAuthor Commented:
Not using a CSV. Pulling the data directly from the database.
0
Chris DentPowerShell DeveloperCommented:
Of course, sorry. Any of the fields likely to be DbNull?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Active Directory

From novice to tech pro — start learning today.