Select Page

Introduction to Dynamic Data Masking with SQL Server 2016

Eric Russo | | November 11, 2015

Users are anticipating the release of Microsoft SQL Server 2016 and its many new features, including Dynamic Data Masking, a security feature designed to prevent sensitive data or personal information from being seen by non-privileged users.

Monitoring and managing user privileges has become an essential part of organizations’ data security. Internal attacks have become of increasing concern because they cause more damage. The attackers, in these instances, already have access or have stolen credentials to gain user privileges. Estimates are that these types of attacks cost United States businesses $400 billion annually, $348 billion of which is attributable to privileged users, according to a 2010 SANS Institute report. But many of these types of attacks are now occurring when security credentials are stolen, according to a recent report on InformationWeek‘s DARK Reading security blog.

The SANS Institute defines a privileged user as a “user who, by virtue of function, and/or seniority, has been allocated powers within the computer system, which are significantly greater than those available to the majority of users.”

As Microsoft explains:

[Dynamic data masking is] a security feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed. Dynamic data masking is easy to use with existing applications, since masking rules are applied in the query results. Many applications can mask sensitive data without modifying existing queries.

What types of data would this be? It could be a social security number, an e-mail address, credit card information, or even a telephone number.

If, for example, the mask was applied to a social security number, it might display only the last four digits of the number to a non-privileged user. Instead of 987-65-4321, only XXX-XX-4321 would be shown to a non-privileged user.

This feature is especially useful for meeting regulatory dictates or for data scrubbing to insure data integrity. The latter instance is useful for those developers who need to work with data in realistic testing and development scenarios, notes Search SQL Server. Static data masking, by contrast, causes data integrity problems.

Setting up dynamic data masking—as accomplished with the SQL Server 2016 CTP 2.2 release—is simple. There are three types of masks available – a default for full masking of a designated field; e-mail address masking; and a custom-string mask, which can be applied to phone or social security numbers.

Any user can create tables using dynamic data masking; however, to add, replace, remove or change an existing mask requires a permission called “Alter Any Mask” and “Alter” at the table level. Microsoft’s Developer Network advises “It is appropriate to grant ALTER ANY MASK to a security officer.…The CONTROL permission on the database includes both the ALTER ANY MASK and UNMASK permission.”

Of course, how this feature will be applied depends on your organization’s existing security protocols as well as regulatory dictates of HIPPA and other laws.

Dynamic data masking is a piece of a comprehensive security strategy. It does not, for example, replace encryption.

Aaron Bertrand, writing on the website mssqltips, concludes:

Dynamic data masking provides a simple way to implement obfuscation of private data (and in my tests so far, the performance impact is negligible). It is important to understand the limitations, and keep in mind that it is not true encryption and that the data cannot be protected in all scenarios.

Datavail is closely following all the SQL Server 2016 releases and updates. We are now hands-on with the trial version. For more information about updating to a newer version of SQL Server, please download our white paper, Upgrading to SQL Server 2012 and Beyond.

Datavail can help your organization make the transition to a newer version of SQL Server of your choosing. Contact Datavail to discuss a custom solution designed for your enterprise and to learn more about our database services and how our experts can help with your ongoing operations or special projects.

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

ORA-12154: TNS:could not resolve the connect identifier specified

Most people will encounter this error when their application tries to connect to an Oracle database service, but it can also be raised by one database instance trying to connect to another database service via a database link.

Jeremiah Wilton | March 4, 2009

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

12c Upgrade Bug with SQL Tuning Advisor

Learn the steps to take on your Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.