Secure compute, storage, and databases →mediumMultiple Choice

AZ-500 Secure compute, storage, and databases Practice Question

Exhibit

SELECT 
    DatabaseName,
    ProtectionLevel,
    KeyStoreProviderName,
    KeyPath
FROM sys.column_master_keys;

Refer to the exhibit. You are querying the sys.column_master_keys view in an Azure SQL Database. What is the purpose of this query?

⚠ Common exam trap

Many candidates confuse the sys.column_master_keys view with sys.column_encryption_keys or assume it covers all encryption types (like TDE or Dynamic Data Masking), when it is exclusively for Always Encrypted's master key metadata.

Answer choices

Why each option matters

Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.

Correct answer & explanation

✓

To retrieve the column master key configuration for Always Encrypted.

The sys.column_master_keys view in Azure SQL Database specifically returns metadata about column master keys (CMKs) used by Always Encrypted. These keys protect the column encryption keys (CEKs) that encrypt sensitive data columns. Therefore, querying this view retrieves the column master key configuration for Always Encrypted, making option B correct.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✗
To check the Dynamic Data Masking policies.
Why it's wrong here
Querying sys.column_master_keys will not reveal Dynamic Data Masking (DDM) policies. DDM masking rules are defined per column using the MASKED WITH FUNCTION clause and are stored in catalog views such as sys.masked_columns, which track the masking function and whether masking is enabled. In contrast, sys.column_master_keys is specifically scoped to Always Encrypted column master key metadata, so this query would return no DDM configuration information.
✓
To retrieve the column master key configuration for Always Encrypted.
Why this is correct
The sys.column_master_keys catalog view is the correct place to retrieve the column master key (CMK) configuration for Always Encrypted. It contains one row per CMK in the database, including the key name, key store provider name (for example, 'MSSQL_CERTIFICATE_STORE' or 'AZURE_KEY_VAULT'), the key path, and settings such as allow_enclave_computations. This metadata is essential for managing and rotating the keys that protect column encryption keys in the Always Encrypted feature.
✗
To verify the configuration of Transparent Data Encryption (TDE).
Why it's wrong here
Transparent Data Encryption (TDE) and Always Encrypted are entirely separate encryption mechanisms. TDE performs database-level encryption using a Database Encryption Key (DEK) that is stored in the database boot record and protected by a certificate or an asymmetric key in the master database. To verify TDE configuration you must query sys.dm_database_encryption_keys for encryption state and sys.certificates for the DEK protector; sys.column_master_keys contains no TDE-related information.
✗
To list the encryption keys used for column-level encryption.
Why it's wrong here
While sys.column_master_keys is part of the Always Encrypted key hierarchy, it does not list the actual column encryption keys used for column-level encryption. Always Encrypted uses a two-tier key model: column master keys (stored in sys.column_master_keys) protect one or more column encryption keys (stored in sys.column_encryption_keys and sys.column_encryption_key_values), which in turn encrypt the actual column data. Other forms of column-level encryption, such as cell-level encryption using symmetric keys, rely on sys.symmetric_keys, sys.asymmetric_keys, and sys.certificates rather than this view.

Go deeper

AZ-500 Secure compute, storage, and databases Practice Question

Exhibit

Why each option matters

Option-by-option breakdown

Related to this question