Database column encryption for sensitive metadata field values – Part 2

Just in case you missed it, check out part 1 for an introduction to this topic.

In part 1 we configured the FileHold application server to be able to decrypt any database columns encrypted with the SQL Server Always Encrypted feature. In part 2 we will help you find and encrypt a database column. Make sure you read part 1 before jumping in here.

There are some nice tutorials at Microsoft for how to setup an encrypted column and the associated keys. However, before you tackle that you first need to find the table and column to encrypt.

Whenever you create a metadata field in FileHold a new table to store the values will be created. This is true for all types of fields except dropdown and drilldown menus. You will need to find the name of this table to add the encryption. The following SQL script will help. You may need to update the database name if you are not using the standard prefix and you can insert your metadata field name in place of SSN.

SELECT [DataTableName]
  FROM [ch_librarymanager].[dbo].[DocumentSchemaFieldDefinitions]
  WHERE Name = 'SSN'

If you are storing the sensitive values in a text field or similar, this is enough to get started. The Microsoft tutorials can kick in here. You can start the encrypt wizard by right clicking on the table name in SQL Management.

Eventually the wizard will ask you to choose column names from the table to encrypt. Choose the FieldData column. The other columns will not contain sensitive information and should not be encrypted.

Next you will be asked to choose from one of two types of encryption: deterministic or randomized. You may want to choose randomized as it gives the greatest protection, but there are several downsides that could affect FileHold and SQL Server features. The complete list is in the Microsoft documentation, but the main problem you will notice is using a database search in FileHold such as EQUALS. This will fail for the randomized case. However, if you are simply viewing metadata associated with a document found using non-encrypted data there will be no issue with randomized.

For example, you have a field SSN that you want to encrypt. You choose the randomized option and when you view metadata in the metadata panel or as part of a folder or search view you see your SSN number. However, if you try an advanced search for SSN with EQUALS you will get a search error. The error log will report a message like the following.

Encryption scheme mismatch for columns/variables '@parameter0', 'FieldData'. The encryption scheme for the columns/variables is (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'ch_librarymanager') and the expression near line '7' expects it to be (encryption_type = 'DETERMINISTIC') (or weaker).

If you are unsure of whether a field may be used for searching in the future, you may choose randomized and change it to deterministic later if you have a need to search.

The last item to setup is the encryption keys. You need at least two. The first is the column encryption key. This key is stored in the database in an encrypted format. The second is the master encryption key used for encrypting the column encryption keys. This key should be kept in a trusted key store. It will not be kept in the database. You can use the Windows certificate store, an Azure Key Vault or a Hardware Security Module (HSM).

I will not get into the details of how to manage your keys, but you should be aware that the key must be available to the client. For example, if you automatically create a master key in the Windows certificate store it will exist on the machine you were running SQL Management on likely for the user you were logged in as during the creation. Even if FileHold is running on the same machine, it is running in the context of the FileHold service user. If you create the master key as an administrator, the FileHold service user will not be able to see it and you will get an error like the following.

Failed to decrypt column 'FieldData'. Failed to decrypt a column encryption key using key store provider: 'MSSQL_CERTIFICATE_STORE'. The last 10 bytes of the encrypted column encryption key are: '4F-92-AF-F1-CE-75-06-AE-1A-A0'. Certificate with thumbprint '517DF35795D3AE19287852FD497591F4AD6466BD' not found in certificate store 'My' in certificate location 'CurrentUser'. Verify the certificate path in the column master key definition in the database is correct, and the certificate has been imported correctly into the certificate location/store.

You can solve this problem by exporting the key with the user that created it then importing it to the certificate store with the FileHold service user credentials.

Well, that’s it, except for a couple of final notes. As with any encryption implementation, make sure you backup your keys in a safe and secure way. If you lose your column encryption keys, your data will remain encrypted forever. Lastly, the values for dropdown or drilldown fields are stored in DocumentSchemaFieldChoices and DrilldownFieldChoices respectively. All field values are stored in the same two tables. Beware that if you plan to have sensitive data in a dropdown or drilldown menu, the only way to encrypt it will be to encrypt every dropdown or drilldown value.