Load Possible Property Pattern values from SQL

Question

Is there an option to load the possibles values of the Property Pattern, from SQL or other external sources?

Answer 1

Hello,

Yes, this is possible. For example, if your SQL database engine has some sort of an API that can be accessed from PowerShell, you can create a PowerShell script that will read possible values from an SQL database. A good example of a database engine that can be accessed from PowerShell is MS SQL.

Then, the script can update Property Patterns based on the values retrieved from the SQL database. Take a look at the following sample in our SDK that demonstrates how to update a list of possible values for the Department property in the built-in User Pattern using Adaxes ADSI API: http://www.adaxes.com/sdk/?SampleScript ... terns.html.

Finally, you can even use the script in Business Rules, Custom Commands or Scheduled Tasks to update the Property Pattern automatically. For example, you can create a Business Rule executed before creating a new user that will automatically update the User Pattern.

Also, take a look at the following script sample. It connects to an MS SQL database specified by $databaseName located on $databaseHost using the credentials of Adaxes default administrator (the user that was specified during Adaxes installation). Then, it loads a list of possible values for the Office property from the field called ADOfficeName in the database table called Offices. After that, it updates the list of possible values of the Office property in the built-in User Pattern. The sample script can be used in Business Rules, Custom Commands or Scheduled Tasks.

$databaseHost = "host.company.com" #TODO: modify me
$databaseName = "MyDatabase" # TODO: modify me
$isOfficePropertyRequired = $True

# Use the credentials of the default Adaxes administrator
# to connect to the database.
$databaseUsername = $NULL
$databasePassword = $NULL

# Get the office name
$connectionString = "Data Source=$databaseHost; Initial Catalog=$databaseName;"
if ($databaseUsername -eq $NULL)
{
    $connectionString = $connectionString +
        "Integrated Security=SSPI;"
}
else
{
    $connectionString = $connectionString +
        "User ID=$databaseUsername;Password=$databasePassword;"
}

$connection = New-Object "System.Data.SqlClient.SqlConnection"  $connectionString
$connection.Open()

$command = $connection.CreateCommand()
$command.CommandText = "SELECT ADOfficeName FROM Offices"

$reader = $command.ExecuteReader()

$officesName = @()
while ($reader.Read())
{
    $officesName += $reader["ADOfficeName"]
}

$reader.Close()
$command.Dispose()
$connection.Close()

if($officesName.Length -eq 0)
{
    return
}

# Sort office names in the array
[System.Array]::Sort($officesName)

# Modify 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)

foreach ($item in $userPattern.Items)
{
    if($item.PropertyName -eq "physicalDeliveryOfficeName")
    {
        $userPattern.Items.Remove($item)
        break
    }
}

$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
$constraints.Add($constraint)
$item.SetConstraints($constraints)

$item.SetInfo()
$userPattern.Items.Add($item)

If you need any assistance with the script, we can help you or even write it for you.

Comments

Thank you for the script. I modified the script slightly because I need to set the default values for a custom property. The default values are loaded, but when I try to Edit or Delete the constraint I get the "Catastrophic Failure". Am I missing something?

$isRequired = $False

# Sort Management Group in the array
[System.Array]::Sort($managementGroup)

# Modify the User Pattern
$propertyPatternsPath = $Context.GetWellKnownContainerPath("PropertyPatterns")
$propertyPatternsPathObj = New-Object "Softerra.Adaxes.Adsi.AdsPath" $propertyPatternsPath
$managementGroupPath = $propertyPatternsPathObj.CreateChildPath("CN=Management Group")

$mgmtGroup = $Context.BindToObject($managementGroupPath)

foreach ($item in $mgmtGroup.Items)
{
    if($item.PropertyName -eq "Management Group")
    {
        $mgmtGroup.Items.Remove($item)
        break
    }
}

$item = $mgmtGroup.Items.Create()
$item.PropertyName = "Management Group"
$item.IsPropertyRequired = $isRequired

$constraints = $item.GetConstraints()
$constraint = $constraints.Create("ADM_PROPERTYCONSTRAINTTYPE_VALUERANGE")
$constraint.Values = $managementGroup
$constraints.Add($constraint)
$item.SetConstraints($constraints)

$item.SetInfo()
$mgmtGroup.Items.Add($item)

---

Hello,

This happens because in the following line:

$item.PropertyName = "Management Group"

you specified the property by its Display Name in Adaxes (Management Group). Here, the property should be specified by its LDAP name.

Because of this, the script updated the Property Pattern with an incorrect property constraint that now causes a failure.

To get a LDAP name for a property:

1. Open Adaxes Administration Console.
2. Expand the service node that represents your service.
3. In the Console Tree, locate an object of the same type as you want to know the property name for and right-click it (for example, if you want to know the name of a certain property of Contacts, select and right-click any Contact).
4. In the context menu, select Add/Modify Property.
   
5. In the dialog box that appears, you will see a list of all properties as they are displayed in Adaxes along with their respective LDAP names.
6. If you can't find a property in the list, check the Show all properties option.
   

---

Works fine, thank you. This is the best support ever!

---

Hello,

Thank you for your good words, we really appreciate it! :)