Problem Statement –

From a security standpoint, sensitive data in the database needs to be protected against unauthorized access. Personal information of an individual like phone number, email, credit card details, bank account number, social security number needs to be stored safely in a database table. Currently to secure the personally-identifiable information (PII), we either implement customizable masking logic or go for encryption. All these workaround incurs additional cost, time and complexity. Again if your application undergoes a security audit or regulatory compliance, these loopholes are highlighted as well.

In this article, we will look at a new security feature in SQL Server 2016 called as ‘Dynamic Data Masking‘, which will now provide a database driven technique to mask sensitive data.

What is Dynamic Data Masking?

It’s very important to understand that sensitive data needs to be only exposed to the people who need to see it. Application end users and Developers are not among them.

• Application end users should have access only to the data which they need to see.
• Developers who have access to the database for development work or troubleshooting a problem, should not be allowed to see sensitive data.

SQL Server 2016 provides a new built-in security feature which will limit access to sensitive data by masking stored data. We can configure users who can have access to the unmasked data and for other unauthorized users, just show the masked data.

The best thing about Dynamic Data Masking is that the data is masked on the fly and the underlying data in the database does not change. It is very simple to enable this feature in existing databases. There is no need of any application changes to take advantage of this functionality.

It is just a simple way to mask data in the query results for end users, and not a replacement for encryption. In case of encryption, the data is actually modified, so that if the backup devices or drives are tampered, the data cannot be recovered.

What are the different Masking functions/formats available?

• DEFAULT() – This method can be described as Full masking. It replaces the content of the column with ‘XXXX’.
• EMAIL() – This method exposes the first letter of the email address and the constant suffix ‘.com’. It converts the email-id to the format aXXX@XXX.com
• PARTIAL() – This method exposes the first and the last letters and adds a custom padding string in the middle.
• RANDOM() – This method is used to mask numeric types with a random value within a specified range.

Real life implementation of Dynamic Data Masking 

The below example shows a table having different masking functions —

When the select statement is executed by an authorized user, all the data is visible —

A new user is created and granted only SELECT access on the table. When the select statement is executed by this unauthorized user, all the sensitive data is masked, as shown below —

We can leverage the sys.masked_columns view to find out all the columns in the database where masking function has been applied —

I have uploaded the entire SQL Script, used for the purpose of this article, in this link in GITHUB 

Conclusion –

Handling sensitive data is always a big risk and even a small human error can cause a huge loss. Dynamic Data Masking might not be a replacement for true encryption but certainly it is another protection layer. This is a great security feature and I am sure will be widely used by applications for protecting their sensitive information.

3 reasons why I love this feature –

• It is very simple to implement.
• It provides data masking on the fly.
• Data is not physically changed in the database, just the display behavior changes.

Related articles on SQL Server 2016 –

Built-in JSON Support in SQL Server 2016

Time Travel in SQL Server 2016 with Temporal Tables

Live Query Statistics in SQL Server 2016

Query Store in SQL Server 2016

Compare Execution Plans in SQL Server 2016

Categories: SQL Server, SQL Server 2016