Script Repository

Import possible values for property from SQL Database

January 23, 2020

The below PowerShell script updates built-in User Pattern with a list of offices specified in an SQL human resources database.

To update the office list on a regular basis, you need to schedule execution of the script. For this purpose, create a Scheduled Task configured for the Domain-DNS object type.

See Also: Automatically Set Users' Address Based on Their Office.


  • $databaseHost - specifies the fully qualified domain name or IP address of the database host;
  • $databaseName - specifies the database name;
  • $tableName - specifies the name of the database table that contains information on available offices;
  • $fieldName - specifies the name of the database field to pull offices from;
  • $isOfficePropertyRequired - specifies whether to set the Office attribute as required, $True or $False;
  • $databaseUsername - specifies the username to be used to connect to the database;
  • $databasePassword - specifies the database password.
Edit Remove
$databaseHost = "" #TODO: modify me
$databaseName = "MyDatabase" # TODO: modify me
$tableName = "Offices"
$fieldName = "OfficeName"
$isOfficePropertyRequired = $True

# 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 office names
$connectionString = "Data Source=$databaseHost; Initial Catalog=$databaseName;"
if ($databaseUsername -eq $NULL)
    $connectionString = $connectionString +
        "Integrated Security=SSPI;"
    $connectionString = $connectionString +
        "User ID=$databaseUsername;Password=$databasePassword;"

$connection = New-Object "System.Data.SqlClient.SqlConnection"  $connectionString
$command = $connection.CreateCommand()
$command.CommandText = "SELECT $fieldName FROM $tableName"
$reader = $command.ExecuteReader()
$officesName = @()

while ($reader.Read())
    $officesName += $reader[$fieldName]

if($officesName.Length -eq 0)
    return # Nothing to add


# Modify the User Pattern

# Bind to the User Pattern
$propertyPatternsPath = $Context.GetWellKnownContainerPath("PropertyPatterns")
$propertyPatternsPathObj = New-Object "Softerra.Adaxes.Adsi.AdsPath" $propertyPatternsPath
$builtinPathObj = $propertyPatternsPathObj.CreateChildPath("CN=Builtin")
$userPatternPath = $builtinPathObj.CreateChildPath("CN=User Pattern")
$userPattern = $Context.BindToObject($userPatternPath)

# Check whether an item exists for the Office property
# If it exists, remove it
foreach ($item in $userPattern.Items)
    if($item.PropertyName -eq "physicalDeliveryOfficeName")

# Create a new item with ne values for the Office property
$item = $userPattern.Items.Create()
$item.PropertyName = "physicalDeliveryOfficeName"
$item.IsPropertyRequired = $isOfficePropertyRequired
$constraints = $item.GetConstraints()
$constraint = $constraints.Create("ADM_PROPERTYCONSTRAINTTYPE_VALUERANGE")
$constraint.AreValuesDenied = $False
$constraint.Values = $officesName

Comments ( 0 )
No results found.
Leave a comment