Hotspot Question You have an Azure subscription. The subscription contains an Azure SQL managed instance that stores employee details, including social security numbers and phone numbers. You need…

Question

Exhibit

Answer Area

• Phone numbers:Dynamic data masking
  Always EncryptedColumn encryptionDynamic data maskingTransparent Data Encryption (TDE)
• Social security numbers:Always Encrypted
  Always EncryptedColumn encryptionDynamic data maskingTransparent Data Encryption (TDE)

Explanation

This question tests knowledge of Azure SQL data protection features: Dynamic Data Masking (DDM) and Always Encrypted. Each feature serves a distinct purpose in controlling how sensitive data is exposed to different user roles.

Approach. For the phone number column (helpdesk sees only last four digits), Dynamic Data Masking should be enabled. DDM applies a mask at the query result level, so the helpdesk team sees a masked version like 'XXXX-1234' without changing the stored data - this is a presentation-layer control. For the social security number column (DBAs must be prevented from seeing it), Always Encrypted should be enabled. Always Encrypted encrypts data at the client side before it reaches the database engine, meaning even database administrators with full server access cannot view the plaintext values - only applications with the correct encryption keys can decrypt the data. DDM alone would not protect against DBAs since they can bypass masking by querying with elevated privileges or using UNMASK permissions.

Concept tested. Azure SQL Managed Instance data protection: Dynamic Data Masking (DDM) for role-based obfuscation of data in query results, and Always Encrypted for cryptographic protection that prevents privileged server-side users (including DBAs) from accessing sensitive plaintext column values.

Reference. https://learn.microsoft.com/en-us/azure/azure-sql/database/dynamic-data-masking-overview and https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine

No community discussion yet for this question.