Exporting data from SQL to create new users in AD

PowerShell Help
1

Good morning folks! As the title says I am exporting data straight from SQL. This is my testlab I use to run scripts before going into production. Now I get back data from SQL so that doesn’t seem to be my problem I don’t think. The output of my data from SQL looks like this.

Firstname     : FIRAS
Middlename    : TAREQ
Lastname      : ABUMUNSHAR
StudentUserid : N199642
School        : 0074
Grade         : 11
PIN           : 50448
NameChange    : False

Firstname     : MADIYAN
Middlename    : TARIQ
Lastname      : ABUMUNSHAR
StudentUserid : N377798
School        : 0074
Grade         : 09
PIN           : 72599
NameChange    : False

Here is my script:

#$FileName = (Get-Date).tostring("MM-dd-yyyy-hh-mmt")  
#$FileName = "c:\powershell\StudentImport$($FileName).log"
#$T = Get-Date -Uformat "%m-%d-%Y %r"
#"Started Powershell Student Import $($T)" >> $FileName
#[Environment]::NewLine >> $FileName

$Userscreatedcounter = 0
$Usersmovedcounter = 0
$Instance = "SQL"
$Database = "ESDStudentImports"
$Table = "Student_AD_Users"
$Cred = Get-Credential

$Students = Read-SqlTableData -ServerInstance $instance -DatabaseName $database -SchemaName dbo -TableName $table -Credential $cred -ColumnName Firstname,Middlename,Lastname,StudentUserid,School,Grade,PIN,NameChange

Foreach ($Student in $Students) {

    $FirstName              = $Student.FirstName
    $LastName               = $Student.LastName
    $MiddleName             = $Student.MiddleName
    #$Name                   = "$LastName $FirstName $MiddleName"
    $CostCenter             = "$Student.School"
    $Pwd                    = "$Student.Pin"
    $Grade                  = "$Student.Grade"
    $Sam                    = "$Student.StudentUserID"
    $Dom                    = "student.testlab.local"
    
    $CostCenter = $student.School
    Switch ( $CostCenter ) {
        '0085' {
            $OU     = "OU=AllGrades,OU=Students,OU=BHS,OU=High School,OU=North,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0085 All Students"       
        }
        '0171' {
            $OU     = "OU=AllGrades,OU=Students,OU=Phillippi,OU=Elementary School,OU=Central,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0171 All Students"
        }
        '0031' {
            $OU     = "OU=AllGrades,OU=Students,OU=SMS,OU=Middle School,OU=Central,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0031 All Students"
        }
        '0021' {
            $OU     = "OU=AllGrades,OU=Students,OU=Pineview,OU=Other,OU=Central,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0021 All Students"
        }
        '0084' {
            $OU     = "OU=AllGrades,OU=Students,OU=BMS,OU=Middle School,OU=North,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0021 All Students"
        }
        '0131' {
            $OU     = "OU=AllGrades,OU=Students,OU=Fruitville,OU=Elementary School,OU=North,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0131 All Students"
        }
        '1291' {
            $OU     = "OU=AllGrades,OU=Students,OU=Woodland,OU=Middle School,OU=South,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "1291 All Students"
        }
        '0101' {
            $OU     = "OU=AllGrades,OU=Students,OU=Brentwood,OU=Elementary School,OU=North,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0101 All Students"
        }
        '1282' {
            $OU     = "OU=AllGrades,OU=Students,OU=Tatum,OU=Elementary School,OU=North,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "1282 All Students"
        }
        '1211' {
            $OU     = "OU=AllGrades,OU=Students,OU=LNS,OU=Other,OU=Central,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "1211 All Students"
        }
        '1231' {
            $OU     = "OU=AllGrades,OU=Students,OU=Toledo,OU=Elementary School,OU=South,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "1231 All Students"
        }
        '0294' {
            $OU     = "OU=AllGrades,OU=Students,OU=Triad-Second Chance,OU=Other,OU=Central,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0294 All Students"
        }
        '0141' {
            $OU     = "OU=AllGrades,OU=Students,OU=Mcintosh,OU=Middle School,OU=North,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0141 All Students"
        }
        '1261' {
            $OU     = "OU=AllGrades,OU=Students,OU=Heron,OU=Middle School,OU=South,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "1261 All Students"
        }
        '0221' {
            $OU     = "OU=AllGrades,OU=Students,OU=VHS,OU=High School,OU=South,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0221 All Students"
        }
        '0271' {
            $OU     = "OU=AllGrades,OU=Students,OU=GulfGate,OU=Elementary School,OU=Central,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0271 All Students"
        }
        '0291' {
            $OU     = "OU=AllGrades,OU=Students,OU=Wilkinson,OU=Elementary School,OU=Central,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0291 All Students"
        }
        '0012' {
            $OU     = "OU=AllGrades,OU=Students,OU=AltaVista,OU=Elementary School,OU=North,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0012 All Students"
        }
        '0071' {
            $OU     = "OU=AllGrades,OU=Students,OU=BayHaven,OU=Elementary School,OU=North,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0071 All Students"
        }
        '0501' {
            $OU     = "OU=AllGrades,OU=Students,OU=Emma,OU=Elementary School,OU=North,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0501 All Students"
        }
        '0261' {
            $OU     = "OU=AllGrades,OU=Students,OU=Gocio,OU=Elementary School,OU=North,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0261 All Students"
        }
        '1241' {
            $OU     = "OU=AllGrades,OU=Students,OU=Atwater,OU=Elementary School,OU=South,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "1241 All Students"
        }
        '0121' {
            $OU     = "OU=AllGrades,OU=Students,OU=Englewood,OU=Elementary School,OU=South,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0121 All Students"
        }
        '0381' {
            $OU     = "OU=AllGrades,OU=Students,OU=Garden,OU=Elementary School,OU=South,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0381 All Students"
        }
        '0461' {
            $OU     = "OU=AllGrades,OU=Students,OU=Glenallen,OU=Elementary School,OU=South,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0461 All Students"
        }
        '1341' {
            $OU     = "OU=AllGrades,OU=Students,OU=Lamarque,OU=Elementary School,OU=South,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "1341 All Students"
        }
        '0201' {
            $OU     = "OU=AllGrades,OU=Students,OU=Tuttle,OU=Elementary School,OU=North,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0201 All Students"
        }
        '0301' {
            $OU     = "OU=AllGrades,OU=Students,OU=Ashton,OU=Elementary School,OU=Central,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0301 All Students"
        }
        '1251' {
            $OU     = "OU=AllGrades,OU=Students,OU=NPHS,OU=High School,OU=South,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "1251 All Students"
        }
        '0191' {
            $OU     = "OU=Allgrades,OU=Students,OU=Southside,OU=Elementary School,OU=North,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0191 All Students"
        }
        '0181' {
            $OU     = "OU=AllGrades,OU=Students,OU=Riverview HS,OU=High School,OU=Central,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0181 All Students"
        }
        '0111' {
            $OU     = "OU=AllGrades,OU=Students,OU=Brookside,OU=Middle School,OU=North,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0111 All Students"
        }
        '0293' {
            $OU     = "OU=AllGrades,OU=Students,OU=OakPark,OU=Other,OU=Central,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0293 All Students"
        }
        '0471' {
            $OU     = "OU=AllGrades,OU=Students,OU=Lakeview,OU=Elementary School,OU=Central,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0471 All Students"
        }
        '0211' {
            $OU     = "OU=AllGrades,OU=Students,OU=VES,OU=Elementary School,OU=South,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0211 All Students"
        }
        '0451' {
            $OU     = "OU=AllGrades,OU=Students,OU=VMS,OU=Middle School,OU=South,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0451 All Students"
        }
        '0491' {
            $OU     = "OU=AllGrades,OU=Students,OU=Taylor,OU=Elementary School,OU=South,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "0491 All Students"
        }
        '1271' {
            $OU     = "OU=AllGrades,OU=Students,OU=Cranberry,OU=Elementary School,OU=South,OU=Zones,DC=student,DC=testlab,DC=local"
            $Group  = "1271 All Students"
        }
        '0292' {
            $OU     = "OU=AllGrades,OU=Students,OU=PSS-ESE,OU=Other,OU=Central,OU=Zones,DC=student,DC=testlab,DC=local"
        }
#EndRegion costcenter        
    }

    $NewUserParams = @{
        'UserPrincipalName' = "$Sam@$Dom"
        'SamAccountName' = "$Sam"
        'Name' = "$LastName $FirstName"
        'GivenName' = "$FirstName"
        'SurName' = "$LastName"
        'DisplayName' = "$LastName $FirstName"
        'Path' = "$OU"
        'AccountPassword' = (ConvertTo-SecureString $Pwd -AsPlainText -Force)
        'PasswordNeverExpires' = $true
        'Server' = "dc2.student.testlab.local"
    }

    New-ADUser @NewUserParams 

}

And here is what I am not understanding the error, which I have been reseaching but haven’t been ablel to find an explanation of why I am getting it:

New-ADUser : The name provided is not a properly formed account name
At C:\student\StudentImport.ps1:201 char:5
+     New-ADUser @NewUserParams
+     ~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (CN=AARON NATTIN...estlab,DC=local:String) [New-ADUser], ADException
    + FullyQualifiedErrorId : ActiveDirectoryServer:1315,Microsoft.ActiveDirectory.Management.Commands.NewADUser

Is it the way I have the $Displayname with spaces or??? I am lost on this. Any help would be appreciated.

Rich

2

I think I have figured out my problem with properly formed account name. I guess when I declare my variables just after my foreach I can’t have those spaces. My OCD gets the best of me when I want to properly align stuff LOL :). Now my problem I am having is my switch statement. I declare the $costcenter from the output and I know it works because I have done a foreach loop to write-host “$student.Costcenter” and it produces the correct results so depending on what those results are should work in my switch statement I would think but it doesn’t seem to be working. If the costcenter is 1211 for example it will look at the switch statement for that and I have 2 variable with with each to one, put it in the correct OU and 2 assign the correct group. The error I get when running the script now is it is seeing -Path from New-ADUser as empty or null:

New-ADUser : Cannot validate argument on parameter 'Path'. The argument is null or empty. Provide an argument that is not null or
empty, and then try the command again.
At C:\student\StudentImport.ps1:200 char:16
+     New-ADUser @NewUserParams
+                ~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [New-ADUser], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.ActiveDirectory.Management.Commands.NewADUser

 

3

Well I am lost…I am now once again getting the first error and I have literally changed nothing. What am I missing?

New-ADUser : The name provided is not a properly formed account name
At C:\student\StudentImport.ps1:161 char:5
+     New-ADUser @NewUserParams
+     ~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (CN=AARONSON SZU...estlab,DC=local:String) [New-ADUser], ADException
    + FullyQualifiedErrorId : ActiveDirectoryServer:1315,Microsoft.ActiveDirectory.Management.Commands.NewADUser

 

4

I suspect you have characters in your names that are not permitted. If you check the data for the students that it’s tripping up on are there any characters in the name?

5

I don’t see any characters in the name at all or spaces. I have exported it as a csv and checked directly in powershell but don’t see any characters.

6

well i figured it out. I don’t understand why I had to do this but this is what I had to do after my foreach loop when I declare my variables I had to make these changes:

$Textinfo = (Get-Culture).TextInfo
    $firstname = $student.firstname.ToLower()
    $firstname = $Textinfo.ToTitleCase($firstname)
    $middlename = $student.middlename.ToLower()
    $middlename = $Textinfo.ToTitleCase($middlename)
    $lastname = $student.lastname.ToLower()
    $lastname = $Textinfo.ToTitleCase($lastname)
    $Sam = $student.StudentUserid
    $Grade = $student.Grade
    $CostCenter = $student.School