Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Powershell Script issue

Posted on 2014-10-28
9
Medium Priority
?
264 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 71

Expert Comment

by:Chris Dent
ID: 40410142
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
ID: 40410726
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 71

Accepted Solution

by:
Chris Dent earned 2000 total points
ID: 40410871
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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

Author Comment

by:jcroy727
ID: 40410973
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
 
LVL 71

Expert Comment

by:Chris Dent
ID: 40411013
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
ID: 40411150
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 71

Expert Comment

by:Chris Dent
ID: 40411205
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
ID: 40411651
Not using a CSV. Pulling the data directly from the database.
0
 
LVL 71

Expert Comment

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

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A bad practice commonly found during an account life cycle is to set its password to an initial, insecure password. The Password Reset Tool was developed to make the password reset process easier and more secure.
A walk-through example of how to obtain and apply new DID phone numbers to your cloud PBX enabled users that are configured in Office 365. Whether you have 1, 10 or 100+ users in your tenant, it's quite easy to get them phone-enabled and making/rece…
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 to another domain controller. Log onto the new domain controller with a user account t…
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 …

564 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