Solved

Powershell Script issue

Posted on 2014-10-28
9
173 Views
Last Modified: 2015-03-04
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
0
Comment
Question by:jcroy727
  • 5
  • 4
9 Comments
 
LVL 70

Expert Comment

by:Chris Dent
Comment Utility
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
 

Author Comment

by:jcroy727
Comment Utility
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
 
LVL 70

Accepted Solution

by:
Chris Dent earned 500 total points
Comment Utility
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
 

Author Comment

by:jcroy727
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 70

Expert Comment

by:Chris Dent
Comment Utility
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
 

Author Comment

by:jcroy727
Comment Utility
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
 
LVL 70

Expert Comment

by:Chris Dent
Comment Utility
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
 

Author Comment

by:jcroy727
Comment Utility
Not using a CSV. Pulling the data directly from the database.
0
 
LVL 70

Expert Comment

by:Chris Dent
Comment Utility
Of course, sorry. Any of the fields likely to be DbNull?
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

How to sign a powershell script so you can prevent tampering, and only allow users to run authorised Powershell scripts
In this article, we will see the basic design consideration while designing a Multi-tenant web application in a simple manner. Though, many frameworks are available in the market to develop a multi - tenant application, but do they provide data, cod…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now