Encrypt Manual Task answers

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.

Encrypt columns

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.

For detailed instructions, refer to the official Microsoft tutorial.

Encrypt the following columns with the Deterministic encryption type:

• For the Workspace (WS) database scheme:

  • In the answer table, param_name and param_value

  • In the draft_answer table, field_name and field_value

• For the Control Tower (CT) database scheme,

  • In the awshitassignmentanswer table, forminputname, forminputvalue, and correctinputvalue

  • In the external_record table, result

  • In the hitsubmissiondataitem table, jsondataitemvalues

  • In the awshitquestion table, jsonanswers

caution

In WF applications, encryption is limited. You can use only the Deterministic type. You cannot encrypt any columns in the database other than the ones listed above.

Configure database connections

To update the database connections for the CT and WS vaults, change the ct.datasource.url and tenants-config.WorkfusionRealm.datasource.url properties in the CT and WS vaults by appending the following: columnEncryptionSetting=Enabled;keyVaultProviderClientId=ClientID;keyVaultProviderClientKey=SecretKey. Use the Client ID and secret key value generated at the step where you created a service principal.

Example:

#old value
tenants-config.WorkfusionRealm.datasource.url=jdbc:sqlserver://YOUR_DATABASE_HOST:1433;databaseName=workfusion;schema=ws
 
#new value
tenants-config.WorkfusionRealm.datasource.url=jdbc:sqlserver://YOUR_DATABASE_HOST:1433;databaseName=workfusion;schema=ws;columnEncryptionSetting=Enabled;keyVaultProviderClientId=YOUR_AZURE_CLIENT_ID;keyVaultProviderClientKey=YOUR_AZURE_SECRET_KEY

info

After you finish the configuration, restart CT and WS.

Encrypt Manual Task answers with Java KeyStore

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

Create a file with encryption properties, for example, encryption.properties:

db.url=mssql_hostname:db_port
db.name=<db_name>
db.username=<mssql_user>
db.password=<mssql_pass>
keystore.location=<path_to_your_keystore>/keystore.pfx
keystore.secret=mypassword
encryption.key.name=MyCEK
master.key.name=MyCMK
keystore.alias=AlwaysEncryptedKey

# Skip creation of encryption and master keys
skip.metadata=false

# Decrypt data
decrypt=false

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

$ java -cp /opt/workfusion/tools/encryption_utils.jar com.workfusion.utils.scripts.encryption.ColumnEncryptionKeyUtils --config_file=<path_to_your_encryption_properties>/encryption.properties

Example:

$ java -cp /opt/workfusion/tools/encryption_utils.jar com.workfusion.utils.scripts.encryption.ColumnEncryptionKeyUtils --config_file=/opt/workfusion/tools/encryption.properties
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 ws.answer param_name
Migration ws.answer param_value
Migration ws.draft_answer field_name
Migration ws.draft_answer field_value

Configure database connections

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

Example:

#old value
tenants-config.WorkfusionRealm.datasource.url=jdbc:sqlserver://YOUR_DATABASE_HOST:1433;databaseName=workfusion;schema=ws
 
#new value
tenants-config.WorkfusionRealm.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, WS, and Manual Task Renderer.