AZ-305 ยท Relational data design
Protecting relational data in Azure SQL: the layered toolkit
TDE with service- or customer-managed keys, Always Encrypted, dynamic data masking, row-level security, auditing, Microsoft Defender for SQL, private endpoints and Entra-only authentication.
Key points
- Network: private endpoints, no public access.
- Entra-only authentication: no SQL logins.
- TDE: on by default; service-managed or customer-managed key (BYOK), server level.
- Always Encrypted: client driver encrypts columns; engine, DBAs never see keys.
- Dynamic data masking: masks results only; row-level security: filters rows.
- Microsoft Defender for SQL: vulnerability assessment, threat alerts (SQL injection, brute force).
Source: https://learn.microsoft.com/en-us/azure/azure-sql/database/security-overview
Practice questions
Which feature encrypts sensitive columns inside the client application so that the database engine and its administrators never see the plaintext or the keys?
- Transparent data encryption
- Always Encrypted
- Dynamic data masking
- TLS encryption in transit
Show answer
Answer: B. Always Encrypted
Always Encrypted encrypts and decrypts in the client driver, and the column master key stays in a store such as Azure Key Vault, outside the database. TDE encrypts files at rest but the engine decrypts pages in memory, so DBAs still read plaintext. Dynamic data masking only hides values in results, and TLS protects data in transit.
Which statements about dynamic data masking in Azure SQL Database are correct?
- The data stored in the database is not changed
- It encrypts the masked columns on disk
- Members of db_owner see the original values
- UNMASK can be granted at column level
- Masking is performed by the client driver
Show answer
Answer: A. The data stored in the database is not changed, C. Members of db_owner see the original values, D. UNMASK can be granted at column level
Dynamic data masking changes only what nonprivileged users see in query results; the stored data is untouched. Administrators such as the server admin, the Microsoft Entra admin and db_owner members see real values, and the UNMASK permission can be granted down to a single column. It does not encrypt anything on disk, and masking is applied by the database engine, not by the client driver.
Transparent data encryption has to be switched on manually for each newly created Azure SQL database.
Show answer
Answer: False
False. TDE is enabled by default for all newly deployed Azure SQL databases, using service-managed keys. Only some old databases (created before May 2017) were not encrypted by default. Copies, restores and geo-replicas of an encrypted database are also encrypted.
Practice 8 questions on this topic in Bitzu
Short lessons, instant explanations and spaced review of what you get wrong. Free on iOS and Android.
