SQL Server 2016 dynamic data masking – a preface

Dynamic data masking is one of best security attribute introduced in SQL Server 2016 Community Technology Preview 3. This is a security mechanism which covers the sensitive piece of information in the output of a user query over a set of defined column. This feature is very easy to implement with Existing code as this is applied at the Query output level. Main motivation to use this feature is to restrict visibility of critical data from unauthorized viewing. This is a security mechanism which hides the sensitive piece of information in the output of a user query over a set of defined column.

e.g. an application might show a Credit Card number and cover everything except the last three characters. Rather than showing 891-634-932, it would show XXX-XXX-932. Since this data obfuscation is performed at the output level base on the user’s permissions and type and it’s not masked at the storage level, hence it is not a complete solution to secure the data. Dynamic Data masking is totally different from encryption feature. But this can be considered as part of the Security policy.

Dynamic data masking is very helpful in some of the situations like

• As self explained, It can be used in some situations where Data obfuscation is mandatory as per the Government regulatory like Sarbanes-Oxley, PCI DSS, HIPAA etc. According to the best practices it’s always very important for Financial and Insurance organizations to protect the sensitive and personal information using best practices.
• Apart from this Dynamic data masking can be utilized for Developers. We often get numerous requests from development teams to share the data for testing purpose, in those scenarios while sharing information we must obfuscate the sensitive data using some mechanism. Using Dynamic data masking real time masked information can be shared with the developers for more realistic testing.

SQL Server Dynamic data masking is available in four different formats based on the data types.

Default- Fully masked data as per the data types of the selected fields.

example: ALTER COLUMN City ADD MASKED WITH (FUNCTION = 'default()') 

Email – Using this method we can apply masking on the email data of the users.

example: ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()') 

Custom String – As per the name, this method can be used to mask the starting characters and last characters as per the custom requirement.

example: ALTER COLUMN [Mobile Number] ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)')

Random – This function is used to replace number columns with random values.

example: ALTER COLUMN [OrdrerNO] ADD MASKED WITH (FUNCTION = 'random(1, 5)')

In this below examples, I will show you how to setup Dynamic data masking in SQL Server 2016.

1. Create a dummy table using below script and insert some data records.

2. Create a test login and database user to verify the data masking and grant read permissions on the database.

USE master
GO
CREATE LOGIN [Paul] WITH PASSWORD=N’***********’,DEFAULT DATABASE =[master]
go
USE [Adevntureworks2016CTP3]
GO
Create USER [Paul] FOR LOGIN [Paul]
Go
ALTER ROLE [data_reader] ADD MEMBER [Paul]

3. Connect to the database and verify the database permissions.
Since no data masking is applied, data is visible with read only permissions to the users.
4. Let’s use Dynamic data masking to hide the email address information of the mycustomer table using alter statement.

Use the below script to mask the email address column data from the users which have read permissions on the database.

ALTER TABLE mycustomer
ALTER COLUMN EmailAddress ADD MASKED WITH (FUNCTION = ‘email()’)

5. Now run the select statement using test user (PAUL) which has read permissions on the database and we can see that the data masking has been applied on the Email address field.

6.  There is another option available to mask email addresses as other columns using partial function. In this example we will drop the masking on the EmailAddress column and will reconfigure masking.

ALTER TABLE mycustomer
ALTER COLUMN EmailAddress DROP MASKED
GO
ALTER TABLE mycustomer
ALTER COLUMN EmailAddress ADD MASKED WITH (FUNCTION =’partial(0,”MASKED”,4)’)

7. Now select the data using same read only user, data is masked for EmailAddress field

8. In this example we will perform masking on PhoneNumber column to apply data masking using below command.

ALTER TABLE mycustomer
ALTER COLUMN PhoneNumber ADD MASKED WITH (FUNCTION =’partial(4,”-XXX-“,4)’)

9. Quering the data using read only user will display the PhoneNumber field masked as below.

This is how you can implement dynamic data masking without making major changes in the Application logic. Since this feature has been recently introduced by Microsoft, Hence there are few restrictions or limitations as following

• Data masking cannot be applied on Encrypted columns, in Filestream and on a Computed column.
• Some of the data types are also not supported e.g. max data type.

Microsoft is continuously working towards the enhancement of SQL Server features and personally I liked this feature very much, using this feature there is no need of the Data Obfuscation third party tools.