SQL Server Dynamic Data Masking Discovery and Implementation

By: Thomas LaRock | Comments | Related: > Security

Problem

Your SQL Server database contains data that should be masked from end users, but you have no way of determining which columns are candidates for Dynamic Data Masking (DDM). How can we identify the columns and implement SQL Server DDM to protect this data?

Solution

You can use the Database Migration Assistant to asses which columns are candidates for DDM.

The first step is to download the latest version of the Data Migration Assistant (DMA). The DMA is meant to replace the legacy SQL Server Upgrade Advisor (SSUA).

The SSUA was built to provide an assessment of your database prior to migrating to a later version of SQL Server. The DMA will also provide an assessment and can also perform the migration of your database. The assessment will not only look for migration issues, it will also look for opportunities for your database to take advantage of new features.

One of those new features is DDM. You can point the DMA at an existing database, perform an assessment, and get a list of candidate columns that you should consider for DDM.

Create an assessment with the SQL Server Data Migration Assistant

To create an assessment, you will first launch the DMA. After the DMA is launched, you create a new project:

Next, we will choose the “New features’ recommendation”:

New Features' Recommendation in SQL Server

Next, we will connect to a source server:

Connect to the source SQL Server to perform the assessment

Then, we will select a database:

Select a database to perform the SQL Server Assessment

Next, we will start the assessment. When it is completed you will notice there are three tabs: Performance, Security, and Storage. We will browse the Security tab as shown below:

Security Findings from the SQL Server Assessment

Note that the DMA is returning columns that are candidates for both DDM and Always Encrypted (AE). You should review these columns and decided which security feature is right for you.

Enable DDM For SQL Server Columns

Once you have identified columns that are candidates for DDM, you can get started by applying the mask. Keep in mind that DDM is simply a mask, it is not encryption, and it is implemented at the column level. That means you must apply a mask for each column independent of other columns. The mask is applied at the end of a database query, right before the data is returned to the client. Therefore, the performance impact of DDM is kept to a minimum.

Since these columns already exist in the database table, we will need to perform an ALTER statement in order to apply a mask. We will apply the mask to the following columns:

[Person].[EmailAddress].[EmailAddress]
[Purchasing].[Vendor].[AccountNumber]

There are four possible masking functions allowed: Default, Email, Random, and Custom String. The Default function will mask the data according to the data type, and replace the data with XXXX or 0’s. The Email function will expose only the first letter of the email address and will always put a “.com” at the end, regardless if the email is .com or not. The Random function is used on numeric data types and will replace the data with a random number from a specified range. The Custom String exposes the first and last letters and places a custom padding string in the middle.

Below are example T-SQL scripts to implement DDM:

ALTER TABLE [Person].[EmailAddress]  
ALTER COLUMN [EmailAddress] varchar(50) MASKED WITH (FUNCTION = 'email()');  

ALTER TABLE [Purchasing].[Vendor]  
ALTER COLUMN [AccountNumber] nvarchar(15) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXXX",1)');

Next, let’s create a login to the instance and give it SELECT permissions on the tables as follows:

USE [master]
GO
CREATE LOGIN [DDM_User] WITH PASSWORD= ‘M$SQLTipsRox27', 
GO
USE [AdventureWorks2012]
GO
CREATE USER [DDM_User] FOR LOGIN [DDM_User]
GO
GRANT SELECT ON [Person].[EmailAddress] TO [DDM_User]
GRANT SELECT ON [Purchasing].[Vendor]  TO [DDM_User]
GO

OK, now if you login and select from the table(s) as the DDM_User you will see masked data. Let’s have a look at what that user can expect to see from a common data analysis tools such as Excel. Here is what the [Person].[EmailAddress] table looks like:

SQL Server Dynamic Data Masking Example for Email Addresses

And here is what the [Purchasing].[Vendor] table looks like:

SQL Server Dynamic Data Masking Example for Account Number Data

Remarks

Some additional facts you should know about DDM:

The mask is preserved upon a restore of the database, as the mask is defined on the table columns.
Masks do not prevent DUI statements (Deletes, Updates, Inserts) to the table. If the user has permissions to make modifications, they will still be able to do so.
DDM is applied to users when doing imports and exports, so only masked data will be extracted by users not allowed to see unmasked data.
DDM cannot be used against a computed column, but if a computed column contains a masked column, the computed column will also be masked.

Lastly, DDM should not be considered 100% unbreakable. DDM is meant to prevent accidental exposure of data to non-privileged users. It is possible that a bad actor could construct a brute force attack to expose enough pieces of information to circumvent the mask.

Next Steps

Check out the following resources as you decide to implement DDM for your SQL Servers:

About the author

Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and a former Microsoft Certified Trainer with over 20 years’ experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips