As most of you know, the latest update from Microsoft about SQL Server 2016 was the release of CTP2.1 version. With this update, a long list of new features was introduced. In this blog post, we will discuss one of the security features called Dynamic Data Masking.

Imagine accessing your bank account on a website and seeing your credit card or bank account number in clear text on the web page. This can be a cause for concern as it leaves room for error. One small mistake from the developer can lead to a leak of sensitive data and potentially result in a huge loss. But what if the credit card number was returned with only its last 4 digits visible, like XXXX-XXXX-XXXX-1234, without any additional coding? This is where Dynamic Data Masking comes into play.

Dynamic Data Masking is a feature in SQL Server 2016 that allows you to obfuscate sensitive data in real-time, without modifying the actual data stored in the database. It provides an additional layer of security by controlling the visibility of sensitive data to different users or roles.

Let’s take a look at an example to understand how Dynamic Data Masking works. First, we need to create a database and a table:

CREATE DATABASE MaskingDemo;

USE MaskingDemo;

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
);

Now, let’s insert a row into the table:

INSERT INTO MyContacts
(fName, lName, CreditCard, SalaryINR, OfficeEmail, PersonalEmail, SomeDate)
VALUES('John', 'Doe', '1234-5678-1234-5678', 50000, 'john.doe@example.com', 'john.doe@gmail.com', '2020-01-01');

Next, we can apply masking to the columns that contain sensitive data:

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()');

ALTER TABLE MyContacts
ALTER COLUMN SomeDate ADD MASKED WITH (FUNCTION = 'default()');

ALTER TABLE MyContacts
ALTER COLUMN fName ADD MASKED WITH (FUNCTION = 'default()');

ALTER TABLE MyContacts
ALTER COLUMN OfficeEmail ADD MASKED WITH (FUNCTION = 'email()');

Now, let’s create a new user and grant them select permissions:

CREATE USER WhoAmI WITHOUT LOGIN;
GRANT SELECT ON MyContacts TO WhoAmI;

Now, if we query the table as the new user, we will see the masked data:

EXECUTE AS USER = 'WhoAmI';
SELECT * FROM MyContacts;
REVERT;

As we can see, the fields that are masked are showing obfuscated data based on the masking rule. This provides an added layer of security, ensuring that sensitive data is not exposed to unauthorized users.

It’s important to note that starting from versions after CTP2 release, the trace flag mentioned in the example will not be needed. If you add the trace flag, you will start getting an “Incorrect syntax” error.

Dynamic Data Masking is a powerful feature in SQL Server 2016 that helps protect sensitive data without the need for complex coding or application-level changes. It provides an additional layer of security by controlling the visibility of sensitive data to different users or roles. By implementing Dynamic Data Masking, organizations can minimize the risk of data breaches and ensure compliance with data privacy regulations.

Stay tuned for more articles on SQL Server 2016 and its exciting features!