How to update active directory

This topic contains 5 replies, has 5 voices, and was last updated by Profile photo of Mark Prior Mark Prior 4 months, 3 weeks ago.

  • Author
    Posts
  • #46690
    Profile photo of Lexi Paddington
    Lexi Paddington
    Participant

    Hello All,

    I am all new to this forum, can someone please help me with this..
    I have below code for transferring activedirectory data into sql, can someone plz help me code to update activedirectory from sql server.

    #GetActiveDirectoryUsers
    Import-Module ActiveDirectory
    ###################################################################################################
    # Powershell to grab all active directory users incrementally
    #inserting or updating them into a SQL table.
    ###################################################################################################
    function Get-String ($obj){
    if ([string]::IsNullOrEmpty($obj))
    {
    return ""
    }
    else
    {
    return $obj.ToString();
    }
    }

    ###################################################################################################
    #SQL Connection Setup
    ###################################################################################################
    $SQLServer = "localhost";
    $SQLDBName = "DBA_Utilities";
    $SqlQuery = @"
    UPDATE [DBA_Utilities].[dbo].[GetActiveDirectoryUsers]
    SET
    [OperationalUnit] = @OperationalUnit,
    [FirstName] = @FirstName,
    [LastName] = @LastName,
    [DisplayName] = @DisplayName,
    [email] = @email,
    [EmailAddress] = @EmailAddress,
    [StreetAddress] = @StreetAddress,
    [City] = @City,
    [State] = @State,
    [PostalCode] = @PostalCode,
    [HomePhone] = @HomePhone,
    [MobilePhone] = @MobilePhone,
    [OfficePhone] = @OfficePhone,
    [Fax] = @Fax,
    [Company] = @Company,
    [Organization] = @Organization,
    [Department] = @Department,
    [Title] = @Title,
    [Description] = @Description,
    [Office] = @Office,
    [extensionAttribute1] = @extensionAttribute1,
    [extensionAttribute2] = @extensionAttribute2,
    [extensionAttribute3] = @extensionAttribute3,
    [extensionAttribute4] = @extensionAttribute4,
    [extensionAttribute5] = @extensionAttribute5,
    [AccountExpires] = @AccountExpires,
    [AccountIsEnabled] = @AccountIsEnabled,
    [PasswordLastSet] = @PasswordLastSet,
    [PasswordExpires] = @PasswordExpires,
    [PasswordNeverExpires]= @PasswordNeverExpires,
    [PasswordIsExpired] = @PasswordIsExpired,
    [LastLogonTimestamp] = @LastLogonTimestamp,
    [CreatedDate] = @CreatedDate,
    [DWUpdatedDate] = getdate()
    WHERE[sAMAccountName] = @sAMAccountName
    AND [CanonicalName] = @CanonicalName

    IF @@ROWCOUNT = 0
    BEGIN
    INSERT INTO [DBA_Utilities].[dbo].[GetActiveDirectoryUsers]
    (CanonicalName,[sAMAccountName],[OperationalUnit],[FirstName],[LastName],[DisplayName],[email],[EmailAddress],[StreetAddress],[City],[State],[PostalCode],[HomePhone],[MobilePhone],[OfficePhone],[Office],[Fax],[Company],[Organization],[Department],[Title],[Description],[extensionAttribute1],[extensionAttribute2],[extensionAttribute3],[extensionAttribute4],[extensionAttribute5],[AccountExpires],[AccountIsEnabled],[PasswordLastSet],[PasswordExpires],[PasswordNeverExpires],[PasswordIsExpired],[LastLogonTimestamp])
    SELECT @CanonicalName,@sAMAccountName ,@OperationalUnit ,@FirstName ,@LastName ,@DisplayName ,@email ,@EmailAddress ,@StreetAddress ,@City ,@State ,@PostalCode ,@HomePhone ,@MobilePhone ,@OfficePhone ,@Office ,@Fax ,@Company ,@Organization ,@Department ,@Title ,@Description ,@extensionAttribute1 ,@extensionAttribute2 ,@extensionAttribute3 ,@extensionAttribute4 ,@extensionAttribute5 ,@AccountExpires ,@AccountIsEnabled ,@PasswordLastSet ,@PasswordExpires ,@PasswordNeverExpires ,@PasswordIsExpired ,@LastLogonTimestamp
    END

    "@
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
    $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True";
    $SqlConnection.Open();
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
    $SqlCmd.Connection = $SqlConnection;
    $SqlCmd.CommandType = [System.Data.CommandType]::Text ;

    ##$SqlCmd.CommandText = "SELECT COUNT(*) FROM [DBA_Utilities].[dbo].[ActiveDirectoryUsers]";
    ##$RecordCount = $SqlCmd.ExecuteScalar();

    $SqlCmd.CommandText = $SqlQuery;

    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@CanonicalName", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@sAMAccountName", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@OperationalUnit", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@FirstName", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@LastName", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@DisplayName", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@email", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@EmailAddress", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@StreetAddress", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@City", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@State", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@PostalCode", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@HomePhone", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@MobilePhone", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@OfficePhone", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Fax", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Company", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Organization", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Department", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Title", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Description", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Office", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@extensionAttribute1", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@extensionAttribute2", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@extensionAttribute3", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@extensionAttribute4", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@extensionAttribute5", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@AccountExpires", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@AccountIsEnabled", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@PasswordLastSet", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@PasswordExpires", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@PasswordNeverExpires",[Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@PasswordIsExpired", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@LastLogonTimestamp", [Data.SQLDBType]::VarChar, 128))) | Out-Null
    $SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@CreatedDate", [Data.SQLDBType]::VarChar, 128))) | Out-Null

    ###################################################################################################
    #Get the Results From AD
    ###################################################################################################
    #$Results = Get-ADUser -Filter * -Server "BigBang.com" -ResultSetSize 10 -Properties * | select -property sAMAccountName,ou,
    $Results = Get-ADUser -Filter * -Properties * | select -property CanonicalName,sAMAccountName,ou,
    GivenName,SurName,DisplayName,email,emailaddress,
    StreetAddress,City,State,PostalCode,
    HomePhone,MobilePhone,OfficePhone,Fax,
    Company,Organization,Department,Title,Description,Office,
    extensionAttribute1,extensionAttribute2,extensionAttribute3,extensionAttribute4,extensionAttribute5,
    @{Name='AccountExpires';Expression={[DATETIME]::fromFileTime($_.accountExpires)}},Enabled,PasswordLastSet,
    @{n="PasswordExpirationDate";e={$_.PasswordLastSet.AddDays($maxPasswordAge)}},PasswordNeverExpires,PasswordExpired,
    LastLogonDate,whenCreated

    foreach ($item in $Results) {
    $SqlCmd.Parameters[0].Value = Get-String($item.CanonicalName)
    $SqlCmd.Parameters[1].Value = Get-String($item.sAMAccountName)
    $SqlCmd.Parameters[2].Value = Get-String($item.ou)
    $SqlCmd.Parameters[3].Value = Get-String($item.GivenName)
    $SqlCmd.Parameters[4].Value = Get-String($item.SurName)
    $SqlCmd.Parameters[5].Value = Get-String($item.DisplayName)
    $SqlCmd.Parameters[6].Value = Get-String($item.email)
    $SqlCmd.Parameters[7].Value = Get-String($item.emailaddress)
    $SqlCmd.Parameters[8].Value = Get-String($item.StreetAddress)
    $SqlCmd.Parameters[9].Value = Get-String($item.City)
    $SqlCmd.Parameters[10].Value = Get-String($item.State)
    $SqlCmd.Parameters[11].Value = Get-String($item.PostalCode)
    $SqlCmd.Parameters[12].Value = Get-String($item.HomePhone)
    $SqlCmd.Parameters[13].Value = Get-String($item.MobilePhone)
    $SqlCmd.Parameters[14].Value = Get-String($item.OfficePhone)
    $SqlCmd.Parameters[15].Value = Get-String($item.Fax)
    $SqlCmd.Parameters[16].Value = Get-String($item.Company)
    $SqlCmd.Parameters[17].Value = Get-String($item.Organization)
    $SqlCmd.Parameters[18].Value = Get-String($item.Department)
    $SqlCmd.Parameters[19].Value = Get-String($item.Title)
    $SqlCmd.Parameters[20].Value = Get-String($item.Description)
    $SqlCmd.Parameters[21].Value = Get-String($item.Office)
    $SqlCmd.Parameters[22].Value = Get-String($item.extensionAttribute1)
    $SqlCmd.Parameters[23].Value = Get-String($item.extensionAttribute2)
    $SqlCmd.Parameters[24].Value = Get-String($item.extensionAttribute3)
    $SqlCmd.Parameters[25].Value = Get-String($item.extensionAttribute4)
    $SqlCmd.Parameters[26].Value = Get-String($item.extensionAttribute5)
    $SqlCmd.Parameters[27].Value = Get-String($item.AccountExpires)
    $SqlCmd.Parameters[28].Value = Get-String($item.Enabled)
    $SqlCmd.Parameters[29].Value = Get-String($item.PasswordLastSet)
    $SqlCmd.Parameters[30].Value = Get-String($item.PasswordExpirationDate)
    $SqlCmd.Parameters[31].Value = Get-String($item.PasswordNeverExpires)
    $SqlCmd.Parameters[32].Value = Get-String($item.PasswordExpired)
    $SqlCmd.Parameters[33].Value = Get-String($item.LastLogonDate)
    $SqlCmd.Parameters[34].Value = Get-String($item.whenCreated)
    $SqlCmd.ExecuteNonQuery();
    }
    if ($SqlConnection.State -eq "Open") {$SqlConnection.Close()}

    Many thanks in advance.

  • #46740
    Profile photo of Don Jones
    Don Jones
    Keymaster

    If you look right above the posting textbox, you'll see two options for formatting code – either use PRE tags, or post your code into a Gist and then paste the Gist URL here.

    However, what you're asking is potentially a lot of work. While everyone here is happy to try and answer questions, we're not able to provide free script-writing services. If you have a specific question about where to start or are running into a specific problem, people here are usually happy to try and help. I'd post that in a new thread, rather than continuing this one.

  • #46825
    Profile photo of Dan Potter
    Dan Potter
    Participant

    woah..

  • #46880
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    First, that is a really hard and ugly way to get AD information to a database. How many Active Directory records are you replicating to the database? From a performance standpoint, doing a BULK INSERT is the fastest method to get data into SQL that I've found.

    Can you provide some background on why you are putting the data in the database and how it's going to be used?

  • #46882
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    Can you provide some background on why you are trying to replicate Active Directory users to another database? There are some much easier methods to get data from Powershell to SQL. How many records would you be migrating? 100, 1000, 10000...

  • #46913
    Profile photo of Mark Prior
    Mark Prior
    Participant

You must be logged in to reply to this topic.