SQL Server 2016 Dynamic Data Masking

Dynamic Data Masking is a new feature introduced in SQL 2016. This is feature helps you to protect your sensitive data from unauthorized access. Microsoft has done a lots of stuff around security and Dynamic Data Masking is one of them.

Here is an easy example from Microsoft Documentation which explains this feature

A service representative at a call center may identify callers by several digits of their social security number or credit card number, but those data items should not be fully exposed to the service representative. A masking rule can be defined that masks all but the last four digits of any social security number or credit card number in the result set of any query. As another example, an appropriate data mask can be defined to protect personally identifiable information (PII) data, so that a developer can query production environments for troubleshooting purposes without violating compliance regulations.

Below is the different masking rules which you can define on your columns

• Default -> Full masking according to the data type defined 
• Email -> This is to mask the email address stored in your table
• Random -> Use on any numeric type to mask the original value with a random value within a specified range.
• Custom String -> If above listed rules doesn’t meet your needs, you can define your own masking function with custom string

Now let’s see in demo on how to implement DDM

Run the below code to have your setup ready to implement Dynamic Data Masking (DDM) on your SQL 2016 instance.

USE master

GO

CREATE DATABASE DataMaskingDemo

GO

USE DataMaskingDemo

GO

CREATE TABLE MASKING (

ID INT IDENTITY (1,1),

FirstName CHAR(20),

LastName CHAR(20),

EmailID NVARCHAR(70),

MembershipID INT,

CreditCard NVARCHAR(16)

)

INSERT INTO DataMaskingDemo..MASKING VALUES 

('Naveen','Kumar','thilagar.naveen@gmail.com',869583,'7654234592457826'),

('ABC','XYZ','abc.xyz@gmail.com',869583,'7654578242349245')

GO

SELECT * FROM DataMaskingDemo..MASKING

We will also have a SQL Login created for testing how the data looks like once its masked.

Run the below script to create your SQL login with read access to our demo database.

USE [master]

GO

CREATE LOGIN [ServiceDesk] WITH PASSWORD=N'P@ssword123' ,DEFAULT_DATABASE=[DataMaskingDemo], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

GO

USE [DataMaskingDemo]

GO

CREATE USER [ServiceDesk] FOR LOGIN [ServiceDesk]

GO

USE [DataMaskingDemo]

GO

ALTER ROLE [db_datareader] ADD MEMBER [ServiceDesk]

GO

Data from the table before its masked

So now we've got the setup ready. Ok, first let’s see how to mask the EmailID column 

T-SQL to mask the EmailID column

ALTER TABLE DataMaskingDemo..MASKING_

ALTER COLUMN EmailID NVARCHAR(70) MASKED WITH (FUNCTION = 'Email()');

Now run the same select statement from the ServiceDesk user session. Our data should be masked now

Now let’s mask the MembershipID and CreditCard details as well

T-SQL to mask MemberShipID and CreditCard information

ALTER Table DataMaskingDemo..MASKING

ALTER COLUMN MembershipID ADD MASKED WITH (FUNCTION='default()')

ALTER Table DataMaskingDemo..MASKING

ALTER COLUMN CreditCard ADD MASKED WITH (FUNCTION='partial(0,"XXX-XXXX-XXX",4)')

For CreditCard column how the partial function masks is 0 represents how many values from the starting should be visible. Second one “XXX-XXXX-XXX” represents the padding type and 4 defines from the last how many characters should be visible. 

Here is the output from ServiceDesk user after masking MemberShipID and CreditCard information

Try to use mask either FirstName or LastName column with default() masking function. You will get a new masking format, as default() function masks data as per the data type defined on the table.

Now we will see how to get the list of columns which are masked using DDM

sys.masked_columns is the new view which was added in SQL 2016 to get details about masked columns. 

T-SQL to get list of masked columns

SELECT 

DB_NAME() AS 'Database Name', 

T.name as 'Table Name', 

MC.name AS 'Column Name',

MC.masking_function as 'Masking Function' 

FROM sys.masked_columns MC 

INNER JOIN SYS.tables T 

ON MC.object_id=T.object_id

WHERE MC.is_masked=1

T-SQL Script to unmask the columns

ALTER TABLE DataMaskingDemo..MASKING 

ALTER COLUMN EmailID DROP MASKED;  

ALTER TABLE DataMaskingDemo..MASKING 

ALTER COLUMN MembershipID DROP MASKED;  

ALTER TABLE DataMaskingDemo..MASKING 

ALTER COLUMN CreditCard DROP MASKED;  

Implementing DDM is pretty straight forward and main advantage is you need not make any changes to your application. That’s one BIG advantage. This can be combined with Row-level security and TDE to make your data more secure. Hope this helps.

Cheers,

Naveen