You are the database administrator for a large e-commerce company. The company has an Azure SQL Database named SalesDB that stores sensitive customer data including credit card numbers and personal details. The security team has mandated the following requirements:

1. All customer credit card numbers must be encrypted at rest and in transit between the application and the database. The encryption keys must be stored in a hardware security module (HSM) managed by the company.
2. All access to the database must be authenticated using Microsoft Entra ID, and multi-factor authentication (MFA) must be enforced for all administrative users.
3. Any attempts to access the database from unusual geographic locations must be detected and automatically blocked for 24 hours.
4. All schema changes must be audited, and the audit logs must be sent to a central Log Analytics workspace for analysis.

Currently, the database uses SQL authentication, no encryption, and no auditing. You need to implement the required security controls with minimal downtime. What should you do?

Question

You are the database administrator for a large e-commerce company. The company has an Azure SQL Database named SalesDB that stores sensitive customer data including credit card numbers and personal details. The security team has mandated the following requirements:

1. All customer credit card numbers must be encrypted at rest and in transit between the application and the database. The encryption keys must be stored in a hardware security module (HSM) managed by the company.
2. All access to the database must be authenticated using Microsoft Entra ID, and multi-factor authentication (MFA) must be enforced for all administrative users.
3. Any attempts to access the database from unusual geographic locations must be detected and automatically blocked for 24 hours.
4. All schema changes must be audited, and the audit logs must be sent to a central Log Analytics workspace for analysis.

Currently, the database uses SQL authentication, no encryption, and no auditing. You need to implement the required security controls with minimal downtime. What should you do?

Accepted Answer

Enable Always Encrypted for the credit card columns using Azure Key Vault Managed HSM for column master keys. Switch to Microsoft Entra ID authentication and configure Conditional Access policy to require MFA for administrators. Enable Microsoft Defender for SQL with anomaly detection and automatic blocking. Enable Azure SQL Auditing with Log Analytics destination.. It directly addresses all mandated requirements: Always Encrypted with Azure Key Vault Managed HSM ensures credit card numbers are encrypted at rest and in transit with customer-managed HSM keys; switching to Microsoft Entra ID authentication with a Conditional Access policy enforces MFA for administrators; Microsoft Defender for SQL provides anomaly detection that can automatically block access from unusual geographic locations for 24 hours; and Azure SQL Auditing configured to send logs to Log Analytics meets the central audit requirement. This combination provides the required security controls with minimal downtime as Always Encrypted can be enabled online.

Answer

Enable Always Encrypted for the credit card columns with keys stored in Azure Key Vault. Keep SQL authentication but require users to use MFA via a VPN. Enable TDE for at-rest encryption.

Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault. Configure Azure SQL Auditing to send logs to Log Analytics. Enable Microsoft Defender for SQL.

Answer

Use Dynamic Data Masking to mask credit card numbers. Enable TDE and use Azure SQL Auditing with a storage account. Enable Microsoft Defender for SQL.

DP-300 Implement a secure environment Practice Question

Why each option matters

Option-by-option breakdown

Question Discussion