Kenneth M. Nielsen

New Features in SQL Server 2016 – Dynamic Data Masking

July 23, 2015 by

There are many new features in SQL Server 2016, but the one we will focus on in this post is:

  • Dynamic Data Masking

Have you ever been on a website, where your personal information, ie. Social Security number or Credit Card number shown in clear text, ready for everyone to have a look at. Would it not be cool if your information was somehow masked by default, and not needed to rely on the application to mask the data before displaying it on the screen?

Examples of Datamasking

Type Value Masked
Social Security number 320474-2345 320474-****
Credit Card 4566-6546-6546-7897 4566-****-****-****
Telephone +45 45454545 +45 45******
Email kenneth@mydomain.com K*******@*********.com

If you look at the above, then it is clear that it would be really nice to have your sensitive data somewhat masked when displayed on a monitor, that other people can look at the same time.

Now with the introduction of SQL Server 2016 we have the possibility to mask data in the database, and the data is masked when queried as well – this means that the application developers should not worry about this little security issue any more.

How to use it

While it is a feature in CTP 2.1 and enabled by default in this build, you should enable a few trace flags to use it, of you are using CTP 2.0 build. The trace flags is as follows.

When this is done, you are ready to work with Dynamic Data Masking.

There are three types of masks available from SQL server as default.

Default Can be used on Nchar and Nvarchar, remember that MAX size is not supported
Use XXXX for char fields and a zero value for numeric datatypes
Can also mask datetypes, date, datetime2, datetime, smalldatetime, time and datetimeoffset
Email When using email mask the first letter in the email will be shown, and the domain
String Allows you to mask strings with you own masking string in the middle

Let’s create a table for data masking

The following table will contain information that we would like to mask later.

At the moment, the table is not enabled for masking, and every column in the table will be showing all the data it is storing. Let us also insert some data that we can work with.

Can I mask data already in a table?

Unlike the other security feature “Always Encrypted”, we have the possibility to mask data that is already in a table without the hassle of creating a new table and importing data into that one. We simply have to alter the table and columns that we need masked data in.

To do so, we will issue the following statement.

The functions that can be used is the ones stated above, so if the column was holding ie. Email addresses, the alter function could be this. The following script will enable data masking on the data in the dbo.client table.

Now data should be masked when we query it, but if we just make a SELECT * FROM [dbo].[Client] we will still be able to see the data – this is because we are logged into the database as DBO, and therefore have all the rights needed to see and manipulate data.

To test if our datamask is working, we have to create a new login and user, that is only member of the Data_Reader role, this role have rad permissions, and data will be masked dynamically.

Create Logins

Connect as Reader

When this user is created, you have to open a new Query and connect to the database using the new user.

  1. CTRL+N (opens a new query window)
  2. Right click on the query window
  3. Select Connection ➜ Disconnect

  4. Right click on the query window
  5. Select Connection ➜ Connect

  6. Select SQL Server Authentication
  7. Type in the name “DynamicMaskReader” in login
  8. Type in the password “DynamicMask” in password

  9. And click Connect

Now we are ready to query the data that we have put a dynamic datamask on.

Query the Data

In the query window, you can now issue statements where you select data from the table [dbo].[client] and you will see that the data is masked as by the function we defined earlier.

  • Firstname is masked so that the first letter is visible and all remaining letters is replaced by X
  • Lastname is masked by default, leaving all traces of a name masked and replaced by 4 X
  • Birthdate is masked to be 2000-01-01 for all records
  • Email is masked by function Email and therefore only showing first letter and top-level domain.
  • Phonenumber is masked to only show country code and last number in this number format.
  • Birthplace is masked to show only first letter and all remaining letters is replaces by X
  • SocialSecurityNumber is masked to show only the first 6 digits and masking the last 4 digit
ClientID Firstname Lastname Birthdate Email PhoneNumber Birthplace SocialSecurity
Number
1 KXXXXXXXXXX xxxx 2000-01-01 00:00:00.000 KXXX@XXXX.com +45-XXXXXX78 CXXXXXXXXXX 190574XXXX
2 PXXXXXXXXXX xxxx 2000-01-01 00:00:00.000 PXXX@XXXX.com +45-XXXXXX89 SXXXXXXXXXX 190153XXXX
3 LXXXXXXXXXX xxxx 2000-01-01 00:00:00.000 LXXX@XXXX.com +45-XXXXXX21 AXXXXXXXXXX 106123XXXX

Conclusion

This post should have given you a basic understanding of the concept of Dynamic Data Masking, and should enable you to use it on your own data/tables when you make the shift to SQL Server 2016.

I hope you have enjoyed reading and testing, and remember it is very easy to set up a test SQL Server 2016 in AZURE. Perhaps I should make a post on that ;o)

See more

Consider these free tools for SQL Server that improve database developer productivity.


Kenneth M. Nielsen

Kenneth M. Nielsen works as managing consultant and team lead for the company Rehfeld Partners in Denmark. He has worked at various consulting firms and worked on many small/large/very large BI installations in Denmark over the last 12 years.

He really likes to advise the customers to take the right decisions, but also maintains a high technical knowledge, so he can act as both architect and developer.

Over the last years, he has become a highly-rated international speaker at various SQL events. Organizing the Danish SQLSaturday and member of the board in SQLSUG.dk

View all posts by Kenneth M. Nielsen
Security, SQL Server 2016

About Kenneth M. Nielsen

Kenneth M. Nielsen works as managing consultant and team lead for the company Rehfeld Partners in Denmark. He has worked at various consulting firms and worked on many small/large/very large BI installations in Denmark over the last 12 years.

He really likes to advise the customers to take the right decisions, but also maintains a high technical knowledge, so he can act as both architect and developer.

Over the last years, he has become a highly-rated international speaker at various SQL events. Organizing the Danish SQLSaturday and member of the board in SQLSUG.dk

View all posts by Kenneth M. Nielsen

1,930 Views
  • Wilfred

    How do you define who can see the real data? For example: Helpdesk is not allowed to see the creditcard details, but order entry is allowed to see this. Is there a (new) database role for this?
    If data is masked does it mean you can’t change the data?