In SQL Server 2016, Microsoft introduced a new feature called dynamic data masking. This feature allows you to mask the values of certain columns and keep that data hidden from certain users, without having to modify your applications. However, there are some limitations to SQL Server’s data masking capabilities.

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.

SQL Server Dynamic Data Masking

With SQL Server, you can define a dynamic mask for a column using the following syntax:

ALTER TABLE demo.customers
ALTER COLUMN first_name
ADD MASKED WITH (FUNCTION = 'partial(1, "XXXXXXX", 0)')

This will partially hide the value of the “first_name” column. Any user (other than the owner of the table and DBAs) will see the data as masked.

However, SQL Server’s data masking has some limitations. You can only use one of the four built-in functions for masking, and you cannot use your own functions. Additionally, if users can create their own SQL queries, they can easily bypass data masking.

Gallium Data Masking

Gallium Data takes a different approach to data masking. It acts as a smart proxy between database clients and database servers and executes user-defined logic that can modify network traffic when appropriate.

Gallium Data offers two options for data masking:

1. Request Filter: Rewrite the SQL Command

A request filter in Gallium Data can catch the SQL command before it gets to SQL Server and rewrite it to apply the desired masking. This works well if you know in advance what SQL commands to expect.

2. Response Filter: Change the Result Set

The other option is to change the result set as it comes back from SQL Server. This is more expensive in terms of performance, but it gives you complete freedom over how or whether to mask the data on a row-by-row basis.

Comparison

SQL Server dynamic masking has the advantage of being built-in and having excellent performance. However, it has limitations in terms of flexibility and customization.

Gallium Data masking, on the other hand, offers more flexibility and control. You can define masking rules that depend on the value of any columns, the user, the user’s IP address, and more. Masking can be done on all columns, including computed columns and stored procedures. Additionally, masking/unmasking can be done with surgical precision.

Both approaches have their merits, and they are not exclusive. It is possible to use both SQL Server data masking and Gallium Data masking to implement different aspects of the requirements.

Conclusion

In conclusion, SQL Server data masking is a simple mechanism that is suitable for many applications that require basic masking. Gallium Data masking, on the other hand, provides more flexibility and may be preferable for complex cases where the masking requirements are not straightforward.

Ultimately, the choice between SQL Server data masking and Gallium Data masking depends on the specific needs of your application and the level of control you require over the masking process.

If you’re interested in learning more about Gallium Data, you can explore how it can help with row-level security, work with SQL Server’s data classification feature to restrict data access, or easily restrict queries based on prior behavior.