none
Set-ADObject $guid -Replace issue RRS feed

  • Question

  • Hello all!

    Kindly please for help - I have strange issue with PS script.

    Here is a small summary what it does: it creates CSV file from shared drive, update SQL database and when it is done it updates Active Directory user objects with attributes valuses from CSV such as position, lastname, firstname, department, manager etc.

    Due to unknown reason, some AD objects cannot be updated due to error message:

    Set-ADObject : replace
    At D:\TETA_IMPORT\TEST\sqlinject-test.ps1:183 char:1
    + Set-ADObject $guid -Replace @{
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (c5dce716-4965-4360-8e89-f82517e8f512:ADObject) [Set-ADObject], ADInvalidOperationException
        + FullyQualifiedErrorId : ActiveDirectoryServer:0,Microsoft.ActiveDirectory.Management.Commands.SetADObject

    The full code is below:

    Import-module ActiveDirectory
    
    #Start-Transcript -path D:\HR\log-file.txt
    
    Copy-Item \\domain.com\shared_drive\file\data_file.CSV D:\shared_drive\data_file.CSV -Force
    
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server=server01.domain.com;Database=YARIS;Integrated Security=False;uid=Awaw00dbs0001Yaris;pwd=p@sswd2!"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = "truncate table GSE_DANE"
    $SqlCmd.Connection = $SqlConnection
    
    $SqlConnection.Open()
    $SqlCmd.ExecuteNonQuery() 
    $SqlConnection.Close()
    
    #################################################### 
    # 
    # PowerShell CSV to SQL Import Script 
    # 
    #################################################### 
     
    # Database variables 
    $sqlserver = "database_serv.domain.com" 
    $database = "Yaris" 
    $table = "GSE_DANE" 
      
    # CSV variables 
    $csvfile = "D:\shared_drive\data_file.CSV" 
    $csvdelimiter = "`t" 
    $FirstRowColumnNames = $true 
      
    ################### No need to modify anything below ################### 
    Write-Host "Script started..." 
    $elapsed = [System.Diagnostics.Stopwatch]::StartNew()  
    [void][Reflection.Assembly]::LoadWithPartialName("System.Data") 
    [void][Reflection.Assembly]::LoadWithPartialName("System.Data.SqlClient") 
      
    # 50k worked fastest and kept memory usage to a minimum 
    $batchsize = 50000 
      
    # Build the sqlbulkcopy connection, and set the timeout to infinite 
    $connectionstring = "Data Source=$sqlserver;Initial Catalog=$database;uid=Awaw00dbs0001Yaris;pwd=p@sswd2!" 
    $bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($connectionstring, [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock) 
    $bulkcopy.DestinationTableName = $table 
    $bulkcopy.bulkcopyTimeout = 0 
    $bulkcopy.batchsize = $batchsize 
      
    # Create the datatable, and autogenerate the columns. 
    $datatable = New-Object System.Data.DataTable 
      
    # Open the text file from disk 
    $reader = New-Object System.IO.StreamReader($csvfile) 
    $columns = (Get-Content $csvfile -First 1).Split($csvdelimiter) 
    if ($FirstRowColumnNames -eq $true) { $null = $reader.readLine() } 
      
    foreach ($column in $columns) {  
        $null = $datatable.Columns.Add() 
    } 
      
    # Read in the data, line by line, not column by column 
    while (($line = $reader.ReadLine()) -ne $null)  { 
        
        $null = $datatable.Rows.Add($line.Split($csvdelimiter)) 
     
    # Import and empty the datatable before it starts taking up too much RAM, but  
    # after it has enough rows to make the import efficient. 
        $i++; if (($i % $batchsize) -eq 0) {  
            $bulkcopy.WriteToServer($datatable)  
            Write-Host "$i rows have been inserted in $($elapsed.Elapsed.ToString())." 
            $datatable.Clear()  
        }  
    }  
      
    # Add in all the remaining rows since the last clear 
    if($datatable.Rows.Count -gt 0) { 
        $bulkcopy.WriteToServer($datatable) 
        $datatable.Clear() 
    } 
      
    # Clean Up 
    $reader.Close(); $reader.Dispose() 
    $bulkcopy.Close(); $bulkcopy.Dispose() 
    $datatable.Dispose() 
      
    Write-Host "Script complete. $i rows have been inserted into the database." 
    Write-Host "Total Elapsed Time: $($elapsed.Elapsed.ToString())" 
    # Sometimes the Garbage Collector takes too long to clear the huge datatable. 
    [System.GC]::Collect()
    
    Echo "***********************Starting Import******************************************************************************"
    $A = Get-Date
    $A.ToUniversalTime()
    echo $A
    
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server=database_serv.domain.com;Database=YARIS;Integrated Security=False;uid=Awaw00dbs0001Yaris;pwd=p@sswd2!"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    
    $SqlCmd.CommandText = "SELECT * from GSE_DANE"
    
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close()
    $DataSet.Tables[0] | foreach {
    
    $suplogin = 0
    $arr = @($_.LOGIN.split('\/'))
    
    if ($arr[0]) {
    if ($arr[0] -notlike "domain") {}
    else {
    $guid = (Get-ADUser $arr[1]).objectGuid
    
    #echo $_.EP15
    #$arr2 = @($_.EP3.split('\/'))
    
            $UserNameToFind=$_.SUPERIOR_ID
            $connectionstring2="Server=database_serv.mednet.world;Database=YARIS;Integrated Security=False;uid=Awaw00dbs0001Yaris;pwd=p@sswd2!"
            $SQLCommandText2="SELECT LOGIN FROM GSE_DANE WHERE USER_ID='$($UserNameToFind)'"
            #$SQLCommandText2="SELECT LOGIN FROM GSE_DANE WHERE login like 'mednet\wojciecp'"
            $SQLConnection2 = new-object system.data.sqlclient.SqlConnection($connectionstring)
            $SQLConnection2.open()
            $sqlCommand2 = New-Object system.Data.sqlclient.SqlCommand($SQLCommandText2,$SQLConnection2)
            $suplogin = $sqlCommand2.ExecuteScalar()
            $SQLConnection2.close()
            if ($suplogin) {
            $arr2 = @($suplogin.split('\/'))
            if ($arr2[0]) {
    if ($arr2[0] -notlike "domain") {$dnsup=$null}
    else {
    $dnsup =(Get-AdUser $arr2[1] | select DistinguishedName)
    }
    echo $_.LOGIN
    echo $suplogin
    echo $guid
    echo $dnsup.DistinguishedName
    
    if ($dnsup.DistinguishedName) {
    
    Set-ADObject $guid -Replace @{
    
    'physicalDeliveryOfficeName'=$_.COMPANY;
    #'telephoneNumber'=$telephoneNumber;
    'department'=$_.DEPARTMENT;
    'description'=$_.STANOWISKO;
    'title'=$_.JOB_POSITION;
    'streetAddress'=$_.OFFICE_ADDRESS;
    'c'="EN";
    'co'=$_.COUNTRY;
    'company'=$_.DIVISION;
    'manager'=$dnsup.DistinguishedName;
    'ipPhone'=$_.USER_ID;
    'pager'=$_.CC;
    
    
    } -Partition "DC=mednet,DC=world"
    
    }
    
    
    #echo $_.IP1,$_.IP3,$dnsup
    
    
    }
    
    
    }
    
    else {
    
    echo $_.LOGIN
    #echo $suplogin
    #echo $guid
    
    
    echo $dnsup.DistinguishedName
    
    
    Set-ADObject $guid -Replace @{
    
    'physicalDeliveryOfficeName'=$_.COMPANY;
    #'telephoneNumber'=$telephoneNumber;
    'department'=$_.DEPARTMENT;
    'description'=$_.STANOWISKO;
    'title'=$_.JOB_POSITION;
    'streetAddress'=$_.OFFICE_ADDRESS;
    'c'="EN";
    'co'=$_.COUNTRY;
    'company'=$_.DIVISION;
    #'manager'=$dnsup.DistinguishedName;
    'ipPhone'=$_.USER_ID;
    'pager'=$_.CC;
    
    } -Partition "DC=domain,DC=com"
    
    }
    
    }
    }
    }
    
    
    

    Wednesday, August 21, 2019 9:01 PM

All replies

  • In those cases, have you verified that "$guid" contains a valid GUID? I didn't see any error handling for cases where Get-ADUser might have failed.

    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Wednesday, August 21, 2019 10:20 PM
  • Hi Rich,

    I have checked the GUID for this user and seems to be ok:

    Get-aduser -identity "user" | Select-object ObjectGUID


    I get this:

    ObjectGUID        : c5dce716-4965-4360-8e89-f82517e8f512

    When I try to manually update this user using:

    Set-ADObject "c5dce716-4965-4360-8e89-f82517e8f512" -Replace -WhatIf -Debug @{
    
    'physicalDeliveryOfficeName'=$_.COMPANY;
    #'telephoneNumber'=$telephoneNumber;
    'department'=$_.DEPARTMENT;
    'description'=$_.STANOWISKO;
    'title'=$_.JOB_POSITION;
    'streetAddress'=$_.OFFICE_ADDRESS;
    'c'="PL";
    'co'=$_.COUNTRY;
    'company'=$_.DIVISION;
    #'manager'=$dnsup.DistinguishedName;
    'ipPhone'=$_.USER_ID;
    'pager'=$_.CC;
    
    } -Partition "DC=domain,DC=com"

    I get this output:

    DOMAIN\User
    Set-ADObject : replace
    At D:\TETA_IMPORT\TEST\sqlinject-test.ps1:183 char:1
    + Set-ADObject "c5dce716-4965-4360-8e89-f82517e8f512" -Replace @{
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (c5dce716-4965-4360-8e89-f82517e8f512:ADObject) [Set-ADObject], ADInvalidOper 
       ationException
        + FullyQualifiedErrorId : ActiveDirectoryServer:0,Microsoft.ActiveDirectory.Management.Commands.SetADObject

    Thursday, August 22, 2019 6:52 AM
  • Set-AdObject cannot take a GUID.  Why are you using a GUID?  Why are you using Set-AdObject.  Use "Set-AdUser".  Also use "Replace" instead of "Add".


    \_(ツ)_/

    Thursday, August 22, 2019 7:19 AM
    Moderator
  • Hi,

    Actually I tried to use Set-ADUser as follows:

    Set-ADUser -identity "user" -Replace @{
    
    'physicalDeliveryOfficeName'=$_.COMPANY;
    #'telephoneNumber'=$telephoneNumber;
    'department'=$_.DEPARTMENT;
    'description'=$_.STANOWISKO;
    'title'=$_.JOB_POSITION;
    'streetAddress'=$_.OFFICE_ADDRESS;
    'c'="PL";
    'co'=$_.COUNTRY;
    'company'=$_.DIVISION;
    #'manager'=$dnsup.DistinguishedName;
    'ipPhone'=$_.USER_ID;
    'pager'=$_.CC;
    
    } -Partition "DC=domain,DC=com"
    

    When I run the script I get this:

    ***********************Starting Import******************************************************************************

    Thursday, August 22, 2019 7:42:37 AM
    Thursday, August 22, 2019 9:42:37 AM
    1
    DOMAIN\User
    Set-ADUser : replace
    At D:\TETA_IMPORT\TEST\sqlinject-test.ps1:183 char:1
    + Set-ADUser -identity "user" -Replace @{
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (user:ADUser) [Set-ADUser], ADInvalidOperationException
        + FullyQualifiedErrorId : ActiveDirectoryServer:0,Microsoft.ActiveDirectory.Management.Commands.SetADUser


    Thursday, August 22, 2019 7:45 AM
  • Start with:

    help Set-AdUser -online

    Read carefully and add parameters a few at a time until you find your error.

    You don't need or want "Partition".

    The following would be more correct but no field can be null.

    Set-ADUser -identity user -Replace @{   
        physicalDeliveryOfficeName = $_.COMPANY
        department                 = $_.DEPARTMENT
        description                = $_.STANOWISKO
        title                      = $_.JOB_POSITION
        streetAddress              = $_.OFFICE_ADDRESS
        country                    = $_.COUNTRY
        company                    = $_.DIVISION
        ipPhone                    = $_.USER_ID
        pager                      = $_.CC
    }


    \_(ツ)_/

    Thursday, August 22, 2019 8:53 AM
    Moderator
  • Hi,

    I have checked all parameters - all fields have values.

    I have replaced the code as you suggested... still the same... :((

    Total Elapsed Time: 00:00:00.0265136
    ***********************Starting import******************************************************************************
    
    Thursday, August 22, 2019 11:40:25 AM
    Thursday, August 22, 2019 1:40:25 PM
    1
    DOMAIN\USER
    Set-ADuser : replace
    At D:\TETA_IMPORT\TEST\sqlinject-test.ps1:183 char:1
    + Set-ADuser -Identity "user" -Replace @{
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (zawieruw:ADUser) [Set-ADUser], ADInvalidOperationException
        + FullyQualifiedErrorId : ActiveDirectoryServer:0,Microsoft.ActiveDirectory.Management.Commands.SetADUser

    Thursday, August 22, 2019 11:42 AM
  • You still have a bad value in your code.  emove all and add back one at a time.


    \_(ツ)_/

    Thursday, August 22, 2019 2:38 PM
    Moderator
  • What happens if you remove the "ipphone" (and maybe "pager") from the equation? IIRC, if that attribute doesn't exist on the user you can't use "-Replace", but you can use "-Add".

    I don't recall if that's true for other attributes, or not. You may have to add some additional code to check for the presence of other attributes and add an "-Add" splat to the Set-ADUser.


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Thursday, August 22, 2019 3:07 PM
  • Both IPPhone and Pager work with "Replace" even if the attributes do not exisit on that user.  To be sure I just tested it.


    \_(ツ)_/

    Thursday, August 22, 2019 3:27 PM
    Moderator
  • Then I agree with your "remove them and add them back one at a time."

    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Thursday, August 22, 2019 3:39 PM
  • Hi All,

    Sorry I didn't reply to you for so long.

    You were all right - there were missing values for parameters 'department' and 'position'. The CSV has "`t" delimeter and encoding errors.

    Now it works good.

    Many thanks for your help!

    Best regards,

    AD

    Friday, August 23, 2019 1:19 PM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee



    Just do it.

    Friday, September 6, 2019 12:02 PM
    Moderator