How to Process GDPR Requests
What is a GDPR Request?
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.
How to handle GDPR Requests for Directory Sync Pro for Notes
When Directory Sync Pro for Notes 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 Binary Tree Support when completing these requests if you are not comfortable with the database.
Where does the Directory Sync Pro for Notes get its user data?
All user data within Directory Sync Pro for Notes 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 Notes 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 Notes 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.
Updating the SQL Password in the Configuration Files
The SQL Server Change Password Utility can be used to change the Directory Sync Pro connection strings, in the Domino and Windows server configuration files, used to access the SQL server. The utility is configured by editing the ChangeSQLServerPW.json file.
The ChangeSQLServerPW.json file requires an update to specify the file path to the configuration file that would be updated by the utility.
The file should only have one path which will be for the configuration file location on the server that the utility is to be processed on.
The file does have the default installation location for the configuration file, but this can be altered if a custom installation path has been used.
For the Domino Server installation this would update the nBTDirSync.EXE.Config file which has a default installation location in C:\Program Files\HCL\Domino
For the Windows Console Server installation this would update the BinaryTree.DirSync.Exchange.exe.Config which has a default installation location in C:\Program Files\Binary Tree\DirSync and the GlobalConnectionStrings.config which has a default installation location in C:\Program Files\Binary Tree\DirSync\WebUI
To run the SQL Server Change Password Utility:
- Edit the ChangeSQLServerPW.json file and remove the unneeded location strings, leaving the section for the server config file to be updated. Edit the ConfigFilePaths to the config file for the server installation path if necessary. Save the edited file.
- Open a command prompt and navigate to the \program files\Binary tree\dirsync directory.
Run the ChangeSQLServerPW.exe executable using the following syntax:
"changeSQLServerPW -old sqlaccount:currentpassword -new sqlaccount:newpassword"
Note that running this executable does not change the SQL SSMS password, only the encrypted Directory Sync Pro connection strings to access the SQL server. This is intended to be run after a password change for the account used to access the SQL server.
Note that running the utility directory on the configuration files being used may require using an Elevated Command Prompt (Run As Administrator).
There are three Directory Sync Pro configuration files that may need to be updated with this utility. An administrator can update these files on the servers that host them:
Run the utility once on the Windows Directory Sync Pro machine, configured to update two configuration files shown above (BinaryTree.DirSync.Exchange.exe.Config and GlobalConnectionStrings.config).
Run the utility once on the Domino Directory Sync Pro machine, configured to update one configuration file shown above (nBTDirSync.EXE.Config).
If the Windows Service and Windows UI are installed on different machines, run the utility once on the machine with Windows service, once on the machine with Windows UI, and once on the Domino Directory Sync machine.