Skip to main content

Articles in this section

See more
Print

Import SQL Data | Update AD Users

  • Updated

Import SQL Data | Update AD Users

Rule description

This rule queries the specified SQL data source and updates Active Directory user accounts according to the Action section settings. Accounts are matched between the two systems by anchor attributes.

NOTE: You can also update user virtual attributes. For more details, please see the Virtual Attributes article.

When to use this rule

Use this rule when you need to perform bulk user account update from HR/ERP/SIS system into Active Directory.

Rule settings

Query section

Setting name Description

Limit scope to this domain or OU

This setting defines the search query scope. To improve query performance, limit the scope to a specific OU.

IMPORTANT: To test the rule configuration, limit the rule scope to an OU that contains test accounts or objects and use the Preview feature.

SQL instance

 

Specify the name of the SQL Instance as defined in the Utils extension settings > SQL Server configuration. Using the Default SQL Instance setting will retrieve the current setting from the SQL Connection settings of the Utils extension.

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

SQL database name

Specify database name from selected SQL Instance.

SQL table

Specify SQL Table or View from the selected database. Click the [...] button to display a list of tables from which to choose.

SQL credentials

Specify the database from the data source SQL Instance. Click the [...] button to enter SQL Credentials.

NOTE: Cayosoft Administrator does not support Windows Authentication for 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. 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.

Data Source Filter

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

NOTE: If the Where clause is also specified, it will be applied and DataSource Filter will be ignored.
Maximum number of objects Specify the maximum number of objects to modify in the selected scope.
More options

Return these SQL columns

 

Specify columns 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.

Properties to display

Each object property defined in this setting matches the column that will be displayed in the Web Portal for this web query.

To display additional columns, add the required properties to the Properties to display list.

To add extension attribute 1 that is synchronized from AD, you need to use a value like:

Copy
"OnPremisesExtensionAttributes/extensionAttribute1~Extension Attribute 1"

Filter

 

Set the filtering conditions to hide unwanted data based on criteria not supported in the Query criteria setting.

Example: filter by the found object Distinguished Name.

TIP: For optimal performance, use the Query criteria setting above to filter objects whenever possible.

Empty field in Data Source

If the record in SQL column is empty, you can skip updating the attribute or clear its value.

Data Source Anchor attribute

Select a column in the data source that contains the attribute value for identifying and mapping a computer.

Active Directory Anchor attribute

Defines the attribute in the AD to which the Data Source anchor attribute is to be compared. When a user is updated this value also specifies the AD attribute into which the Data Source anchor is written for comparison the next time the rule is executed.

Action section

Setting name Description
Update method

Specify the method to update AD Users properties:

  • Always update.

  • Only update empty values.

  • Only update on value difference.
Simplified output When set to 'Yes', the output will show user attribute values as they were queried. When set to 'No', values will be required and verified after updating them to show true end values in the output. This will decrease rule performance for large datasets. 
Account

FirstName (GivenName)

If the Data Source contains a field named FirstName, ignore this setting. Otherwise, use the Selector button to choose a field from the Data Source.

Initials

Specify user initials.

Last/SurName (sn)

If the Data Source contains a field named LastName, ignore this setting. Otherwise, use the Selector button to choose a field from the Data Source.

Display Name

 

If the Data Source contains fields named FirstName and LastName, choose the desired format ignore this setting. Otherwise, use the Selector button to select a field from the Data Source or contact Cayosoft for an override format.

Description

 

If the Data Source contains a field name Description, ignore this setting. Otherwise, manually enter a static text value or use the Selector button to choose a field from the Data Source.

Settings

New Password

This setting defines the password for the new account. This value can be static text, a field from the Data Source chosen using the Selection button, or set to Generate Random Password.

NOTE: Static passwords or passwords from Data Source must meet the Active Directory Password Complexity Policy of the target container, or the account will be created in a disabled state. Randomly Generated Passwords will be generated to match both the Active Directory Password Complexity Policy and additional complexity requirements defined in the Cayosoft Administrator Password Complexity Policy.

  • Must change password at next logon

  • Account enabled

  • User cannot change password

  • Password never expires

These settings enable/disable the standard Active Directory user object settings.

Account Expiration Date

This setting defines the Account Expiration attribute in Active Directory.

In addition to populating this field from the Data Source, a text string can also be manually entered into the field in the format MM/DD/YYYY or YYYY-MM-DD.

Organization

  • Office

  • Job Title (Title)

  • Department

  • Company

  • Employee Number

  • EmployeeID

  • Division

If the Data Source contains one of these field names, ignore this setting. Otherwise, manually enter a static text value or use the Selector button to choose a field from the Data Source.

Manager Identifier

Use the Selector button to choose a field from the Data Source that is a unique identifier for the user’s manager. Typically this will be the Managers EmployeeNumber or EmployeeID.

AD Attribute for Manager Lookup

Select an Active Directory attribute that is used to search for the value of the Manager Identifier specified in the field above.

Contact Info

Country

If the Data Source contains a field name Country, ignore this setting. Otherwise, manually enter a static text value or use the Selector button to choose a field from the Data Source.

  • Office Phone (telephoneNumber)

  • Mobile Phone (mobile)

  • Street Address

  • City (l)

  • State

  • Postal Code

If the Data Source contains a field with one of these names, ignore this setting. Otherwise manually enter a static text value or use the Selector button to choose a field from the Data Source.

 

Other Properties

Other properties

Using picker dialog, set a mapping between data source columns and target user properties. 

Other properties script

Data mapping also can be set by the script.

If you want every provisioned user to have extension attribute 1 populated with some string value then use this

Copy
:{@{'extensionAttribute1'='YourString'}}

If you want every provisioned user to have extension attribute 2 populated with the corresponding value from the column in your data source file, then use this:

Copy
{@{'extensionAttribute2'=$_.EXT2}}

since EXT2 is the name of the column from your data source.

NOTE: If you set mapping for the same properties both in 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.

© Copyright 2013-2026 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

Please sign in to leave a comment.