How to mask data in SQL Server

ES: Cómo enmascarar datos en SQL Server

Protect our information is mandatory, specially for us who work with database servers. We talked before about what a DBA work means and security is not apart of this job. Masking is a good option to protect sensitive information and that’s why now we’ll see how to mask data in SQL Server.

Before we begin

I’ve seen that many times concepts are misunderstood and we need to be aware of the differences.

Encrypting != Masking

Ok?

So, how it works?

Masking in SQL Server can be very helpful and we can make this work through a technology called Dynamic Data Masking.

Then we can limit data exposure to non privileged users.

Where do we apply Data Masking?

We can think about some scenarios:

• Dev and Test environments.
• Call centers and related applications with limited access to data.
• Some DBAs with limited access.
• Database Analysts with limited access.
• External stuff working with our data

Now let’s move to the code

You’ll see that in SQL Server is just as easy as adding some code while we create a table which we want to mask. You do this work with the columns you know they have sensitive data.

There are four basic masking functions. But you want to see the code right?

--	Create table appliying masking functions
CREATE TABLE UserInfo
(id int identity(1,1),
FirstName varchar(15),
LastName varchar(15) MASKED WITH (FUNCTION = 'default()'),
CreditCard varchar (25) MASKED WITH (FUNCTION = 'partial(4,"XXXXXXX",0)'),
Email varchar(25) MASKED WITH (FUNCTION = 'email()'),
DocNumber bigint MASKED WITH (FUNCTION = 'random(111111, 999999)')
)

INSERT INTO UserInfo 
(FirstName, LastName, CreditCard, Email, DocNumber) 
VALUES
('Pablo', 'Fernandez', '4940-9898-8989-1234','pablof@datoptim.com',123581321)


SELECT *
FROM UserInfo;


--	Run simulating a different user connection
--	nonAdminUser only have datareader role
EXECUTE AS USER = 'nonAdminUser';  
SELECT * 
FROM UserInfo;  
REVERT;  

And the magic is done.

You don’t need more code or more complications. The user called as ‘nonAdminUser‘ see the data changed with this masking layer or with random data (depending on the function you applied) using the same SELECT query within the table.

Mask data in SQL Server Functions

Funtion	Description
Default	We depend on the data type of the column. WIth text types it's used 'XXXX', numerics use '0' and dates use '01.01.1900 00:00:00.0000000'
Email	It will be shown only the first character of email and it generalized the @.com
Random	It's design for numeric random values with range defined by random([start], [end])
Custom	It will be shown the first and the last characters masking what's in between according with the function partial(prefix,[mask],sufix)

Last considerations

Don’t ever forget that to mask data in SQL doesn’t provide 100% protection to your information. Literally you are using a mask for the data, the content itself is still saved as it is.

What do we protect? It’s important you Find Sensitive Data in SQL Server first and then tag them appropriately.

If you are protecting information, maybe you also want to save a registry about who access to sensitive data and monitor it too.

If you want to know more about permissions and best practices, you can take a look at the official documentation.

You want to know what about Azure? Take a look at How to mask data in Azure SQL Database.