Data Masking : Azure and SQL Server

Jan 19, 20213 min read

What is Data Masking?

Data Masking is a way to create a fake data instead of displaying sensitive data. It is useful for Organization to hide there customer details, sales demos, or software testing.

Why Data Masking is Important?

Data masking solves several critical threats – data loss, data exfiltration, insider threats or account compromise, and insecure interfaces with third party systems.
Reduces data risks associated with cloud adoption.
Makes data useless to an attacker, while maintaining many of its inherent functional properties.
Allows sharing data with authorized users, such as testers and developers, without exposing production data.
Can be used for data sanitization – normal file deletion still leaves traces of data in storage media, while sanitization replaces the old values with masked ones.

Data Masking Types

There are several types of data masking types commonly used to secure sensitive data.

Static Data Masking

Static data masking refers to the process in which important data is masked in the original database environment. The content is duplicated into a test environment, and can then be shared around third-party vendors or other necessary parties.

Data is masked and extracted in the production database and moved into the test database. While this may be a necessary process for working with third-party consultants, it’s not ideal. That’s because throughout the process of masking data for a duplicate database, real data is extracted which can leave a backdoor open that encourages breaches.

Dynamic Data Masking

In dynamic data masking, automation and rules allow IT departments to secure data in real-time. That means it never leaves the production database, and as such is less susceptible to threats.

Data is never exposed to those who access the database because the contents are jumbled in real-time, making the contents inauthentic.

A resource called a dynamic masking tool finds and masks certain types of sensitive data using a reverse proxy. Only authorized users will be able to see the authentic data.

Concerns from dynamic data masking mostly stem from database performance. In an enterprise environment, time is money and even milliseconds have value. In addition to time considerations of running such a proxy, whether or not the proxy itself is secure can be a cause for concern.

On-the-fly data masking

Similar to dynamic data masking, on-the-fly data masking occurs on demand. In this type of data masking, an Extract Transform Load (ETL) process occurs where data is masked within the memory of a given database application. This is particularly useful for agile companies focused on continuous delivery.

Overall, your selection of a data masking strategy must take into consideration the size of the organization, as well as the location (cloud v. on premise) and complexity of the data you wish to protect.

For More Details visit this link.

Dynamic Data Masking in SQL server:

Step 1

Include Mask object in our sensitive data table. In below script I have create a table and include mask object with some functions. There are 4 dynamic mask functions (Default, Email, Random, Custom String). Visit this link for more details.

CREATE TABLE EMP_MASK(
EMP_ID INT PRIMARY KEY,
EMP_NAME VARCHAR(100),
MOBILE VARCHAR(20) MASKED WITH (FUNCTION = 'DEFAULT()'),
EMAIL VARCHAR(50) MASKED WITH (FUNCTION = 'EMAIL()')
);

Step 2

Now insert some data into table

INSERT INTO EMP_MASK VALUES(1,'Shamen','0766888399','shamen@gamil.com');
INSERT INTO EMP_MASK VALUES(2,'Madhu','0763488393','Madhu@gamil.com');
INSERT INTO EMP_MASK VALUES(3,'Max','076635394','Max@gamil.com');
INSERT INTO EMP_MASK VALUES(4,'Hilmy','0764888692','Hilmy@gamil.com');
INSERT INTO EMP_MASK VALUES(5,'Jhon','0766458392','Jhon@gamil.com');
INSERT INTO EMP_MASK VALUES(6,'Rush','0766348390','Rush@gamil.com');

Step 3

In admin view we can see original data but for other users data is encrypted