Dynamic Data Masking

FEBRUARY 26, 2015

Have you ever wanted to only show parts of a field to certain sets of users such as credit card numbers, telephone numbers or last names? This functionality is now possible in Azure SQL Database with Dynamic Data Masking. This month, Microsoft has continued its trend of releasing new SQL functionality to Azure SQL Database. Last month Microsoft released the Row-Level Security Preview, and this month have released Dynamic Data Masking as part of their SQL Database Update V12 (preview). Dynamic Data Masking allows all or parts of field to be obscured when the results are returned to a user. For example, a credit card could be returned with only its last 4 digits visible - XXXX-XXXX-XXXX-1234. This article will walk you how to set up dynamic data masking for a field containing credit card details.

Walkthrough

The first thing I had to make sure that I did was to create a new server in one of the correct regions. I live in Australia so I usually create my servers in Southeast Asia, but for this exercise, I created the server in the North Central US region. Information regarding when the V12 preview is coming to each region can be found here. If you want to confirm that your database is using the V12 Preview, you could just use the query below.

The next thing to do is to enable Dynamic Data Masking for the database. You do this by clicking Dynamic Data Masking (preview) and then filling out the blade as follows:

The Privileged Login will be a user that will not see the data in its masked state. The next thing to do is to set up a Mask. In my test database, I have a Customers table that also includes a credit card number. If I do a select from the Customers table, I get the following results:

In order to set up the Mask, I choose Add Mask at the top of the Dynamic Data Masking blade and fill in the table and column information that I want to mask.

In order to see the data using the mask, you need to reconnect to SQL Azure with the Security Enabled connection string, which will be <server name>.database.secure.windows.net as opposed to <server name>.database.windows.net. Remember to do this bit to save yourself cursing Azure, Microsoft, me, etc. You also need to use a user that isn't the Privileged Login that was set up above. When you connect again through Management Studio and run the same query, you get these results:

Conclusion

It is really easy to see how you could use this in a real world application. For instance, you could have a web application that uses the .secure connection string to show the masked data to users without having to write any masking code at the application or database level. This way no credit card details or phone numbers would be visible on the web. I am also really liking the trend by Microsoft to release new SQL Server functionality into Azure SQL Database first as I think that it will be a real driver for people to start using the product. Why wait for the next release when you can have the functionality now? This is just another reason to start using Azure SQL Database.

References
https://msdn.microsoft.com/library/7221fa4e-ca4a-4d5c-9f93-1b8a4af7b9e8....
http://azure.microsoft.com/en-us/documentation/articles/sql-database-dyn...
http://azure.microsoft.com/en-us/documentation/articles/sql-database-pre...

Blog Tags:

administration