Azure SQL Database – Dynamic Data Masking

Throughout recent articles published on this forum, we have described a number of security-related features built-into Azure SQL Database, including, most notably, Transparent Data Encryption, Row-Level Security, and Azure SQL Database Auditing. Their availability reflects the consistent effort by Microsoft to provide functional parity between Azure SQL Database and SQL Server instances running in Azure virtual machines as well as in your on-premises environment. Another example of this trend is support for Dynamic Data Masking, which we will cover in this article.

Dynamic Data Masking offers the ability to limit visibility of sensitive data stored in relational database tables accessible to non-privileged users. This functionality, supported starting with SQL Server 2016 and present in the current version of Azure SQL Database relies on a custom policy defined on the database level. The policy specifies one or more filters that should be applied to the result set returned in response to user or application initiated queries. This capability facilitates scenarios where database-resident content should not be fully exposed to database users. This is commonly required when referencing data containing Personally Identifiable Information (PII), such as Social Security Numbers (SSNs), credit card numbers, or email addresses. By applying Dynamic Data Masking, you can ensure that only a portion of the relevant column (for example, the last four digits of a SSN) is present in its original form in a result set.

A Dynamic Data Masking policy consists of the following components:

  • masking rule – a set of properties that determine columns to which the masking functions assigned through the policy should apply. The properties include a database schema name, table name, and column name.
  • masking field format – the format that determines the algorithm to be applied to the content of the original column. You have the following field formats available to you:
    • Default value – the algorithm is dependent on the data type of the fields you designate. In particular, for sting data types (including nchar, ntext, and nvarchar), the mask will consist of a sequence of XXXX characters. For numeric data types (including bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, and real), the resulting value will be simply zero. For date data types (including date, datetime2, datetime, datetimeoffset, smalldatetime, and time), the mask will yield 01-01-1900. For any special data types (for example, timestamp table, hierarchyid, GUID, binary, image, or varbinary spacial), a masked query will return an empty value.
    • Credit card value – the algorithm results in displaying only the last four digits of the full number, replacing all the preceding ones with a sequence of X characters.
    • Email – the algorithm reveals only the first letter of the email address and replaces the domain part with XXX.com. Each of the remaining characters (with the exception of @) is substituted with X.
    • Number (random number range) – the algorithm generates a random number, which value depends on the underlying data type and the From-To range that you designate.
    • Custom string – the algorithm allows you to specify a distinct number of characters from the beginning and the end of the textual content of the target field. It also applies a custom-defined padding string to the in-between portion of the field. This option can be leveraged to mask entries containing Social Security Numbers.
  • excluded users – you have the option of granting the ability to view unobfuscated data to individual, non-privileged database users as well as Azure Active Directory users and groups. Note that data masking does not apply to queries submitted by privileged users.

The most straightforward way to configure Dynamic Data Masking for individual tables within an Azure SQL Database is to use the Azure portal. Alternatively, you can also accomplish the same objective by using either Azure PowerShell (with the combination of the New-AzureRmSqlDatabaseDataMaskingRule and Set-AzureRmSqlDatabaseDataMaskingPolicy cmdlets), T-SQL, or REST API.

To illustrate a sample implementation of Dynamic Data Masking, we will use the [SalesLT].[Customer] table of the AdventureWorks LT sample database. You have the option of implementing this sample when provisioning a new database. To start, sign in to the Azure portal with an account that has sufficient permissions to provision a new instance of Azure SQL Database and, if necessary, create a new Azure SQL server. Once signed in, provision a new Azure SQL Database by specifying the following settings:

  • Database name: maskingDemoDB
  • Subscription: the name of your Azure subscription
  • Resource group: create a new resource group named maskingDemoRG
  • Select source: Sample (AdventureWorksLT)
  • Server: create a new server with the following settings:
    • Server name: a unique, valid name (verify that a green checkmark appears in the Server name textbox)
    • Server admin login: maskingDemoAdmin
    • Password: pa55w.rd1234
    • Location: an Azure region close to your physical location
    • Allow azure services to access server: make sure that the checkbox is enabled
  • Want to use SQL elastic pool: Not now
  • Pricing tier: Basic

Once the database is provisioned, on the database blade, click Dynamic Data Masking. On the resulting blade, click Add mask. This will display the Add masking rule blade. From there, specify the following settings and click Add:

  • Schema: SalesLT
  • Table: Customer
  • Column: EmailAddress (nvarchar)
  • Masking field format: Basic

Back on the Dynamic Data Masking blade, click Save to save your change. Click Overview to return to the primary view of the database and next click Tools. On the Tools blade, click Query editor (preview). Accept the preview terms and, from the Query editor (preview) blade, click Login. Use the SQL server authentication option to log in with the admin account you created when provisioning the server. From the Query editor (preview) blade, run the following:

CREATE USER 'demoUser' WITH PASSWORD = 'pas55w.rd4321';
ALTER ROLE db_datareader ADD MEMBER 'demoUser';

Ensure that the user was successfully created and then run the query against the [SalesLT].[Customer] table to return the values of the EmailAddress column and verify that all returned values are in the form aXXX@XXXX.com (you can rerun the SELECT statement by using your admin account to confirm that you get the listing of the actual values stored in the database):

EXECUTE AS USER = 'demoUser'
SELECT USER_NAME();
SELECT EmailAddress FROM [SalesLT].[Customer];
REVERT;

This concludes our coverage of the Dynamic Data Masking functionality in Azure SQL Database.

See all articles by Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles