Skip to main content

Articles in this section

See more
Print

Import SQL Data | Update AD Contacts rule

  • Updated

Overview

The Import SQL Data | Update AD Contacts rule in Cayosoft Administrator is designed to synchronize and update Active Directory (AD) contact objects with data retrieved from a specified SQL data source. This rule ensures that contact information in Active Directory remains accurate and up-to-date based on external data.

  1. The rule queries a SQL data source and compares its data to existing AD contact objects.

  2. It identifies matches between the systems using anchor attributes, which serve as unique identifiers.

  3. Based on the rule's settings, it updates the matched contact objects in AD according to the actions specified in the Action section.

 

Use cases

This rule is especially useful for organizations that maintain contact information in an external SQL database and need to reflect changes or updates in Active Directory without manual intervention. The Empty field in data source setting ensures precise control over how empty fields are handled during synchronization.

 

Rule settings

Query section

Setting name Description

Limit scope to this domain OU

This setting defines the search query scope.To improve query performance, limit the scope to specific OU.To test rule configuration, limit the rule scope to an OU that contains test accounts or objects.

SQL instance

Specify the name of the SQL Instance as defined in the Utils extension SQL Server configuration. The Default SQL instance setting retrieves the current setting from the SQL Connection settings of the Utils extension.

NOTE: For more information, see the Connecting to Microsoft SQL Server data source article.

SQL database name

Specify the database name from the selected SQL instance.

SQL table

Specify a SQL table or view from the selected database. Click the [...] button to display a list of tables.

SQL credentials

Select a database from the SQL instance data source. Click the [...] button to enter the credentials.

NOTE: Cayosoft Administrator does not support Windows Authentication when connecting to a Microsoft SQL Server database. The database connection must use SQL Server Authentication, where the credentials (username and password) are created and managed within SQL Server itself. The mixed mode refers to the SQL Server configuration that allows both SQL Server Authentication and Windows Authentication, but only SQL Server Authentication is supported by Cayosoft Administrator.

Data source filter

You can use the point-and-click filter builder for the specified data source.

NOTE: If the More options > Where Clause setting is specified, the Data source filter setting is ignored.

More options

Return these SQL columns

Specify columns to be returned by the data source.

Where clause

Define a WHERE statement in the SQL query sent to the data source to limit the rows returned by SQL Server.

Filter

Set the filtering conditions to only return objects or data that must be processed by the rule.

Example: filter by the Name column.

Properties to display

Select properties to display for each object found by the query.

Empty field in data source

This setting determines how the rule handles fields in the SQL data source that are blank or empty.

  • Ignore: Leave the corresponding AD property unchanged if the data source field is empty.
  • Clear property value: Removes the value of the corresponding AD property if the data source field is empty.

Data source anchor attribute

Defines the column in the data source used to determine if the user account already exists. This value is compared to the Active Directory anchor attribute.

As user names are likely to have duplicates, some other attribute with a unique value must be used to determine if records read from the data source have already been processed.

Active Directory anchor attribute
Defines the AD attribute to compare the Data
source anchor attribute value to when searching for a matching object.

 

Action

Update method

Select the update method for the attribute value:

  • Always update: Forces the attribute to update regardless of the current value.

  • Only update empty values: Updates the attribute only if it is currently empty.
  • Only update on value difference: Updates the attribute only if the new value differs from the existing one.

Other properties

Map data source columns to the target user properties using the picker dialog.

Other properties script

Data mapping also can be set by the script.

If you would like every provisioned user to have the extension attribute 1 populated with some string value, use the following script: {@{'extensionAttribute1'='YourString'}}.

If you would like every provisioned user to have the extension attribute 2 populated with the corresponding value from the column in your data source file, use the following script: {@{'extensionAttribute2'=$_.EXT2}}. EXT2 is the name of the column from your data source.

NOTE: If you set mapping for the same properties both in the Other properties and Other properties script, attribute values will be updated by the script.

 

Output section

This section defines the output format of this rule.

To get more information about this section, please see the Rule Output section article.

 

Enforce/Schedule section

This section defines the schedule for how often to run the rule.

To get more information about this section, please see the Rule Enforce/Schedule section article.

 

Change history

Version Notes
12.1 The rule has been introduced.

© Copyright 2013-2025 Cayosoft Inc. All rights reserved.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request
Return to top

Related articles

Comments

0 comments

Article is closed for comments.