Bitzu

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

Single choice

Which feature encrypts sensitive columns inside the client application so that the database engine and its administrators never see the plaintext or the keys?

  1. Transparent data encryption
  2. Always Encrypted
  3. Dynamic data masking
  4. 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.

Select all that apply

Which statements about dynamic data masking in Azure SQL Database are correct?

  1. The data stored in the database is not changed
  2. It encrypts the masked columns on disk
  3. Members of db_owner see the original values
  4. UNMASK can be granted at column level
  5. 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.

True or false

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.

Download on the App StoreGet it on Google Play

More AZ-305 topics in this module