In Directory Sync Pro for Active Directory, an override is used to transform values in the target directory based upon a formula.
The formula language used is T-SQL, used in Microsoft’s SQL Server product line. A valid select statement in T-SQL would be Select (FirstName + LastName) from BT_Person. When adding an override you do not need to include a full SQL select statement as portions of the SQL statement are generated for you. Specifically, you are not required to use the select or from commands in the override. It is only required to enter the columns that should be selected. To continue the example above, a valid override would only need to contain the value of FirstName + LastName.
To add an Override:
When you save an override, Directory Sync Pro for Active Directory re-generates the Person or Groups view. It does this by dynamically generating a single SQL statement using the snippet of SQL code that is part of all overrides. The max size for this SQL statement is 8000 total characters. If many new overrides are added, this limit could be exceeded and an error when adding the overrides will occur. In addition to the default overrides, approximately 15-20 more Person and 20-25 Group overrides can be added before hitting the size limit.
To edit an override:
To delete an override:
To export overrides:
You can reset all overrides to the “factory defaults” by clicking the Reset Overrides button. Caution: This will remove any custom overrides or any edits to existing overrides. If you have made changes, you may want to export those changed overrides before a reset. You can import them later if you wish.
Directory Sync Pro for Active Directory uses the TypeOfTransaction column from the BT_Person table, or the Operation column from the BT_Groups table to determine what action to perform on the target object. These may have overrides applied to them, to control what actions Directory Sync Pro for Active Directory will take for an object. The below image shows an example of this kind of override.
Matching user accounts with Overrides
The values used for matching can have overrides applied to them. This is accomplished by setting up a new override using the field names MatchValue1, MatchValue2, MatchValue3 and MatchValue4. Each MatchValue1-4 corresponds the respective Source and Target pair on the matching tab.
These values are used for matching only. Values that get written to the target are based on the mappings, not the matching.
When updating an existing object, the attributes UserPrincipalName and SAMAccountName will only be written in response to a change after the initial sync. To always update these attributes, change the Internal Field mapping to an unused CustomXXX field.
Internal field must be entirely blank/NULL or source written to a different Custom value.
Make your override for Custom001 and map Custom001 to UserPrincipalName…then un-map userPrincipalName.
Make your override for Custom002 and map Custom002 to sAMAccountName…then un-map sAMAccountName
Field Name | Field Value | Description |
---|---|---|
TargetAddress | CASE EntryType WHEN 'user' THEN 'SMTP:' + P.Custom20 + '@exchange.contoso.com' ELSE 'SMTP:' + dbo.ReplaceDomain(InternetAddress,'exchange.contoso.com') END | This formula will dynamically set the targetaddress value based on the EntryType. |
TargetAddress | 'SMTP:' + dbo.UpdateInternetAddress(InternetAddress,'exchange.') | This formula will set the TargetAddress value based on the InternetAddress and prefix the domain with the value specified, in this case "exchange.". |
TargetAddress | ' 'SMTP:' + dbo.ReplaceDomain(InternetAddress,'exchange.contoso.com') | This formula will set the TargetAddress value based on the InternetAddress and replace the domain with the value specified, in this case "exchange.contoso.com". |
TargetAddress | CASE WHEN InternetAddress LIKE '%@example.com' THEN 'smtp:' + dbo.UpdateInternetAddress(P.InternetAddress, 'exchange.') WHEN InternetAddress LIKE '%@knotes.contoso.com' THEN 'smtp:' + dbo.ReplaceDomain(P.InternetAddress, 'exchange.contoso.com') ELSE P.InternetAddress END | This formula will dynamically set the targetaddress value based on the existing InternetAddress domain name value. If the first domain is found then the TargetAddress will be set to one value, if the second domain is found another value will be used and if neither domain is found then the TargetAddress will be set the same as the current InternetAddress value. |
CommonName | CASE EntryType WHEN 'user' THEN 'do$$' + SourceDirectoryID WHEN 'sharedmail' THEN 'do$$' + SourceDirectoryID ELSE CommonName END | This formula will dynamically set the CommonName value based on the EntryType. |
CommonName | CASE WHEN LEN(CommonName) > 64 THEN LTRIM(RTRIM(LEFT(CommonName,64))) ELSE CommonName END | This formula will limit the CommonName value to 64 characters if it exceeds that limit. |
ProxyAddresses | CASE ProxyAddresses WHEN '' THEN 'smtp:' + dbo.ReplaceDomain(InternetAddress,'@contoso.mail.onmicrosoft.com;smtp:') + dbo.UpdateInternetAddress(InternetAddress,'exchange.') ELSE ProxyAddresses + ';smtp:' + dbo.ReplaceDomain(InternetAddress,'@contoso.mail.onmicrosoft.com;smtp:') + dbo.UpdateInternetAddress(InternetAddress,'exchange.') END | This formula will set or append to the list of ProxyAddresses values the coexistence routing addresses. This example specifically is designed for Office 365. |
Company | LTRIM(RTRIM(LEFT(company, 50))) | This formula will Trim, then limit the string value by 50 characters. |
Custom001 | 'this is a string' | This formula will set any string value to the any SQL field. |
Custom001 | REPLACE(InternetAddress,'@','.') | This formula will replace the '@' symbol with a period '.' to create a string like so. (i.e. first.last.contoso.com) |
Custom001 | LEFT(InternetAddress,CHARINDEX('@',InternetAddress)-1) | This formula will extract the localpart of InternetAddress. |
The below tables include AD fields with some kind of special processing in Directory Sync Pro for Active Directory. Fields can have the following characteristics:
Additional notes are available below for field marked with a *.
Attributes that may be set by Directory Sync Pro for Active Directory regardless of mapping:
Field | Cannot be mapped | Can be mapped / have override | May be explicitly ignored | May be set with config file |
---|---|---|---|---|
distinguishedName | • | |||
objectClass | • | |||
userPassword | • | |||
unicodePwd | • | |||
userAccountControl | • | |||
msExchRecipientDisplayType | • | • | ||
msExchRecipientTypeDetails | • | • | ||
msExchResourceDisplay | • | |||
msExchResourceSearchProperties | • | |||
msExchResourceMetaData | • | |||
showInAddressBook* | • | • | ||
msExchMasterAccountSid | • | • | ||
msExchPoliciesExcluded | • | • | ||
msExchPoliciesIncluded | • | • | ||
userAccountControl | • | • | ||
pwdLastSet | • | |||
adminDescription | • |
• |
Special processing if mapped:
Field | Cannot be mapped | Can be mapped / have override | May be explicitly ignored | May be set with config file |
---|---|---|---|---|
• | • | |||
assistant* | • | |||
manager* | • | |||
managedBy* | • | |||
altRecipient* | • | |||
authoring | • | |||
unauthOrig | • | |||
dLMemSubmitPerms | • | |||
dLMemRejectPerms | • | |||
sAMAccountName* | • | • | ||
legacyExchangeDN* | • | • | ||
mailNickname | • | • |
Never set:
Field | Cannot be mapped | Can be mapped / have override | May be explicitly ignored | May be set with config file |
---|---|---|---|---|
objectGUID | • | |||
objectSid | • | |||
whenCreated | • | |||
whenChanged | • | |||
uSNChanged | • | |||
name | • | |||
cn | • |
Attributes that may be set by Directory Sync Pro for Active Directory regardless of mapping:
Field | Cannot be mapped | Can be mapped / have override | May be explicitly ignored | May be set with config file |
---|---|---|---|---|
objectClass | • | |||
msExchRecipientDisplayType | • | • | ||
msExchVersion | • | |||
showInAddressBook* | • | • | ||
msExchPoliciesExcluded | • | • | ||
msExchPoliciesIncluded | • | |||
adminDescription | • | • |
Special processing if mapped:
Field | Cannot be mapped | Can be mapped / have override | May be explicitly ignored | May be set with config file |
---|---|---|---|---|
• | • | |||
assistant* | • | |||
manager* | • | |||
managedBy* | • | |||
altRecipient* | • | |||
authOring | • | |||
unauthOrig | • | |||
dLMemSubmitPerms | • | |||
dLMemRejectPerms | • | |||
sAMAccountName* | • | • | ||
legacyExchangeDN* | • | |||
groupType | • | • | ||
mailNickname | • | • |
Never set:
Field | Cannot be mapped | Can be mapped / have override | May be explicitly ignored | May be set with config file |
---|---|---|---|---|
objectGUID | • | |||
objectSid | • | |||
whenCreated | • | |||
whenChanged | • | |||
uSNChanged | • | |||
name | • | |||
cn | • |
Special processing by Internal Field Name:
Field | Cannot be mapped | Can be mapped / have override | May be explicitly ignored | May be set with config file | If this internal field name is mapped and value is empty, actual value comes from different internal field |
---|---|---|---|---|---|
DisplayName | • | • | |||
PrimaryAlias | • | • | |||
SAMAccountName* | • | • | |||
InternetAddress | • | • | |||
Name | • | • | |||
CommonName | • | • |
AltRecipient – all objects
Uses the AltRecipient column (or override value if specified) for the source object.
Follows the same process as Manager above.
sAMAccountName – The sAMAccountName may be calculated under special circumstances, such as when a collision occurs. For this reason, during a sync following a reset, repush, or repushpull, the existing sAMAccountName in the target will be assumed to be current and therefore will be used in any mappings involving the sAMAccountName internal field.
Directory Sync Pro for Active Directory automatically ignores certain AD built-in groups during sync. The definition of which built-in groups to ignore is held in the database table DirSync_GroupsToIgnore_PK. This table is created and populated during the install process of Directory Sync Pro for Active Directory and is not configurable from the product UI. This list of groups can be modified only with direct access and permissions to SQL. When Directory Sync Pro for Active Directory performs a sync, any group encountered that matches with a group named in the DirSync_GroupsToIgnore_PK table is skipped and not entered into the database. This built-in group handling functionality is separate from the blacklisting functionality for groups which can be configured by an operator through the product UI, so when ignored these built-in groups are not considered to be ‘blacklisted’.
The General Data Protection Regulations (GDPR) is the new European Union (EU) data protection regulations which go into effect May 25th, 2018. Under the GDPR individuals have certain rights to their personal data. They can make requests to exercise those rights to the data controller, and the controller must respond within 1 month. It is expected that the controller will verify the identity of the requestor.
There are four primary types of GDPR requests:
- Export – Request for a copy of all personal data about an individual held by this controller and any related processors. Must be in a commonly accepted portable data format.
- Update – Request to rectify inaccurate personal data.
- Delete – Request to remove all personal data about an individual from our systems. Can be initiated by an individual or by a revocation of consent process. Includes burden of proof. (Ideally follow a delete with an export to show no remaining data)
- Hold – Request to halt processing of personal data but not delete that data.
When Directory Sync Pro for Active Directory is installed, the data associated with the application is hosted locally within the client’s environment. The client has full control over this data. By default, the user and configuration data is stored in the SQL database called, “DirectorySyncPro_<date>”. It is assumed the operator has the proper administrative SQL Permissions to execute the following methods outlined.
SQL Tables containing User data:
[DirectorySyncPro_<Date>].[dbo].[BT_Person]
Unique Key Look-up Columns:
[SAMAccountName]
[TargetSAMAccountName]
[TargetUserPrincipalName]
[OriginalSAMAccountName]
[OriginalUserPrincipalName]
[UserPrincipalName]
If user data is used for matching (e.g. SAMAccountName, UserPrincipalName, etc.) then those values will also appear in one of the following columns:
[MatchValue1]
[MatchValue2]
[MatchValue3]
[MatchValue4]
[DirectorySyncPro_<Date>].[dbo].[BT_Groups]
Unique Key Look-up Columns:
[MatchValue1]
[MatchValue2]
[MatchValue3]
[MatchValue4]
Be aware that data can be mapped to different Internal Fields (table columns) depending on customer specific configuration, so just about any SQL column could theoretically contain user data if so configured. For example, if SAMAccountName has been mapped to Custom001 or to any other Internal Field selectable in the mappings. Therefore this process should be undertaken by someone knowledgeable about the schema and attribute mappings in use. It may also be helpful to work with Support when completing these requests if you are not comfortable with the database.
All user data within Directory Sync Pro for Active Directory is derived from the source Active Directory Forest configured in the product. Therefore, the authoritative source of any user related data stored in Directory Sync Pro for Active Directory is Active Directory. Any remediation required from a GDPR request should first be remediated in Active Directory or the source feeding Active Directory. Once that user data is updated in the source directory, running a new discovery within the product will update those values as well.
The following sections will provide guidance on fulfilling the 4 primary GDPR request types.
1. Exports – Request for a copy of all personal data about an individual held by this controller and any related processors. Must be in a commonly accepted portable data format.
For the purposes of this document, using PowerShell with the SQLPS Module is the recommended method to refine the results of the output. One may export any SQL Query result to a CSV file. Below is an example script to do so. Replace the variables to conform to your environment.
Import-Module sqlps
$SQLquery='SELECT * FROM [DirectorySyncPro_<Date>].[dbo].[BT_Person]'
$result=invoke-sqlcmd -query $SQLquery -serverinstance <servername> -database <dbname>
$result |export-csv c:\temp\ExportQueryResults.csv -notypeinformation
2. Updates – Request to rectify inaccurate personal data.
As previously stated, all user data within Directory Sync Pro for Active Directory is derived from the source Active Directory Forest configured in the product. Therefore, the authoritative source of user data is Active Directory. Any remediation required from a GDPR request should first be remediated in Active Directory or the source feeding Active Directory.
If editing the user data within SQL is still required, using any SQL editor such as SQL Server Management Studio, run an update command against one or more columns for one or more records. Below are examples to accomplish this. Note however, that any new discovery will update the values based on the source Active Directory.
Update multiple columns for a single record:
UPDATE [DirectorySyncPro_<Date>].[dbo].[BT_Person]
SET <Column1 Name> = <New Value1>, <Column2 Name> = <New Value2>
WHERE userPrincipalName='<Unique ID>'
UPDATE [DirectorySyncPro_<Date>].[dbo].[BT_Person]
SET <Column1 Name> = <New Value1>, <Column2 Name> = <New Value2>
WHERE userPrincipalName='<Unique ID>'
Update multiple columns for multiple records:
UPDATE [DirectorySyncPro_<Date>].[dbo].[CMTEUP_Person]
SET <Column1 Name> = <New Value1>, <Column2 Name> = <New Value2>
WHERE DistinguishedName='<Unique ID>' OR DistinguishedName='<Unique ID>'
UPDATE [DirectorySyncPro_<Date>].[dbo].[CMTEUP_PersonADData]
SET <Column1 Name> = <New Value1>, <Column2 Name> = <New Value2>
WHERE userPrincipalName='<Unique ID>' OR userPrincipalName='<Unique ID>'
Update multiple columns for multiple records using a list:
UPDATE [DirectorySyncPro_<Date>].[dbo].[BT_Person]
SET <Column1 Name> = <New Value1>, <Column2 Name> = <New Value2>
WHERE DistinguishedName IN ('<Unique ID1>', '<Unique ID2>', '<Unique ID3>')
UPDATE [DirectorySyncPro_<Date>].[dbo].[BT_Person]
SET <Column1 Name> = <New Value1>, <Column2 Name> = <New Value2>
WHERE userPrincipalName IN ('<Unique ID1>', '<Unique ID2>', '<Unique ID3>')
3. Deletes – Request to remove all personal data about an individual from our systems. Can be initiated by an individual or by a revocation of consent process. Includes burden of proof. (Ideally follow a delete with an export to show no remaining data.)
Using any SQL editor such as SQL Server Management Studio, run a Delete command against one or more records. Below are examples to accomplish this. However, as previously stated, if the user is not deleted in the source Active Directory during any subsequent new discovery the user will be re-populated into SQL. The only way to truly remove the data is to delete the source user or delete the entire SQL database when it is no longer required.
Delete a single record then verify:
DELETE FROM [DirectorySyncPro_<Date>].[dbo].[BT_Person]
WHERE SAMAccountName='<Unique ID1>'
SELECT * FROM [DirectorySyncPro_<Date>].[dbo].[BT_Person]
WHERE SAMAccountName='<Unique ID1>'
DELETE FROM [DirectorySyncPro_<Date>].[dbo].[BT_Person]
WHERE userPrincipalName='<Unique ID1>'
SELECT * FROM [DirectorySyncPro_<Date>].[dbo].[BT_Person]
WHERE userPrincipalName='<Unique ID1>'
Delete multiple records then verify:
DELETE FROM [DirectorySyncPro_<Date>].[dbo].[BT_Person]
WHERE SAMAccountName='<Unique ID1>' OR SAMAccountName='<Unique ID2>'
SELECT * FROM [DirectorySyncPro_<Date>].[dbo].[BT_Person]
WHERE SAMAccountName='<Unique ID1>' OR SAMAccountName='<Unique ID2>'
DELETE FROM [DirectorySyncPro_<Date>].[dbo].[BT_Person]
WHERE userPrincipalName='<Unique ID1>' OR userPrincipalName='<Unique ID2>'
SELECT * FROM [DirectorySyncPro_<Date>].[dbo].[BT_Person]
WHERE userPrincipalName='<Unique ID1>' OR userPrincipalName='<Unique ID2>'
Delete multiple records then verify:
DELETE FROM [DirectorySyncPro_<Date>].[dbo].[BT_Person]
WHERE SAMAccountName IN ('<Unique ID1>', '<Unique ID2>', '<Unique ID3>')
SELECT * FROM [DirectorySyncPro_<Date>].[dbo].[BT_Person]
WHERE SAMAccountName IN ('<Unique ID1>', '<Unique ID2>', '<Unique ID3>')
DELETE FROM [DirectorySyncPro_<Date>].[dbo].[BT_Person]
WHERE userPrincipalName IN ('<Unique ID1>', '<Unique ID2>', '<Unique ID3>')
SELECT * FROM [DirectorySyncPro_<Date>].[dbo].[BT_Person]
WHERE userPrincipalName IN ('<Unique ID1>', '<Unique ID2>', '<Unique ID3>')
4. Holds – Request to halt processing of personal data but not delete that data.
This can also be accomplished using the product interface. Halting a user from processing can be achieved using the Blacklist feature.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback 使用条款 隐私 Cookie Preference Center