Script Repository


Import user information from SQL database

February 18, 2021
4963

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 ( 10 )
avatar
Tim
May 14, 2021
How would the select statement ($commandText variable) look if I wanted to exclude a specific record based on value. For example, if I did not want to process any records where the Department was equal to "Facilities" with my import?
avatar
Support
May 17, 2021
Hello Tim,

In SQL, search conditions are specified in the WHERE clause. To exclude users whose Department property value equals Facilities from a selection, you should use a query like below in the $commandText variable:

SELECT ID,Name,Username,FirstName,LastName,DisplayName,Description,Password,Department,Company,City,Office,AccountExpiresDate FROM UsersTable WHERE Department <> 'Facilities'
avatar
Fachmi
Nov 18, 2021
1. How can I check if the account is exist or not with multiple value such as Employee ID, email, name?

If none of it found, then only create new user.

1. Can I get the data from Oracle table?
avatar
Support
Nov 18, 2021
Hello Fachmi,

>How can I check if the account is exist or not with multiple value such as Employee ID, email, name?

It will require significantly reworking the script as currently it only checks the Employee ID property.

>Can I get the data from Oracle table?
Yes, it should work just fine. The following article should be helpful:https://docs.microsoft.com/en-us/answers/questions/350779/query-oracle-database-using-powershell.html.
avatar
ngadimin
Nov 20, 2021
Hi, when i run this script on powershell ISE and Adaxes powershell module for AD, just showing this error message :
===================
You cannot call a method on a null-valued expression.
At C:\Users\ngadimin\importsqluser.ps1:107 char:1
+ $domainName = $Context.GetObjectDomain("%distinguishedName%")
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
=============

Is there part that not correct?
Thanks.
avatar
Support
Nov 22, 2021
Hello,

The script is correct. However, it is using the predefined $Context variable. The script can only be executed in Adaxes custom commands, business rules and scheduled tasks. It will not work in Windows PowerShell. This behavior is by design.
avatar
user
Jan 12, 2022
why when I run the code above in Scheduled Tasks, the code above is not triggered, to run it must first press the run script button, is there a solution?
avatar
Support
Jan 12, 2022
Hello,

Scheduled tasks only run when the time you specify for them. Make sure that the schedule you specify meets your requirements. To change the schedule of an existing task, select it in Adaxes Administration console and click Change schedule on the right.
avatar
user
Jan 13, 2022
I've done that but it still doesn't trigger automatically, a message like this appears in the log

Modify 'add (Adaxes\Adaxes Configuration\Configuration Objects\Scheduled Tasks)': set 'ScheduledTaskRunNow' to 'Yes'
Modify 'add (Adaxes\Adaxes Configuration\Configuration Objects\Scheduled Tasks)': set 'ScheduledTaskRunNow' to 'Yes'
Delete '74455e6a-bf41-4065-92e1-7dc58b034818 (Adaxes\Adaxes Configuration\Configuration Objects\Scheduled Tasks\add)'
Modify 'add (Adaxes\Adaxes Configuration\Configuration Objects\Scheduled Tasks)': add 'ceb2068a-48dd-446b-8d98-05a43ab8d78d (Adaxes\Adaxes Configuration\Configuration Objects\Scheduled Tasks\add)' to 'ConditionedActions'

is there any solution ?
avatar
Support
Jan 13, 2022
Hello, 

The provided messages mean that the scheduled task was modified and executed manually. For information on how to schedule tasks, have a look at the following tutorial: https://www.adaxes.com/tutorials_AutomatingDailyTasks_ScheduleTasksForActiveDirectoryManagement.htm. Should you still have issues running the task on a schedule, please, send us at support@adaxes.com a screenshot of the task configuration. Please, make sure to include the task schedule part in the screenshot.
Leave a comment