SQL Server – 2016 – New Feature: Dynamic Data Masking

As most of you know that latest update from Microsoft about SQL Server 2016 was released of CTP2.1 version. There is a long list of new features getting introduced in SQL Server 2016. In this blog, I would talk about one of the security feature called Dynamic Data Masking.

Whenever you access your account on your bank site, would you be comfortable in seeing your credit card or bank account number in clear text on the web page? There are multiple ways to do this at the application level, but as a human nature, it leaves room for error. One small mistake from the developer can leak sensitive data and can cost a huge loss. Wouldn’t it be great if a credit card number would be returned with only its last 4 digits visible – XXXX-XXXX-XXXX-1234 with no additional coding? Sounds interesting, read on!

Before experimenting this feature please remember that if you are using CTP2.0 then you need to turn on trace flags using below command.

DBCC TRACEON(209,219,-1)

If you don’t enable, then here is the error which you would receive while trying my sample script given later.

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ‘masked’.

Not to forget that this is SQL Server 2016 feature. Running the script on earlier versions of SQL would cause below

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ‘MASKED’.
Msg 319, Level 15, State 1, Line 14
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Here is the script to create objects (database, table, masked column).

Dynamic Data Masking

SET NOCOUNT ON
GO
-- drop database MaskingDemo - if already exists
USE [master]
GO
IF DB_ID('MaskingDemo') IS NOT NULL
BEGIN
ALTER DATABASE [MaskingDemo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [MaskingDemo]
END
-- create new database called MaskingDemo
CREATE DATABASE MaskingDemo
GO
USE MaskingDemo
GO
-- Create table with different data type columns
CREATE TABLE MyContacts (
ID INT IDENTITY(1, 1) PRIMARY KEY
,fName NVARCHAR(30) NOT NULL
,lName NVARCHAR(30) NOT NULL
,CreditCard VARCHAR(20) NULL
,SalaryINR INT NULL
,OfficeEmail NVARCHAR(60) NULL
,PersonalEmail NVARCHAR(60) NULL
,SomeDate DATETIME NULL
)
-- insert a row
INSERT INTO [dbo].[MyContacts]
([fName],[lName] ,[CreditCard],[SalaryINR],[OfficeEmail],[PersonalEmail], SomeDate)
VALUES('Pinal','Dave','1234-5678-1234-5678',999999,'pinal@SQLAuthority.com','pinal@foobar.in', '31-March-2013')
GO
-- apply masking
ALTER TABLE MyContacts
ALTER COLUMN CreditCard ADD MASKED
WITH (FUNCTION = 'partial(2,"XX-XXXX-XXXX-XX",2)')
ALTER TABLE MyContacts
ALTER COLUMN SalaryINR ADD MASKED
WITH (FUNCTION = 'default()')-- default on int
ALTER TABLE MyContacts
ALTER COLUMN SomeDate ADD MASKED
WITH (FUNCTION = 'default()')-- default on date
ALTER TABLE MyContacts
ALTER COLUMN fname ADD MASKED
WITH (FUNCTION = 'default()')-- default on varchar
ALTER TABLE MyContacts
ALTER COLUMN OfficeEmail ADD MASKED
WITH (FUNCTION = 'email()')
GO
-- create a new user and grant select permissions
USE MaskingDemo
GO
CREATE USER WhoAmI WITHOUT LOGIN;
GRANT SELECT ON MyContacts TO WhoAmI;

I have added additional comments in script so that it’s easy to understand. Below script would show the power of masking.

USE MaskingDemo
GO
SELECT * FROM MyContacts; -- this would show clear data
GO
EXECUTE AS USER = 'WhoAmI';
SELECT * FROM MyContacts -- this should show masked data
REVERT;
GO

As we can see above, those fields which are masked are showing obfuscated data based on masking rule.

Note: Versions after CTP2 release, the trace flag will not be needed. If you add trace flag, you would start getting “Incorrect syntax” error.

Reference: Pinal Dave (https://blog.sqlauthority.com)