Dynamic Data Masking in Azure SQL

Dynamic Data Masking is available for Azure SQL Database as one of the main security features. There is more on Data Masking on Microsoft Docs. I don’t want to repeat all these technical details, but rather provide you with a simple tutorial on how to set up Dynamic Data Masking for Azure SQL Database. Free Azure subscription is the only thing you will need to try. Please create one Azure SQL Database for testing or use an existing one.

Creating a masking rule

1. Navigate to your database in Azure Portal and choose the Dynamic Data Masking item from the Security menu:

2. Click Add mask button to create a new masking rule. I’m using AdwentureWorkLT as a sample database for this tutorial. There is table SalesLT.Customer table with column EmailAddress which I would like to mask to hide email addresses from low privileged users.

There are few predefined masking functions and one of them is created exactly for simple masking of email addresses. Please choose it in the field Masking field format:

3. Save the rule and you will see it listed in the Masking rules overview:

We have successfully created a masking rule to hide email addresses and we will test it now.

Testing masking rule

Connect to Azure SQL Database for which we have created masking rule and run following script from SSMS, Azure Data Studio, or Query Editor in Azure Portal:

-- run as admin user
SELECT CustomerID, EmailAddress FROM SalesLT.Customer
GO

-- create user with low permissions
CREATE USER LowPermUser WITHOUT LOGIN
GO

-- grant SELECT to LowPermUser
GRANT SELECT ON SalesLT.Customer TO LowPermUser
GO

-- execute as LowPermUser
EXECUTE AS USER = 'LowPermUser'

SELECT CustomerID, EmailAddress FROM SalesLT.Customer
GO

Result set 1:

Result set 2:

The query will return two result sets you can see above. How that works?

1. The first result set is coming from a simple select to source table which we have executed against the database under the admin account which has by default all data unmasked.

2. We have created low privileged database user LowPermUser and granted only SELECT permission on our sample table.

3. We switched execution context to the LowPermUser and executed again SELECT query which returned Result set 2 containing email addresses masked with the predefined Email masking function.

That’s it. Easy.

if you would like to review existing masking rules configured for the database then it can be done with this simple query:

SELECT 
	s.name SchemaName, t.name TableName, c.name ColumnName, 
	c.is_masked, c.masking_function
FROM sys.masked_columns c
	INNER JOIN sys.tables t ON t.object_id = c.object_id 
	INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
GO

As a next step, I will recommend reviewing some security concerns related to Dynamic Data Masking.