Introduction

In SQL Server 2016, Microsoft introduced a new feature called dynamic data masking, which allows you to mask the values of certain columns and keep that data hidden from certain users, without having to modify your applications.

Let's take a look at how SQL Server does data masking, and compare it to the way Gallium Data (a free database proxy) goes about it.

Bottom Line

• SQL Server data masking has the main advantage of being built-in
• Gallium Data has the main advantage of being much more flexible

Demo Database

For this article, we'll use the following trivial database:

create table demo.customers (
    id            int not null,
    first_name    nvarchar(100) not null,
    country       char(2) not null,
    primary key  (id)
)

insert into demo.customers values
(1, 'Andrea', 'AR'),
(2, 'Bruno', 'BE'),
(3, 'Charles', 'CA'),
(4, 'Daniella', 'DK'),
(5, 'Eric', 'ES')

Gallium Data takes a very different approach. It acts as a smart proxy between database clients and database servers and executes user-defined logic (expressed as filters and JavaScript code) that can modify that network traffic when appropriate. For data masking, Gallium Data has two options: modify the SQL command on its way to SQL Server, or modify the result set coming back from SQL Server.

Request Filter: Rewrite the SQL Command

A simple request filter in Gallium Data can catch the statement:

select id, first_name, country 
from demo.customers where first_name like '?'

before it gets to SQL Server and rewrite it to:

select id, substring(first_name, 1, 1) + 'XXXX' as first_name, country
from demo.customers where first_name like '?'

This works well if you know in advance what SQL commands to expect.

You can of course be as fancy as you want in how you rewrite the query, for instance:

select id, 
case
    when country in ('ES', 'DK', 'BE') then substring(first_name, 1, 1) + '€€€€'
    when country in ('US', 'CA') then substring(first_name, 1, 1) + '$$$$'
    else first_name
end as first_name,
country 
from demo.customers
where first_name like '?'

This will make the mask depend on the country column. Note that this rewriting can depend on who the user is, the time of day, or whatever conditions are relevant.

Response Filter: Change the Result Set

The other (sometimes complementary) option is to change the result set as it comes back from SQL Server. This is more expensive because we'll potentially execute code for every row in the result set, but modern computers are amazingly fast.

The JavaScript code for the response filter might look something like this:

let pkt = context.packet;
if (pkt.country == 'BE' || pkt.country == 'DK' || pkt.country == 'ES') {
    pkt.first_name = pkt.first_name.substring(0, 1) + "€€€€";
}
else if (pkt.country == 'US' || pkt.country == 'CA') {
    pkt.first_name = pkt.first_name.substring(0, 1) + "$$$$";
}

If you can do what you need by modifying the query, that's usually preferable, but having complete freedom over how or whether to mask the data on a row-by-row basis gives you an unparalleled level of control.

• the masking is completely up to you, whereas SQL Server can only use one of 4 pre-built functions
• masking can depend on the value of any columns, whereas SQL Server only supports one mask for all rows
• masking can be different depending on the user, the user's IP address, etc..., whereas SQL Server only supports one mask for all users
• masking can be done on all columns, including computed columns and stored procedures, whereas SQL Server can only mask stored columns
• masking/unmasking can be done with surgical precision, whereas SQL Server's masking is all-or-nothing per user