We use cookies to improve your experience.
By your continued use of this site you accept such use.
For more details please see our privacy policy and cookies policy.

Script Repository

Import possible values for properties from SQL Database

February 25, 2021 Views: 3209

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
Leave a comment
Loading...

Got questions?

Support Questions & Answers