Securing Your Data in SQL Server 2016 Part I: Dynamic Data Masking

Data security remains a hot issue as news of data leaks and breaches continue to make headlines. KTL developers work to stay ahead by exploring new security features in SQL to incorporate into our upgrade deployments and software customization to help customers further secure their data. SQL 2016 offers three important new security features. The first of which we’ll cover here is called Dynamic Data Masking, a feature that will be of particular interest for those whose databases store personal data that is visible to many eyes working inside the organization.

Dynamic Data Masking (DDM) is a system that partially obscures data at the column level such that only those with unmasking permissions on the column can query the data back in its original form. What makes DDM particularly useful is that it doesn’t affect any of the permissions to query, modify, or add rows, nor does it require establishing or managing encryption keys. Since it works at the database level, it can be applied to work with any existing application.

By giving unmasking permission to only a small number of system administrators and applications that need it, a large potential security hole is closed against data theft by personnel, contractors, or 3^rd party support companies.

Let’s Demonstrate

Let’s start with a very simple customer table that has DMM applied to the phone number and email columns.

CREATE TABLE [dbo].[Customer](
                  [Id] [int] IDENTITY(1,1) NOT NULL,
                  [FirstName] [varchar](100) NOT NULL,
                  [LastName] [varchar](100) NOT NULL,
                  [Phone] [varchar](12) MASKED WITH (FUNCTION = 'default()') NULL,
                  [Email] [varchar](100) MASKED WITH (FUNCTION = 'email()') NULL,
PRIMARY KEY CLUSTERED
(
                  [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Here the new SQL statement for DDM “MASKED WITH” is applied to the “Phone” and “Email” columns along with a FUNCTION parameter. DDM comes with a number functions that let you have control how the masking is performed. The documentation for these is available using the link at the end of this post.

Now let’s add some simple test data to the table:

INSERT INTO Customer (FirstName, LastName, Phone, Email) VALUES
 ('Roberto', 'Tamburello', '555.123.4567', 'RTamburello@contoso.com'),
 ('Janice', 'Galvin', '555.123.4568', 'JGalvin@contoso.com.co'),
 ('Dan', 'Mu', '555.123.4569', 'ZMu@contoso.net'),
 ('Jane', 'Smith', '454.222.5920', 'Jane.Smith@hotmail.com'),
 ('Danny', 'Jones', '674.295.7950', 'Danny.Jones@hotmail.com')

A query of the table now appears like this:

In this case, everything appears normal as the query was done using the “sa” account having full administrative permissions, but if we switch to another SQL user account that has access to the database, but not specifically given unmasking permission, and do the same query, we see the effect of DDM:

Here the Phone and Email column data is obscured by the masking. One useful aspect of this is the user account in question has permissions to modify the database. It can add and delete rows of customer data. It simply cannot query back unobscured data without the unmasking permission. This allows for scripts or ETL applications that write or synchronize data to continue working as long they don’t key off a masked column.

DDM is not without its limitations. At this time, it cannot be used on FILESTREAM, COLUMN_SET, FULLTEXT index key, encrypted (Always Encrypted), or computed columns. DDM is also a one-way process and can only be removed by copying out the table data with unmasking permission, dropping and re-creating the table, and then reloading the data.

DDM alone is not a silver bullet for data security nor should it be used as an alternative to current practices for storing highly sensitive data like credit cards, but it can be highly useful for protecting against data harvesting theft of low and mid-level sensitive data.

Check back for the rest of the series as Dave continues to talk about the new features in SQL Server 2016. Have questions on Part I? Contact KTL Solutions at [email url=”info@ktlsolutions.com” class=””]info@ktlsolutions.com[/email].

DAVID REED | .Net Developer

David joined KTL solutions in April 2016 and has quickly shifted into the development of custom website applications and eCommerce customizations for Dynamics GP integrations. Prior to KTL, he has over 20 years of professional experience working as a software development consultant in the railroad transportation sector, and in positions at fast paced startup companies, quickly adapting to different technologies and challenging environments. His development background includes web development in C#, T-SQL and Oracle database scripting, and development of complex C# and C++ based windows desktop applications and controls using various specialized APIs such as MAPI and DirectX. David has also worked on projects linking C# development with building automation hardware, electronic sensors, and device controls.