SC-100 Design security solutions for infrastructure Practice Question
You are designing a security solution for Azure SQL Database. The requirements include: encrypting data at rest and in transit, and masking sensitive data from non-privileged users. Which two features should you implement? (Choose two.)
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
✓
Dynamic Data Masking
Transparent Data Encryption (TDE) [CORRECT] is the right choice for encrypting data at rest, as it performs real-time encryption and decryption of the database, associated backups, and transaction log files at the page level using a symmetric key protected by a certificate stored in Azure Key Vault or the service-managed keystore. Dynamic Data Masking [CORRECT] is the right choice for masking sensitive data from non-privileged users, since it limits data exposure by obfuscating the results of queries on designated columns (for example, showing XXXX for a credit card number) without altering the underlying data, and privileged users can be excluded via UNMASK permission. Encryption in transit is handled automatically by Azure SQL Database through TLS, so no additional feature is needed for that requirement. Azure Firewall (B) is a network security service for filtering traffic to Azure resources and does not provide data-at-rest encryption or data masking. Column-level encryption (D) and Always Encrypted (E) both encrypt specific column data, but they address data-at-rest confidentiality for privileged applications rather than masking data from non-privileged users, and Always Encrypted additionally requires client-side key management, making them less appropriate for the stated masking requirement.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Dynamic Data Masking
Why this is correct
Dynamic Data Masking (DDM) is a database-level security feature that obfuscates sensitive columns in query results for non-privileged users. When a user lacks the UNMASK permission, Azure SQL Database rewrites the data on the fly so that values appear masked (e.g., '123-45-6789' becomes 'XXX-XX-6789') without altering the underlying stored data. This masking is applied at query time, which means the raw data remains intact in the database, and privileged users with the UNMASK permission see the original values. DDM is ideal for hiding data from application users or junior DBAs while keeping the data physically unchanged.
- ✗
Azure Firewall
Why it's wrong here
Azure Firewall is a managed cloud network security service that filters traffic between Azure virtual networks and the internet, not a database security layer. While you can restrict network access to Azure SQL Database by setting firewall rules (e.g., limiting source IP addresses), the Azure Firewall product itself does not perform masking, encryption, or access control at the database level. Its role is entirely in the network path, so it cannot be considered a database-level security feature for protecting data inside the database. Therefore, it is not the correct answer for this scenario.
- ✓
Transparent Data Encryption (TDE)
Why this is correct
Transparent Data Encryption (TDE) performs real-time I/O encryption and decryption of your Azure SQL Database data and log files, meaning the data is encrypted at rest. TDE uses a database encryption key (DEK) that is protected by a server certificate or by Azure Key Vault, and it is completely transparent to applications because encryption and decryption happen before data is written to or read from storage. This protects your physical backup files and data disks from being stolen, but it does not mask data for users or control what a user can see in query results. Thus, TDE is a valid security control but addresses a different requirement than dynamic masking.
- ✗
Column-level encryption
Why it's wrong here
Azure SQL Database does not offer a built-in feature explicitly called 'column-level encryption' as a separate admin-configurable control. The built-in encryption for the entire database at rest is provided by Transparent Data Encryption (TDE), and for protecting individual columns end-to-end you would use client-side Always Encrypted. If a question asks about masking sensitive values from non-privileged users, mentioning 'column-level encryption' is inaccurate because encryption does not mask data on query output. It is a generic term that does not correspond to a distinct Azure SQL Database feature, making it an incorrect choice.
- ✗
Always Encrypted
Why it's wrong here
Always Encrypted is a client-side encryption technology that ensures sensitive data is never revealed in plaintext to Azure SQL Database or even to database administrators. It encrypts data within the client application, and only the client with the appropriate key can decrypt it, which protects data in transit and at rest. However, Always Encrypted does not perform dynamic masking for non-privileged users—it either provides full plaintext access (via key) or no access at all. Unlike Dynamic Data Masking, it cannot show a partially masked value (e.g., 'XXXX-XX-6789') to a specific user, so it fails to meet the requirement of masking sensitive data for unprivileged users.
Visual reference
Quick reference
Symmetric Encryption Algorithm Comparison
| Algorithm | Key Size | Block Size | Status | Notes |
|---|---|---|---|---|
| AES-128 | 128-bit | 128-bit | Current standard | NIST approved; WPA3, TLS |
| AES-256 | 256-bit | 128-bit | Current standard | Preferred for sensitive / govt data |
| 3DES | 112-bit effective | 64-bit | Deprecated (2023) | Replaced by AES |
| DES | 56-bit | 64-bit | Broken | Cracked in < 24 h; never deploy |
| ChaCha20 | 256-bit | Stream cipher | Current | TLS 1.3, WireGuard |
Go deeper
Related to this question
About these practice questions
One of 605 original SC-100 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This SC-100 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the SC-100 exam.