AZ-305 Practice Question 55: Encrypt Columns from Admins
Microsoft Azure Solutions Architect Expert (AZ-305) · Test 6 · question 5 of 10
A payments company stores national identification numbers in a column of an Azure SQL Database. Database administrators, cloud operators and Microsoft staff must never see the plaintext values, even with full administrative rights on the database. The application still needs to look up customers by exact identification number, and it can be given access to keys held in Azure Key Vault.
Which solution should you recommend?
Show answer and explanation
Answer:
With Always Encrypted, the client driver encrypts and decrypts the column, and the database engine only ever holds ciphertext and never has the keys in plaintext. Administrators with any level of database permission therefore cannot read the values. Deterministic encryption always produces the same ciphertext for the same plaintext, so equality lookups, joins and indexes on the column keep working; randomized encryption would be more secure but would break the exact-match lookups the application needs.
Option B encrypts data at rest on disk. The engine decrypts pages transparently for every query, so administrators querying the table see plaintext.
Option C only obscures values in query results for non-privileged users. Masking is not a security boundary against administrators, and the data is stored unencrypted.
Option D relies on permissions that a highly privileged administrator can change, and the stored data remains plaintext to anyone with sufficient rights.
Reference: Always Encrypted