Solved

Powershell Script issue

Posted on 2014-10-28
9
185 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
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 70

Accepted Solution

by:
Chris Dent earned 500 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
 

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 70

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 70

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 70

Expert Comment

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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Disabling the Directory Sync Service Account in Office 365 will stop directory synchronization from working.
In this article, I am going to show you how to simulate a multi-site Lab environment on a single Hyper-V host. I use this method successfully in my own lab to simulate three fully routed global AD Sites on a Windows 10 Hyper-V host.
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 …

863 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

23 Experts available now in Live!

Get 1:1 Help Now