A data analytics team is using Azure Synapse Analytics dedicated SQL pools. They need to ensure that specific sensitive columns, such as 'SocialSecurityNumber' and 'CreditCardNumber', are always encrypted at the column level within the database, even when accessed by database administrators. The encryption and decryption process must be handled transparently by the client application using keys managed by the application owners. Which encryption technology should be used?
- AColumn-level encryption using SQL functions
- BAlways Encrypted with secure enclaves
- CDynamic Data Masking (DDM)
- DTransparent Data Encryption (TDE)
Show answer & explanationAnswer & explanation
Correct answer: B. Always Encrypted with secure enclaves
Always Encrypted with secure enclaves is designed for this scenario. It allows client applications to encrypt sensitive data before sending it to the database and decrypt it upon retrieval. With secure enclaves, even complex computations on encrypted data are possible without exposing the data to the database engine or administrators, meeting the requirement for column-level encryption with client-side key management and protection from DBAs.
Why the other options are wrong
- A. While SQL functions can perform column-level encryption, they typically decrypt data within the database engine, exposing it to DBAs, and key management is often more complex than with Always Encrypted.
- C. DDM obfuscates data in query results but does not encrypt the data at rest or protect it from DBAs with direct access.
- D. TDE encrypts the entire database file at rest and in backups, but data is decrypted in memory for DBAs and the database engine.
Always Encrypted with Secure Enclaves
An Azure SQL Database/Synapse feature that allows client applications to encrypt sensitive data, ensuring data is never revealed in plaintext to the database engine or administrators, even during computations, by using secure enclaves.
- Client-side encryption, data remains encrypted at rest, in transit, and in use.
- Secure enclaves enable in-place computations on encrypted data.
- Keys are managed by application owners, not DBAs.
Memory trick: Always Encrypted with enclaves keeps secrets safe, even from the database itself.