Upgrading column encryption

When upgrading from a prior version of NexJ Contact to version 6.1, you can encrypt a previously unencrypted column.

Important: This functionality is currently available only for the Microsoft SQL Server.

Starting with NexJ Contact version 6.1, you can encrypt individual columns in a table for additional security. If you have a table with unencrypted columns in an earlier version of NexJ Contact, you can encrypt the columns during the upgrade process.

You must add the public key infrastructure (PKI) keys to the environment or server file to enable column encryption. To include column encryption in the upgrade process, you must then add a number of upgrade steps to the upgrade file.

Note: Before adding the keys to the environment or server file, you must obtain the Base64-encoded PKI keystore in the PKCS #12 format.

To upgrade columns to use column encryption:

In NexJ Studio, in the Deployment layer, in the Environments tab, open the environment or server file for your deployment.
On the Source tab, specify the available PKI key pairs.
Add the following code:
```
<PKIKeyPairs>
      <PKIKeyPair keystore="keystore1" name="storeAlias1" password="text:password"/>
</PKIKeyPairs>
```
The keystore is the Base64-encoded PKI keystore in the PKCS #12 format. The name is the alias of the PKI keystore.
The password can be encrypted and specified using the text:, base64:, hex:, or master: prefixes.
Specify which key should be used for column encryption.
Add the following code:
```
<CipherKey>
      <Active name="storeAlias1"/>
      <Obsolete name=""/>
</CipherKey>
```
If you are upgrading from non-encrypted to encrypted columns, the active key is the name of one of the PKI keystores specified earlier.
The same key is used for all encrypted columns. You cannot specify a different key for different columns.
In NexJ Studio, in the Persistence layer, in the Upgrade tab, open the Main upgrade file.

Add a step to verify that another encryption key upgrade is not taking place concurrently.

Add the following code:

<Exec description="Check version table">
   <Switch>
      <SQL adapter="MSSQL">
         <![CDATA[if not exists(SELECT 1 FROM ${table:Version} WHERE obsoleteCipherKey IS NULL OR obsoleteCipherKey = 0x00)
         begin 
            raiserror ('Cannot encrypt when in the middle of cipher key upgrade', 0, 0);
         end]]>
      </SQL>
   </Switch>
</Exec>

For every column that needs to be encrypted, add the steps to null-encrypt the existing data.

For each column, add the following code:

<Exec description="null-encrypt">
   <Switch>
      <SQL adapter="MSSQL"><![CDATA[UPDATE ${table:myTable} SET myColumn=${object:NULL_ENCRYPT_STRING}(myColumn)]]></SQL>
   </Switch>
</Exec>

For every column that needs to be encrypted, add the steps for converting the column from from string to binary.
For each column, add the following code:
```
<AlterColumn caseInsensitive="true" converter="Converter.StringCipher" name="myColumn" nullable="true" table="myTable" type="binary"/>
```

Add the step to flag the database for column encryption update.

Add the following code:

<Exec description="flag for column encryption update">
   <Switch>
      <SQL adapter="MSSQL"><![CDATA[UPDATE ${table:Version} SET obsoleteCipherKey=0x00]]></SQL>
   </Switch>
</Exec>

Save and close the Main upgrade file.

After completing these steps, you have enabled encryption and ensured that the upgrade file contains the required steps for encrypting the specified columns.

To complete the upgrade process, do the following:

Stop the NexJ application.
Run the Main.upgrade script.
Start the NexJ application.

To verify that the upgrade completed successfully, review the log for the following statements:

nexj.core.meta.Metaclass.SysCipher
Updating data encryption cipher keys
Upgrading encryption for: <Metaclass> <attributes>
Finished data encryption upgrade