This feature helps us set masking rules so sensitive data can be masked with a bunch of XXXX’s , so that column level security does not force you to change the schema
the unfortunate thing is that we don’t get to set it using the Azure portal for Azure synapse . We will need to use the REST API or the CLI for the same
Lets look at the rules before we look at the CLI – see pic below to see the options we get to Data Masking . in the case below , the random number range is greyed out because the column selected is not numeric.
Masking rules and functions
| 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. XXXX-XXXX-XXXX-1234 |
| 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. aXX@XXXX.com | |
| 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. prefix[padding]suffix  |
now lets look at how to set this on Azure synapse using CLI
- we will use power shell to connect
- microsoft has provided some Azure cmdlets in power shell
- you need to install the module az first , open up power shell ISE and enter the command below
- Install-Module -Name Az -AllowClobber -Scope CurrentUser
- run these commands below
PS C:\WINDOWS\system32> Connect-AzAccount
Account SubscriptionName TenantId
——- —————- ——–
xxxx@outlook.com Visual Studio Enterprise 55xxxx
PS C:\WINDOWS\system32> Get-AzSqlDatabaseDataMaskingPolicy -ResourceGroupName “sjvzdw” -ServerName “sjvzdwsrvr” -DatabaseName “sjvzdwpool”
DatabaseName : sjvzdwpool
ResourceGroupName : sjvzdw
ServerName : sjvzdwsrvr
DataMaskingState : Disabled
PrivilegedUsers :
- As you can see the DataMaskingState is Disabled
- Now run this command to create a DataMasking rule
- New-AzSqlDatabaseDataMaskingRule -ResourceGroupName “sjvzdw” -ServerName “sjvzdwsrvr” -DatabaseName “sjvzdwpool” -SchemaName “twh” -TableName “loaditineraries” -ColumnName “session_key” -MaskingFunction “Default”
- The following masking function values are allowed
- NoMasking
- Default
- Text
- Number
- SocialSecurityNumber
- CreditCardNumber
if you rerun the Get command again , you will see that the DataMasking State has been enabled
References –