AZ-500 Secure compute, storage, and databases Practice Question
You have an Azure SQL Database that stores financial data. You need to prevent unauthorized access by encrypting specific columns containing credit card numbers. The solution must allow authorized applications to query the data transparently. What should you implement?
⚠ Common exam trap
Candidates often confuse Transparent Data Encryption (TDE) with column-level encryption, mistakenly believing TDE protects specific columns from unauthorized access, when in fact TDE only protects data at rest and does not prevent authorized database users or DBAs from reading the data.
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
✓
Always Encrypted
Always Encrypted is the correct choice because it encrypts specific columns (e.g., credit card numbers) at the client-side, ensuring that the data remains encrypted both at rest and in transit, and only authorized applications with the column encryption key can decrypt and query the data transparently. This meets the requirement of preventing unauthorized access (including database administrators) while allowing transparent querying for authorized applications.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Azure Storage service encryption
Why it's wrong here
Azure Storage Service Encryption (SSE) provides automatic 256-bit AES encryption for data written to Azure Blob, File, Queue, and Table storage. It does not apply to Azure SQL Database itself, which uses its own built-in encryption technologies such as Transparent Data Encryption or Always Encrypted. While a SQL database backup stored in Azure Blob Storage would be encrypted by SSE, the live database and its specific financial columns remain unprotected at the column level. Therefore, it cannot meet the requirement to encrypt individual sensitive columns inside Azure SQL Database.
- ✗
Transparent Data Encryption (TDE)
Why it's wrong here
Transparent Data Encryption (TDE) encrypts the entire database at rest by performing real-time I/O encryption and decryption of data and log files at the page level. It protects against offline theft of physical database files, but it does not encrypt individual columns and cannot restrict which users see plaintext values in query results. Once a query is executed, TDE transparently decrypts the data and returns it in plaintext to any authorized database principal. Thus, while TDE secures data at rest, it does not provide the column-level confidentiality required for this scenario.
- ✗
Dynamic Data Masking
Why it's wrong here
Dynamic Data Masking (DDM) hides sensitive values from unauthorized users by applying masking rules to query result sets at the presentation layer. For example, a social security number like '123-45-6789' may appear as 'XXX-XX-6789' to non-privileged users, but the underlying data is still stored in plaintext in the database. DDM is not an encryption mechanism—it is an obfuscation feature and can be bypassed by users with EXEMPT permission or by directly querying the database in certain ways. Therefore, it does not provide cryptographic protection or meet the requirement for encrypting financial columns.
- ✓
Always Encrypted
Why this is correct
Always Encrypted is a client-side encryption technology that encrypts sensitive data in specific columns before it is ever sent to Azure SQL Database. The database engine only receives and stores ciphertext, and encryption/decryption occurs transparently inside the client application using a column encryption key protected by a column master key stored in Azure Key Vault or a Windows certificate store. This ensures that even database administrators and cloud operators cannot view the plaintext financial data. Authorized applications that hold the column master key can query and decrypt the data transparently, making Always Encrypted the correct choice for protecting individual financial columns.
Go deeper
Related to this question
About these practice questions
One of 617 original AZ-500 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 AZ-500 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 AZ-500 exam.