This chapter covers dynamic and static data masking in Azure SQL, two critical data protection features tested in the AZ-500 exam under Compute Security (Objective 2.5). Understanding the difference between these masking types, their configuration, and their limitations is essential because exam questions frequently test whether candidates grasp that dynamic masking is a presentation-layer control and static masking creates a sanitized copy. Approximately 5-8% of exam questions touch on data masking, often in scenarios involving compliance with regulations like GDPR or PCI DSS.
Jump to a section
Imagine a law firm that stores sensitive client documents in a secure filing room. Different staff members have different clearance levels. A paralegal might need to see a document to know its subject matter but should not see the client’s social security number or financial details. Instead of creating separate copies of each document with redacted information, the firm uses a set of redaction rules applied at the moment a person views the document. The original document remains intact in the filing room. When a paralegal requests a document, the system applies a redaction overlay that obscures specific fields based on the paralegal’s role. The paralegal sees the document with blacked-out portions, while a senior attorney sees the full text. This is exactly how dynamic data masking works in Azure SQL. The underlying data in the database is never altered. Masking rules are defined on columns, and when a query is executed, the SQL engine checks the user’s permissions and applies the appropriate mask on the fly. The original data is always stored in plaintext; masking is a runtime transformation. This is critical to understand: dynamic data masking is not encryption, and it does not protect data at rest or in transit. It is a presentation-layer control that prevents unauthorized users from seeing sensitive data in query results. Just as a redacted document does not change the original file, dynamic data masking does not change the stored data.
What is Data Masking and Why Does It Exist?
Data masking is a technique used to protect sensitive data by replacing it with realistic but fictitious values. In Azure SQL, two forms exist: dynamic data masking (DDM) and static data masking (SDM). DDM is a feature of Azure SQL Database, Azure SQL Managed Instance, and SQL Server 2016+ that obscures sensitive data in query results on the fly, without altering the underlying database. SDM, on the other hand, creates a copy of the database with masked data, permanently replacing sensitive information. The primary driver for data masking is compliance with data privacy regulations such as GDPR, HIPAA, and PCI DSS, which require that sensitive data be protected from unauthorized access. DDM is often used in production environments to limit exposure to support staff or developers, while SDM is used for non-production environments like testing and training.
How Dynamic Data Masking Works Internally
DDM operates at the query result set level. When a user issues a SELECT query, the SQL engine evaluates the masking rules defined on columns. The masking function is applied to the result set before it is returned to the client. The original data in the database remains unchanged. The masking rules are defined using four built-in masking functions: default, email, random, and custom string. The default function masks the entire column with a predetermined value based on the data type—for example, 'XXXX' for strings, 0 for numeric types, and 01/01/1900 for dates. The email function reveals only the first character of the email address and the constant '.com', e.g., 'aXXX@XXXX.com'. The random function applies a random value within a specified range for numeric types. The custom string function allows you to define a prefix, suffix, and padding string, revealing only a specified number of characters from the beginning and end.
Key Components, Values, and Defaults
Masking functions: There are exactly four built-in functions: default(), email(), random(), and custom().
Default values by data type: For string types (char, nchar, varchar, nvarchar, text, ntext), the default mask is 'XXXX' (or fewer if the column is shorter). For binary types (binary, varbinary, image), the mask is a single byte of 0x00. For date types (date, datetime, datetime2, etc.), the mask is '1900-01-01 00:00:00.000'. For numeric types (bit, tinyint, smallint, int, bigint, decimal, money, etc.), the mask is 0. For XML, the mask is an empty document. For other types, the mask is an empty value.
Permissions: To define a mask, you need ALTER ANY MASK permission. To view unmasked data, a user needs the UNMASK permission. Users without UNMASK see masked data. The database owner (dbo) automatically has UNMASK.
Performance impact: DDM adds negligible overhead because masking is applied after query execution. However, columns with masks cannot be used as keys in indexes or full-text indexes.
Limitations: DDM does not encrypt data; it only hides it from query results. It does not prevent data from being exposed through other means like backup files, export, or direct file access. It also does not protect against inference attacks where a user can deduce masked values through repeated queries (e.g., using WHERE clauses to guess values).
Configuration and Verification Commands
To add a mask on a column, use the ALTER TABLE statement with the MASKED WITH FUNCTION clause. Example:
ALTER TABLE dbo.Employees
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');To add a custom string mask that reveals the first two and last two characters:
ALTER TABLE dbo.Employees
ALTER COLUMN SSN ADD MASKED WITH (FUNCTION = 'custom(first_two, last_two, 'XXXX')');To grant UNMASK permission:
GRANT UNMASK TO [user@domain.com];To revoke UNMASK:
REVOKE UNMASK FROM [user@domain.com];To view masking configuration:
SELECT * FROM sys.masked_columns;This system view returns all columns that have a mask defined, along with the masking function.
Interaction with Related Technologies
DDM works alongside other Azure SQL security features, but there are important interactions:
Always Encrypted: DDM and Always Encrypted can be used together, but the mask is applied after decryption. If a column is encrypted with Always Encrypted, the mask will be applied to the decrypted value. However, if the client driver does not support Always Encrypted, the masked encrypted value may appear as binary data.
Row-Level Security (RLS): DDM and RLS can be combined. RLS filters rows based on user predicates, and DDM masks columns in the returned rows. Both are applied at query time.
Auditing: DDM does not affect SQL Server Audit or Azure SQL Auditing. Audit logs capture the actual query and results, which may include masked data if the auditor has UNMASK.
T-SQL functions: Masked data can be accessed via T-SQL functions like SUBSTRING or LEN, which operate on the masked value, not the original.
Static Data Masking (SDM)
SDM is a feature of Azure SQL Database (in preview) and SQL Server 2019+ (as part of Data Masking & Redaction). Unlike DDM, SDM creates a copy of the database with permanently masked data. The original data is replaced in the new copy. SDM is used for non-production environments where you need realistic but sanitized data. The masking process is performed once, and the resulting database contains only masked data. SDM uses the same masking functions as DDM but applies them during the copy operation. To perform SDM, you can use the Azure portal or T-SQL commands such as CREATE DATABASE AS COPY OF with masking options.
Exam-Relevant Details
DDM is a per-column, per-user permission-based masking.
UNMASK permission can be granted to database roles and users.
DDM does not prevent data export; users with SELECT permission can copy masked data, but the copied data remains masked.
DDM is not a replacement for encryption; it is a compliance tool for limiting data exposure.
The exam may present scenarios where a company needs to protect sensitive data from developers; the correct answer often involves DDM with UNMASK denied, not encryption.
Static data masking is used when you need a separate, sanitized copy of the database for testing or training; the exam may ask which masking type to use for non-production environments.
Identify Sensitive Columns
The first step in implementing dynamic data masking is to identify which columns contain sensitive data that needs protection. Common examples include social security numbers, credit card numbers, email addresses, phone numbers, and salary information. For each column, determine the appropriate masking function based on the data type and the level of exposure allowed. For instance, an email column might use the email() function to reveal only the first character, while a salary column might use the default() function to show 0. Document the masking requirements for each column, considering the principle of least privilege.
Define Masking Rules
Once sensitive columns are identified, define masking rules using the ALTER TABLE statement with the MASKED WITH FUNCTION clause. For each column, choose one of the four built-in masking functions: default(), email(), random(), or custom(). The custom() function allows you to specify a prefix, suffix, and padding string. For example, to mask a credit card number showing only the last four digits, you could use custom('', 'XXXX', 'XXXX-XXXX-XXXX-') which would result in 'XXXX-XXXX-XXXX-1234'. Ensure that the masking function is appropriate for the data type; using a function that returns a value of a different type may cause errors.
Grant and Revoke UNMASK Permission
After defining masks, manage access to the original data by granting or revoking the UNMASK permission. Users without UNMASK will see masked data in query results. Grant UNMASK only to users who absolutely need to see the original values, such as data analysts or compliance officers. The database owner (dbo) automatically has UNMASK. Use the GRANT UNMASK and REVOKE UNMASK T-SQL commands. Remember that UNMASK is a database-level permission, so it applies to all masked columns in the database. There is no per-column UNMASK.
Test Masking Behavior
Before deploying to production, test the masking behavior by connecting as a user without UNMASK and executing SELECT queries on the masked columns. Verify that the masked values appear as expected. Also test that users with UNMASK see the original values. Check edge cases, such as NULL values (which remain NULL even when masked) and columns with insufficient length for the mask (e.g., a varchar(3) column with default mask will show 'XXX' instead of 'XXXX'). Ensure that masking does not break application functionality that depends on the original data format.
Monitor and Audit Masking
After implementing DDM, monitor its usage and audit access to sensitive data. Use Azure SQL Auditing to log queries that access masked columns. Review audit logs to detect any attempts to infer masked values through brute force or pattern matching. Also, periodically review the list of users with UNMASK permission to ensure it remains minimal. Consider using dynamic management views (DMVs) like sys.dm_exec_requests to monitor performance impact, though DDM typically adds negligible overhead. If performance issues arise, check for queries that frequently access masked columns and consider indexing strategies (note: masked columns cannot be indexed).
Enterprise Scenario 1: Healthcare Application Compliance
A healthcare provider runs an Azure SQL Database containing patient records with sensitive fields like social security numbers (SSN), diagnosis codes, and insurance details. To comply with HIPAA, they need to ensure that support staff and developers working on the application do not see actual patient data. The DBA defines dynamic data masking on the SSN column using the custom() function to show only the last four digits, and on the diagnosis column using the default() mask to show 'XXX'. Developers are granted SELECT but not UNMASK, so they see masked data in their queries. The production database remains intact, and only authorized medical staff have UNMASK. This setup allows the development team to build and test features without exposing sensitive patient information. A common mistake is to assume that masking prevents data export; however, developers can still copy masked data to their local machines, which is acceptable since the data is already masked. Performance is not an issue because the database is small (under 100 GB), but for larger databases, the overhead of masking is still minimal.
Enterprise Scenario 2: Financial Services Testing Environment
A financial institution needs to create a test database with realistic but non-sensitive data for QA testing. They use static data masking to create a copy of the production database with all credit card numbers and personal information replaced with fictitious but realistic values. The SDM process runs overnight, creating a new database with the suffix '_Test'. QA engineers connect to this database and run their tests without any risk of data leakage. The challenge is ensuring that the masked data remains referentially intact (e.g., foreign keys still work) and that the masking functions produce values that are valid for the application (e.g., valid credit card number format). If the masking is not properly configured, test scripts may fail due to invalid data formats. The institution uses the custom() function to preserve the last four digits while randomizing the rest, ensuring the masked data passes basic format validation.
Scenario 3: Multi-Tenant SaaS Application
A SaaS company hosts a multi-tenant application on Azure SQL Database where each tenant's data is stored in separate schemas. They use dynamic data masking to prevent tenant support agents from viewing other tenants' data. Masking rules are applied to columns like email and phone number. However, a critical issue arises: the masking function email() only reveals the first character, but support agents need to see the full domain to route tickets correctly. The DBA switches to a custom mask that reveals the first character and the domain (e.g., 'j***@example.com'). This is achieved using a custom string mask with prefix 'j' and suffix '@example.com'. The exam may test this scenario by asking which masking function to use when the requirement is to reveal the domain but hide the local part.
AZ-500 Exam Focus on Data Masking (Objective 2.5)
The AZ-500 exam tests your ability to implement and manage data masking in Azure SQL. Specific sub-objectives include: (1) Configure dynamic data masking, (2) Implement static data masking, (3) Manage permissions for masked data, and (4) Understand limitations and integration with other security features.
Common Wrong Answers and Why Candidates Choose Them
Confusing dynamic masking with encryption: Many candidates choose 'Always Encrypted' when the scenario requires hiding data from users without altering the database. The trap is that both protect data, but encryption protects at rest and in transit, while masking protects only in query results. The exam will present a scenario where the requirement is to 'hide sensitive data from developers without changing the underlying data' — the correct answer is dynamic data masking, not encryption.
Assuming masking prevents data export: Candidates often think that dynamic masking prevents users from copying data. In reality, users can still export query results, but the exported data is masked. The exam may ask 'What is a limitation of dynamic data masking?' and the wrong answer might be 'It prevents data from being exported.' The correct answer is 'It does not prevent data from being exported; it only masks the data in query results.'
Granting UNMASK to all developers: A common mistake is to grant UNMASK to users who need to see some unmasked columns. However, UNMASK is an all-or-nothing permission — it reveals all masked columns. The exam will test this by asking how to allow a user to see unmasked data for only one column. The correct answer is that you cannot; you must either grant UNMASK (which reveals all) or use views or other mechanisms.
Using static masking when dynamic is appropriate: The exam may describe a scenario where a company needs to create a sanitized copy of the database for testing. Candidates might choose dynamic masking, but the correct answer is static masking because dynamic masking does not create a separate copy.
Specific Numbers and Terms That Appear on the Exam
The four masking functions: default, email, random, custom.
The default mask for strings is 'XXXX' (or fewer if column length is less than 4).
The default mask for numeric types is 0.
The default mask for date types is '1900-01-01 00:00:00.000'.
UNMASK is a database-level permission.
To view masked columns, query sys.masked_columns.
Edge Cases and Exceptions
NULL values are not masked; they remain NULL.
Columns with insufficient length for the mask (e.g., varchar(2) with default mask) will show a truncated mask (e.g., 'XX' instead of 'XXXX').
Masked columns cannot be used in indexes, including full-text indexes.
DDM does not work with memory-optimized tables.
DDM is not supported for columns used in partition keys.
How to Eliminate Wrong Answers
When you see a question about data masking, first determine whether the requirement is to create a separate copy (static) or to hide data in place (dynamic). Then, check if the scenario involves altering stored data — if not, eliminate static masking and encryption. If the question mentions 'without changing the underlying data,' the answer is almost certainly dynamic data masking. Finally, remember that UNMASK is all-or-nothing; any question that suggests per-column unmasking is a trap.
Dynamic data masking is a presentation-layer control; it does not encrypt data or alter the stored data.
There are exactly four built-in masking functions: default(), email(), random(), and custom().
Default mask values: 'XXXX' for strings, 0 for numerics, '1900-01-01' for dates.
UNMASK is a database-level permission that reveals all masked columns to the grantee.
Masked columns cannot be indexed, including full-text indexes.
Static data masking creates a separate database copy with permanently masked data, used for non-production environments.
DDM does not prevent data export; users can still copy masked data.
NULL values remain NULL even when a column is masked.
To view masking configuration, query sys.masked_columns.
DDM works alongside Always Encrypted and Row-Level Security but with specific limitations.
These come up on the exam all the time. Here's how to tell them apart.
Dynamic Data Masking (DDM)
Masks data in query results without altering the database
Applied at runtime; no separate copy created
Reversible: users with UNMASK see original data
Suitable for production environments to limit exposure
Does not protect data at rest or in backups
Static Data Masking (SDM)
Creates a copy of the database with permanently masked data
Applied once during the copy operation
Irreversible: the copy contains only masked data
Suitable for non-production environments like testing and training
Protects data in the copy, but original remains unmasked
Mistake
Dynamic data masking encrypts the data at rest.
Correct
Dynamic data masking does not encrypt data. It only obscures data in query results. The underlying data is stored in plaintext. Encryption is provided by features like Transparent Data Encryption (TDE) or Always Encrypted.
Mistake
You can grant UNMASK permission on a per-column basis.
Correct
UNMASK is a database-level permission. Granting UNMASK to a user allows them to see unmasked data in all masked columns across the entire database. There is no per-column UNMASK.
Mistake
Dynamic data masking prevents users from exporting data.
Correct
Dynamic data masking does not prevent data export. Users with SELECT permission can export query results, but the exported data will be masked. The data is not protected at rest or in transit.
Mistake
Static data masking and dynamic data masking are interchangeable.
Correct
They serve different purposes. Dynamic masking hides data in real-time without altering the database, while static masking creates a separate copy with permanently masked data. Static masking is used for non-production environments.
Mistake
Masked columns can be used in indexes.
Correct
Columns with dynamic data masking cannot be part of an index, including full-text indexes. This is a limitation that can affect query performance.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
Dynamic data masking (DDM) hides sensitive data in query results without altering the underlying database. It is applied at runtime and can be reversed by granting the UNMASK permission. Static data masking (SDM) creates a new database copy with permanently masked data, which is irreversible. DDM is used in production to limit data exposure, while SDM is used for non-production environments like testing and training. The exam often tests this distinction.
Grant the UNMASK permission at the database level using the command: GRANT UNMASK TO [user];. This gives the user the ability to see original values in all masked columns. There is no per-column UNMASK; it is all-or-nothing. The database owner (dbo) automatically has UNMASK. To revoke, use REVOKE UNMASK FROM [user];.
No. Dynamic data masking only masks data in query results. Backups contain the original unmasked data. To protect data in backups, you need to use Transparent Data Encryption (TDE) or other encryption methods. Static data masking is also not a solution for backup protection because it creates a separate copy; the original backup remains unmasked.
Key limitations include: (1) It does not encrypt data; (2) It does not prevent data export; (3) Masked columns cannot be indexed; (4) It is not supported for memory-optimized tables; (5) NULL values are not masked; (6) It does not protect against inference attacks; (7) UNMASK is all-or-nothing per database. These limitations are frequently tested on the AZ-500 exam.
Query the system view sys.masked_columns. For example: SELECT * FROM sys.masked_columns;. This returns the object ID, column name, and masking function for each masked column. You can also use the Azure portal to view masking rules under the Security section of the database.
Yes, they can be used together, but the mask is applied after decryption. If a column is encrypted with Always Encrypted, the client driver decrypts the data, and then DDM applies the mask. If the client does not support Always Encrypted, the masked encrypted value may appear as binary data. This combination is advanced and rarely tested but may appear in scenario-based questions.
The default mask for date columns (date, datetime, datetime2, etc.) is '1900-01-01 00:00:00.000'. For time columns, it is '00:00:00.0000000'. This is a fixed value that does not depend on the actual data. The exam may ask for this specific default.
You've just covered Dynamic and Static Data Masking in Azure SQL — now see how well it sticks with free AZ-500 practice questions. Full explanations included, no account needed.
Done with this chapter?