Dynamic data masking
APPLIES TO: Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics
Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics support dynamic data masking. Dynamic data masking limits sensitive data exposure by masking it to non-privileged users.
Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. It’s a policy-based security feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed.
For example, a service representative at a call center might identify a caller by confirming several characters of their email address, but the complete email address shouldn't be revealed to the service representative. A masking rule can be defined that masks all the email address in the result set of any query. As another example, an appropriate data mask can be defined to protect personal data, so that a developer can query production environments for troubleshooting purposes without violating compliance regulations.
Dynamic data masking basics
You set up a dynamic data masking policy in the Azure portal by selecting the Dynamic Data Masking blade under Security in your SQL Database configuration pane. This feature cannot be set using portal for SQL Managed Instance. For more information, see Dynamic Data Masking.
Dynamic data masking policy
- SQL users excluded from masking - A set of SQL users or Azure AD identities that get unmasked data in the SQL query results. Users with administrator privileges are always excluded from masking, and see the original data without any mask.
- Masking rules - A set of rules that define the designated fields to be masked and the masking function that is used. The designated fields can be defined using a database schema name, table name, and column name.
- Masking functions - A set of methods that control the exposure of data for different scenarios.
|Masking function||Masking logic|
|Default||Full masking according to the data types of the designated fields
• Use XXXX or fewer Xs if the size of the field is less than 4 characters for string data types (nchar, ntext, nvarchar).
• Use a zero value for numeric data types (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real).
• Use 01-01-1900 for date/time data types (date, datetime2, datetime, datetimeoffset, smalldatetime, time).
• For SQL variant, the default value of the current type is used.
• For XML the document <masked/> is used.
• Use an empty value for special data types (timestamp table, hierarchyid, GUID, binary, image, varbinary spatial types).
|Credit card||Masking method, which exposes the last four digits of the designated fields and adds a constant string as a prefix in the form of a credit card.
|Masking method, which exposes the first letter and replaces the domain with XXX.com using a constant string prefix in the form of an email address.
|Random number||Masking method, which generates a random number according to the selected boundaries and actual data types. If the designated boundaries are equal, then the masking function is a constant number.
|Custom text||Masking method, which exposes the first and last characters and adds a custom padding string in the middle. If the original string is shorter than the exposed prefix and suffix, only the padding string is used.
Recommended fields to mask
The DDM recommendations engine, flags certain fields from your database as potentially sensitive fields, which may be good candidates for masking. In the Dynamic Data Masking blade in the portal, you will see the recommended columns for your database. All you need to do is click Add Mask for one or more columns and then Save to apply a mask for these fields.
Manage dynamic data masking using T-SQL
- To create a dynamic data mask, see Creating a Dynamic Data Mask.
- To add or edit a mask on an existing column, see Adding or Editing a Mask on an Existing Column.
- To grant permissions to view unmasked data, see Granting Permissions to View Unmasked Data.
- To drop a dynamic data mask, see Dropping a Dynamic Data Mask.
Set up dynamic data masking for your database using PowerShell cmdlets
Data masking policies
Data masking rules
Set up dynamic data masking for your database using the REST API
You can use the REST API to programmatically manage data masking policy and rules. The published REST API supports the following operations:
Data masking policies
- Create Or Update: Creates or updates a database data masking policy.
- Get: Gets a database data masking policy.
Data masking rules
- Create Or Update: Creates or updates a database data masking rule.
- List By Database: Gets a list of database data masking rules.
These are the built-in roles to configure dynamic data masking is:
These are the required actions to use dynamic data masking:
- Microsoft.Sql/servers/databases/dataMaskingPolicies/* Read:
- Microsoft.Sql/servers/databases/dataMaskingPolicies/read Write:
To learn more about permissions when using dynamic data masking with T-SQL command, see Permissions
- Dynamic Data Masking for SQL Server.
- Data Exposed episode about Granular Permissions for Azure SQL Dynamic Data Masking on Channel 9.