Script Repository


Import possible values for properties from SQL Database

February 25, 2021
2347

The script updates allowed property values in a property pattern based on the data from MS SQL database. To run the script, create a scheduled task configured for the Domain-DNS object type and add a managed domain to the Activity Scope of the task.

Parameters:

  • $databaseHost - Specifies the fully qualified domain name or IP address of the MS SQL database host.
  • $databaseName - Specifies the database name.
  • $patternDN - Specifies the distinguished name (DN) of the property pattern to update. For information on how to get the DN of a directory object, see Get the DN of a directory object.
  • $fieldInfo - Maps the name of the field in the MS SQL database with the corresponding table name, property LDAP name and property mandatory state (required/not required).
  • $databaseUsername - Specifies the username to be used to connect to the database. If set to $NULL, the credentials of the Adaxes service account will be used.
  • $databasePassword - Specifies the database password. If set to $NULL, the credentials of the Adaxes service account will be used.
Edit Remove
PowerShell
$databaseHost = "host.company.com" #TODO: modify me
$databaseName = "MyDatabase" # TODO: modify me
$patternDN = "CN=My Pattern,CN=Property Patterns,CN=Configuration Objects,CN=Adaxes Configuration,CN=Adaxes" #TODO: modify me
$fieldInfo = @{
    "OfficeName" = @{TableName = "Offices"; PropertyName = "physicalDeliveryOfficeName"; Required = $True}
    "JobTitle" = @{TableName = "JobTitles"; PropertyName = "title"; Required = $False}
} # TODO: modify me

# Specify credentials used to connect to the database.
# When set to $NULL, the credentials of the Adaxes service account will be used
$databaseUsername = $NULL
$databasePassword = $NULL

# Get data from MS SQL 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()
    
    $propertiesInfo = @{}
    foreach ($fieldName in $fieldInfo.Keys)
    {
        $tableName = $fieldInfo[$fieldName].TableName
        $propertyName = $fieldInfo[$fieldName].PropertyName
        $propertiesInfo.Add($propertyName, @{
                Values = (New-Object System.Collections.ArrayList)
                Required = $fieldInfo[$fieldName].Required
            }
        )
        
        try
        {
            $command = $connection.CreateCommand()
            $command.CommandText = "SELECT $fieldName FROM $tableName"
            $reader = $command.ExecuteReader()
            
            while ($reader.Read())
            {
                $value = $reader[$fieldName]
                [void]$propertiesInfo[$propertyName].Values.Add($value)
            }
        }
        finally
        {
            if ($reader) { $reader.Close() }
            if ($command) { $command.Dispose() }
        }
    }
}
finally
{
    if ($connection) { $connection.Close() }
}

# Update the Property Pattern
$pattern = $Context.BindToObjectByDN($patternDN)
foreach ($propertyName in $propertiesInfo.Keys)
{
    if ($propertiesInfo[$propertyName].Values.Count -eq 0)
    {
        continue
    }
    
    foreach ($item in $pattern.Items)
    {
        if ($item.PropertyName -eq $propertyName)
        {
            $pattern.Items.Remove($item)
            break
        }
    }
    
    $item = $pattern.Items.Create()
    $item.PropertyName = $propertyName
    $item.IsPropertyRequired = $propertiesInfo[$propertyName].Required
    
    $constraints = $item.GetConstraints()
    $constraint = $constraints.Create("ADM_PROPERTYCONSTRAINTTYPE_VALUERANGE")
    $constraint.AreValuesDenied = $False
    $values = $propertiesInfo[$propertyName].Values.ToArray()
    [System.Array]::Sort($values)
    $constraint.Values = $values
    $constraints.Add($constraint)
    
    $item.SetConstraints($constraints)
    $item.SetInfo()
    $pattern.Items.Add($item)
}

Comments ( 0 )
No results found.
Leave a comment