Question

A healthcare application already stores patient Social Security numbers in a plaintext NVARCHAR column of an Azure SQL Database. Compliance now requires this column to be protected with Always Encrypted using randomized encryption, and administrators must be able to perform equality lookups on the encrypted data in the future. The DBA wants to encrypt the existing data in place — without exporting it to a client tool — and avoid re-inserting rows through a middle-tier driver. Which configuration allows the encryption operation and future equality searches to be performed while meeting these goals?

Accepted Answer

Configure Always Encrypted with a VBS enclave, set the column to use randomized encryption with an enclave-enabled column encryption key, and run the in-place ALTER against the enclave

Answer

Configure Always Encrypted with deterministic encryption using a non-enclave column encryption key so equality searches work, and encrypt via the SqlPackage client tool

Answer

Use Transparent Data Encryption on the database and enable a firewall rule restricting access to the compliance team

Answer

Configure Always Encrypted with randomized encryption and a non-enclave column encryption key, then run the encryption using the Set-SqlColumnEncryption PowerShell cmdlet