Introduction to Dynamic Data Masking with SQL Server 2016

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.