Script Repository


Import user information from SQL database

August 26, 2020
3080

The script updates user accounts in Active Directory based on information in an SQL database. If a user does not have an account in Active Directory, their accounts are created based on the information available in the database. Users are identified in Active Directory by their Employee ID.

To schedule import of user information, create a Scheduled Task configured for the Organizational Unit object type and add the required OU to the Activity Scope of the task.

Parameters:

  • $databaseHost - Specifies the DNS host name of the computer where the SQL database is homed.
  • $databaseName - Specifies the database name.
  • $databaseUsername - Specifies the username of the account that will be used to connect to the database. If set to $NULL the credentials of the Adaxes service account will be used.
  • $databasePassword - Specifies the password that will be used to connect to the database. This variable is ignored if the $databaseUsername one is set to $NULL.
  • $propertyMap - Maps property LDAP names of user accounts with the corresponding database fields.
  • $commandText - Specifies an SQL statement that will be issued to pull the necessary data from the database.
Note: To run the script, install Adaxes PowerShell module.
Edit Remove
PowerShell
Import-Module Adaxes

$databaseHost = "host.company.com" # TODO: modify me
$databaseName = "MyDatabase" # TODO: modify me
$databaseUsername = $NULL # TODO: modify me
$databasePassword = $NULL # TODO: modify me
# If set to $NULL, the credentials of the Adaxes service
# account will be used to connect to the database

$propertyMap = @{
    "employeeID" = "ID";
    "name" = "Name";
    "samaccountname" = "Username";
    "givenName" = "FirstName";
    "sn" = "LastName";
    "displayName" = "DisplayName";
    "description" = "Description";
    "unicodePwd" = "Password";
    "department" = "Department";
    "company" = "Company";
    "l" = "City";
    "postOfficeBox" = "Office";
    "AccountExpires" = "AccountExpiresDate";
} # TODO: modify me: $propertyMap = @{"LdapPropertyName" = "SQL database field"}

$commandText = "SELECT ID,Name,Username,FirstName,LastName,DisplayName,Description,Password,Department,Company,City,Office,AccountExpiresDate FROM UsersTable" 
# TODO: modify me

# Connect to the database
$connectionString = "Data Source=$databaseHost; Initial Catalog=$databaseName;"
if ($databaseUsername -eq $NULL)
{
    $connectionString = $connectionString +
        "Integrated Security=SSPI;"
}
else
{
    $connectionString = $connectionString +
        "User ID=$databaseUsername;Password=$databasePassword;"
}

try
{
    $connection = New-Object "System.Data.SqlClient.SqlConnection"  $connectionString
    $connection.Open()
    
    # Query user information from database
    $command = $connection.CreateCommand()
    $command.CommandText = $commandText

    # Load user information
    $reader = $command.ExecuteReader()
    $usersFromDB = @{}

    while ($reader.Read())
    {
        $valuesFromDB = @{}
        foreach ($ldapPropertyName in $propertyMap.Keys)
        {
            $columnName = $propertyMap[$ldapPropertyName]
            $value = $reader[$columnName]

            # If the value is empty, skip it
            if ([System.String]::IsNullOrEmpty($value) -or ([System.Convert]::IsDBNull($value)))
            {
                continue
            }
            elseif ($value -is [System.String])
            {
                $value = $value.Trim()
            }
            elseif ($ldapPropertyName -ieq "accountExpires")
            {
                try
                {
                    $value = $value.ToFileTime() # Convert Date to Large Integer
                }
                catch
                {
                    continue
                }
            }
            if ($value -ieq "True" -or $value -ieq "False")
            {
                $value = [System.Boolean]::Parse($property.Value)
            }

            $valuesFromDB.Add($ldapPropertyName, $value)
        }
        $usersFromDB.Add($valuesFromDB.employeeID, $valuesFromDB)
    }
}
finally
{
    # Close connection to the SQL database and release resources
    if ($reader) { $reader.Close() }
    if ($command) { $command.Dispose() }
    if ($connection) { $connection.Close() }
}

# Get domain name
$domainName = $Context.GetObjectDomain("%distinguishedName%")

foreach ($employeeId in $usersFromDB.Keys)
{
    $userPropertiesFromDB = $usersFromDB[$employeeId]

    # Get User Password, and remove it from the hashtable
    $userPassword = ConvertTo-SecureString -AsPlainText -String $userPropertiesFromDB["unicodePwd"] -Force
    $userPropertiesFromDB.Remove("unicodePwd")

    # Get other properties
    $propertiesToCheck = @($userPropertiesFromDB.Keys)
    
    # Search user by Employee Number
    $user = Get-AdmUser -Filter {employeeId -eq $employeeId} -AdaxesService localhost -Server $domainName `
        -Properties $propertiesToCheck -ErrorAction SilentlyContinue
        
    if ($NULL -eq $user)
    {
        # The user account does not exist, create one
        # Get user identity, and remove it from the hashtable
        $name = $userPropertiesFromDB["name"]
        $userPropertiesFromDB.Remove("name")
        
        # Create user
        try
        {
            $user = New-AdmUser -Name $name -OtherAttributes $userPropertiesFromDB -AdaxesService localhost `
                -Server $domainName -Path "%distinguishedName%" -Enabled $True -ErrorAction Stop -PassThru
        }
        catch
        {
            $Context.LogMessage($_.Exception.Message, "Error")
            continue
        }
        
        if ($userPassword -ne $NULL)
        {
            # Set password
            Set-AdmAccountPassword -Identity $user.DistinguishedName -NewPassword $userPassword `
                -Reset -Server $domainName -AdaxesService localhost
        }
        
        continue
    }

    # If the user exists, check whether any properties have changed
    foreach ($propetyName in $propertiesToCheck)
    {
        # Remove properties with the same values from the hashtable
        if ($user."$propetyName" -ieq $userPropertiesFromDB[$propetyName])
        {
            $userPropertiesFromDB.Remove($propetyName)
        }
    }

    if ($userPropertiesFromDB.Count -eq 0)
    {
        continue # Nothing changed
    }

    # Update user
    Set-AdmUser $user.DistinguishedName -Replace $userPropertiesFromDB -AdaxesService localhost `
        -Server $domainName
}

Comments ( 0 )
No results found.
Leave a comment