I’d be really grateful for some help with an encryption issue. I’m afraid there’s a rather
lengthy explanation of what we’re trying to do, so please bear with me…

We’re using SQL Server 2019 (RDS in AWS) and plan to encrypt a few columns accessed by a legacy application. Rather than upsetting the application which only needs to present the encrypted data in a very limited number of screens, our thought is to create a separate parallel table to hold the encrypted columns, populating them via a trigger fired by changes to the original table.

The trigger logic will copy inserted and updated values from the original table into the encrypted columns, replacing them with an obfuscation string like ‘******’ that will be visible in most of the legacy application’s screens. Then, in those few screens which need to expose the encrypted values, we’ll adjust the underlying queries to join in the encrypted table.

As an example:

Original table: Employees
[id] [int] IDENTITY(1,1) NOT NULL,
[spouse] varchar NOT NULL

Parallel table: Employees_Encrypted
[id] [int] NOT NULL,
[spouse] varchar NOT NULL (encrypted column)

When adding a record to the original table, the trigger will insert a record into the encrypted table, copying the [id] and [spouse] values, then replacing the [spouse] value in the original table with our obfuscation string. Updates to [spouse] in the original table will be handled in a similar fashion, updating the encrypted table’s [spouse] value.

So far, so good…

Here’s where my real question starts. We’d like to use Always Encrypted to handle encryption/decryption and are aware of the parameterization and collation which we’ll need to incorporate in queries that access the encrypted data. However, Always Encrypted relies on the database driver to perform the encryption/decryption process and when utilizing a trigger, everything is internal to the database so there is no driver involved. I’m assuming, therefore, that our triggers will need to programmatically encrypt the [spouse] data as it’s written into the [Employees_Encrypted] table.

Can anyone offer any advice on how to best handle this? Perhaps we shouldn’t be using Always Encrypted.

Any guidance will be greatly appreciated.

Thanks in advance

Malcolm