Encrypt Manual Task answers with Java KeyStore

Sensitive customer information is protected by encryption at the Data Store level. However, the same data is used in Manual Tasks, namely, in Worker answers from Workspace, thus persisting in system tables without encryption.

A new mechanism is implemented to protect the sensitive Workspace data at the database level. To use it, enable the MS SQL column encryption with Java KeyStore by performing the steps below.

The following columns will be encrypted with the Deterministic encryption type:

• For the Workspace (WS) database scheme, param_name and param_value in the hit_submission table.

• For the Control Tower (CT) database scheme, forminputname, forminputvalue, correctinputvalue in the awshitassignmentanswer table, jsondataitemvalues in the hitsubmissiondataitem table, and jsonAnswers in the awshitquestion table.

Create KeyStore

Connect to the Master server via SSH and run the following command as WFUSER:

$ keytool -genkeypair -keyalg RSA -dname "CN=%1,OU=Unit,O=Organization,L=City,S=State,C=US" -alias AlwaysEncryptedKey -keystore <path_to_your_keystore>/keystore.pfx -storepass mypassword -validity 360 -keysize 2048 -storetype pkcs12 -keypass mypassword

Run column encryption tool

Go to INSTALL_DIR/wf_installer/utils and run the following command as WFUSER:

$ java -Ddb_url=<mssql_hostname>:<db_port> -Ddb_name=<db_pass> -Ddb_username=<mssql_user> -Ddb_password=<mssql_pass> -Dks_location=<path_to_your_keystore>/keystore.pfx -Dks_secret=mypassword -Dks_alias=AlwaysEncryptedKey -Dencryption_key=MyCEK -Dmaster_key_name=MyCMK -cp <install_dir>/wf_installer/utils/column-encryption-tool.jar com.workfusion.installer.utils.ColumnEncryptionKeyUtils

Example:

$ java -Ddb_url=test-wfan-10024-mssql1.wflab.io:1433 -Ddb_name=workfusion -Ddb_username=sa -Ddb_password=mkjgffk*j -Dks_location=/opt/workfusion/wf_installer/keystore.pfx -Dks_secret=mypassword -Dks_alias=AlwaysEncryptedKey -Dencryption_key=MyCEK -Dmaster_key_name=MyCMK -cp /opt/workfusion/wf_installer/column-encryption-tool.jar com.workfusion.installer.utils.ColumnEncryptionKeyUtils
Master key created with name : MyCMK
Column encryption key created with name : MyCEK
Migration ct.hitsubmissiondataitem jsondataitemvalues
Migration ct.awshitquestion jsonAnswers
Migration ct.awshitassignmentanswer forminputname
Migration ct.awshitassignmentanswer forminputvalue
Migration ct.awshitassignmentanswer correctinputvalue
Migration ws.hit_submission param_name
Migration ws.hit_submission param_value

Configure database connections

To update the database connections for the WS and CT vaults, change the ct.datasource.url and ws.datasource.url properties in WS and CT vaults by appending the following: columnEncryptionSetting=Enabled;keyStoreAuthentication=JavaKeyStorePassword;keyStoreLocation=<path_to_your_keystore>/keystore.pfx;keyStoreSecret=mypassword.

Example:

#old value
ws.datasource.url=jdbc:sqlserver://YOUR_DATABASE_HOST:1433;databaseName=workfusion;schema=ws
 
#new value
ws.datasource.url=jdbc:sqlserver://YOUR_DATABASE_HOST:1433;databaseName=workfusion;schema=ws;columnEncryptionSetting=Enabled;keyStoreAuthentication=JavaKeyStorePassword;keyStoreLocation=/opt/workfusion/wf_installer/keystore.pfx;keyStoreSecret=mypassword

info

After you finish the configuration, restart CT and WS.