Articles in this section

See more
Print

AD Users | Update SQL Server Database rule

Avatar
Tatiana Golubovich
  • Updated
Follow

Rule description

This rule queries the specified Active Directory scope and for each user in this scope finds a record with the same anchor in SQL Server Database and updates this record with attribute values that are specified in the Attribute mapping setting.

Note: This rule supports VARCHAR and NVARCHAR data types.

 

When to use this rule

Use this rule when you need to write certain Active Directory user attributes back to the SQL Server Database. 

For example, you can write back the information to HR/ERP/SIS system about whether the user was provisioned to Active Directory, and if he was, specify the proper samAccountName, email address and UserPrincipalName generated for this user. 

 

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 specific OU.

Important: To test rule configuration, limit the rule scope to an OU that contains test accounts or objects.

Query criteria

Query criteria are sent with the query and may improve query performance.

Tip: For different samples on the criteria builder, see KB20180410-1.

Active Directory anchor attribute

Defines the attribute in the Active Directory to which the Data Source anchor attribute is to be compared. 

Data Source Anchor attribute

Defines the column in the Data Source that will be used to determine if the user account already exists. This value is compared to the Active Directory Anchor Attribute.
Other Query Settings  

Properties to display

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

System Properties

List of properties required for this rule to be executed correctly.

Filter

 

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

Example: filter by Name column. 

Sort by

Sort result object list.

Initialization script

Usually, rules use query criteria to limit the query search scope. It improves the performance of the executed rule.

Due to PowerShell limitations, it is not possible to use the calculated expressions in query criteria. That is the point where the initialization script can help. You can initialize a global variable in this setting and then use it in query criteria.

Important: To use a variable, declared in the initialization script, in the query scope, it must be global: $global:<variable name>.

Example: Update AD users, created in the last ten days.

  1. Add this initialization script:
{$global:DatePeriod = (Get-Date).AddDays(-10)}
  1. Use $DatePeriod variable in query criteria builder:

Database Connection Settings

 

SQL instance

Specify the name of the SQL Instance as defined in the Utils Extension 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: Windows Authentication cannot be used to access a Microsoft SQL Server database. The account must be a Mixed Mode or SQL Account.

 

Action Section

Setting name Description

Attribute mapping

Map Active Directory user attributes whose values must be written back to matching database table columns.

Other Properties

  

Other Properties

If you need every updated user record in the database to have in Column 1 the value from the extensionAttribute1, then use this script:

{@{'Column1'=$FoundObject.extensionAttribute1;}} 

Important: If the script has Active Directory attributes that are not specified in Properties to display and in Active Directory anchor attributes, you need to add these attributes to System Properties.

 

Output Section

This section defines the output format of this rule.

To get more information about this section, please see the 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 Enforce/Schedule section article.

 

 Change History

Version Notes
6.4.0 The rule is introduced in the product.

 

Related articles

Comments

0 comments

Please sign in to leave a comment.