Script Repository


Import user information from SQL database

February 22, 2016
2112

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 Domain-DNS object type that runs the script.

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 user account that will be used to connect to the database. Specify $NULL to use the credentials of Adaxes default service administrator.
  • $databasePassword - specifies the password that will be used to connect to the database. Specify $NULL to use the credentials of Adaxes default service administrator.
  • $ouDN - specifies the Distinguished Name (DN) of the Organizational Unit where user accounts are located;
  • $propertyMap - specifies a map of LDAP names of the properties of user accounts and the corresponding database fields;
  • $commandText - specifies an SQL statement that will be sued to pull the necessary data from the database.
Note: The script uses cmdlets from Adaxes PowerShell module for Active Directory. To run the script, you need to install the PowerShell Module for Active Directory component of Adaxes.
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 Adaxes default service
# administrator will be used to connect to the database

$ouDN = "OU=Users,DC=domain,DC=com" # TODO: modify me
$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
    $reader.Close()
    $command.Dispose()
    $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 ($user -eq $NULL)
    {
        # 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 $ouDN -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