The default setting and default state of resting data in CAREWare is encrypted. CAREWare encrypts PII Fields in SQL to add an additional level of protection for client level data. These encrypted fields include the clients' name, gender, DOB, address, etc. These records are stored in the cw_client table which contains the client list and the cw_map_client_provider table which contains the mappings of the clients to the providers they have been added to. In cases where users prefer to disable encryption of PII fields so that a DBA can query those fields in SQL or to resolve performance issues, the EncryptPIIFields setting can be changed in the CAREWare Business Tier to decrypt those PII values changing them from a null value in the database to the value entered for the client in the CAREWare user interface.

 

A few things should be considered when deciding to make changes to encrypted fields in CAREWare:

 

CAREWare administrators should verify the CAREWare Business Tier setting EncryptPIIFields and the CAREWare database setting PIIFieldsEncrypted in common storage settings to make sure that these two settings are set to complete the expected task. The CAREWare Business Tier Setting EncryptPIIFields is the action setting telling CAREWare what to do next. A 0 tells CAREWare to decrypt currently encypted PII Fields and a 1 tells CAREWare to encrypt currently decrypted PII Fields. The setting PIIFieldsEncrypted is a flag in the database telling the CAREWare Business Tier the current state of PII fields. A 0 in that setting tells CAREWare that the PII fields are already decrypted. A 1 in that setting tells CAREWare that the PII Fields are currently encrypted. If the CAREWare Business Tier setting EncryptPIIFIelds is changed from a 1 to a 0 to decrypt the PII fields and the CAREWare database setting PIIFieldsEncrypted is also set to 0, CAREWare considers the process complete. If the reocrds are still encrypted, this indicates a discrepency between the business tier setting and the common storage value flags. This can happen in times when users are migrating CAREWare to a new server or synchronizing their data between a production server and test server.

 

So there are essentially four states for these settings:

 

EncryptPIIFields = 1 and PIIFieldsEncrypted = 1 – In this case, CAREWare is set to encrypt PII Fields, the database is stating they are already encrypted, and CAREWare would take no action.

EncryptPIIFields = 0 and PIIFieldsEncrypted = 0 - In this case, CAREWare is set to decrypt PII Fields, the database is stating they are already encrypted, and CAREWare would take no action.

EncryptPIIFields = 1 and PIIFieldsEncrypted = 0 – In this case, CAREWare is set to encrypt PII Fields, the database is stating they are already currently decrypted, and CAREWare would then begin the process of encrypting PII Fields.

EncryptPIIFields = 0 and PIIFieldsEncrypted = 1 – In this case, CAREWare is set to decrypt PII Fields, the database is stating they are already currently encrypted, and CAREWare would then begin the process of decrypting PII Fields.

Note: Users should only be changing the CAREWare Business Tier setting EncryptPIIFields. If a user believes the function isn't working or they feel they need to change the common storage setting to fix an issue, it may be best to contact the CAREWare Help Desk for support prior to changing the setting and it is definately necessary to create a backup of the CAREWare database prior to changing the setting. If the common storage setting needs to be changed, that indicates there is a conflcit between the CAREWare Business Tier setting EncryptPIIFields and the state of PII fields in the database, which should be reviewed prior to proceeding with changing the PII encryption field settings.

Note: It is important to know what these settings are prior to migrating CAREWare to a new server and prior to restoring a test environment from a production backup. Users should not change these settings in the business tier unless they are confident of the state of PII data in the backup and they are confident in knowing the value of the common storage setting PIIFieldsEncrypted in that backup.

 

The CAREWare Business Tier setting EncryptPIIFields is listed under LocalNumberStorage in the CW Admin Utility.

 

 

This is the common storage setting PIIFieldsEncrypted.

 

In the example of images provided for those settings. We can see that the business tier is set to encrypt PII fields and the database is set such that PII fields are already encrypted. In the case, CAREWare would take no action as resting PII data is currently encrypted. If a user wishes to decrypt those PII Fields at this point, they can follow instructions here to change that setting.

 

When changing these PII field encryption settings from encrypted to decrypted, the values for those PII fields are changed from null to the value entered for the client. Users can monitor the decryption process by running a query to check to see that the number of clients with null PII fields changes from the total number of clients in the database to zero by running the following query on the cw_data database.

 

Use cw_data

Select cln_last_name

From cw_client

Where cln_last_name is null

 

Use cw_data

Select map_cln_last_name

from cw_map_client_provider

Where map_cln_last_name is null

 

When running these queries prior to changing the setting, all of the clients should appear in the results as all of the clients are currently encrypted and their last names should appear as a null value in the database.

 

After changing the setting EncryptPIIFields to a 0 and restarting the CAREWare Business Tier service, users should start to see fewer and fewer results until there are no results from the query as CAREWare populates the PII fileds decrypting the values.

 

Note: During the process of decrypting or ecnrypting PII fields in CAREWare, users should be logged out. Users cannot find clients in the user interface while the state of those client elvel records do not match the business tier setting for encryption. If the clients are currently encrypted and the business tier is set to decrypt the reocrds, the currenlty encrypted records appear to be missing from Find Client results. The process should be verified as complete prior to users accessing CAREWare.

 

If a DBA wants to verify that client records are decrypted, they can run the same queries, however changing the where statement from is null to is not null.

 

Use cw_data

Select cln_last_name

From cw_client

Where cln_last_name is not null

 

Use cw_data

Select map_cln_last_name

from cw_map_client_provider

Where map_cln_last_name is not null

 

If the PII fields are currently decrypted and a CAREWare administrator is turning PII encryption back on, they can run those queries to confirm the process is complete.