You are managing an Azure SQL Database that hosts a customer relationship management (CRM) application. The database has a table named 'Contacts' with columns: ContactID (int, primary key), Name (nvarchar(100)), Email (nvarchar(200)), Phone (nvarchar(20)), and CreditLimit (decimal(18,2)). The compliance team requires that the CreditLimit column be encrypted so that only authorized users can view it. The application must be able to search for exact matches on CreditLimit values. You need to implement encryption without changing the application code significantly. Which encryption method should you use?
Deterministic encryption produces the same ciphertext for identical plaintext, enabling equality searches on CreditLimit through the Always Encrypted-enabled driver. Randomised encryption would block exact-match queries, and application code changes stay minimal since the driver handles encryption transparently.
Why this answer
Always Encrypted with deterministic encryption is correct because it encrypts the CreditLimit column at the client driver level, ensuring data remains encrypted at rest and in transit, while still allowing exact-match searches (e.g., WHERE CreditLimit = 5000) since deterministic encryption always produces the same ciphertext for a given plaintext. This meets the compliance requirement without requiring significant application code changes, as the Azure SQL Database driver handles encryption and decryption transparently for authorized users.
Exam trap
The trap here is that candidates confuse Dynamic Data Masking with encryption, thinking masking satisfies compliance requirements, but masking is a presentation-layer feature that does not protect data from privileged users or direct database access.
How to eliminate wrong answers
Option A is wrong because randomized encryption produces different ciphertext for the same plaintext each time, which prevents equality searches (e.g., WHERE CreditLimit = 5000) and thus fails the application requirement for exact-match queries. Option C is wrong because Transparent Data Encryption (TDE) encrypts the entire database at rest but does not provide column-level granularity or restrict access to specific columns; it protects against physical theft of files, not unauthorized viewing by database users. Option D is wrong because Dynamic Data Masking obfuscates data at query results but does not encrypt the underlying data; it can be bypassed by users with direct access to the database or through inference attacks, failing the compliance requirement for encryption.