A county public-health clinic stores SSN-adjacent beneficiary identifiers that must stay encrypted even from database administrators viewing data pages. Which approach should the SQL AI developer choose?
Select an answer to reveal the explanation.
Short Explanation
You need the beneficiary identifiers protected before they reach the database, so database administrators cannot read them from data pages. Always Encrypted keeps the values encrypted in the database and lets only authorized clients with the keys decrypt them.
Full Explanation
Always Encrypted is the right choice because encryption and decryption happen in the trusted client driver, using keys that do not place plaintext in the SQL engine’s path. The database stores and serves ciphertext, so administrators viewing data pages or querying the protected columns cannot obtain usable beneficiary identifiers without the client keys. Transparent Data Encryption alone does not meet this requirement: it protects database files and backups at rest, but the engine decrypts pages for query processing, allowing authorized sessions to read column values in plaintext. Storing identifiers as plain nvarchar and hiding the table name from documentation is obscurity, not encryption; an administrator who can access the table can still read the values. Disabling auditing and granting db_owner to every clinic application account increases privilege and removes accountability rather than protecting sensitive data. Exam caveat: TDE and Always Encrypted address different threats; TDE protects storage, while Always Encrypted is intended to keep values opaque to privileged database operators. Practical check: verify the clinic application uses an Always Encrypted–enabled driver, that its column keys are held in an approved key store, and that only authorized clients have decryption access. Query the column as a DBA without keys and confirm it remains ciphertext, then query through the authorized clinic client and confirm successful decryption.