Obfuscating data in SQL Server

Modified December 05, 2024

Tim Deschryver

timdeschryver.dev

Obfuscating data is a common practice to protect sensitive information, such as Personally identifiable information (PII). In this post, I'm going to show how you can obfuscate data in SQL Server.

You have different options to obfuscate data. Probably the easiest way developers do this, is by having a obfuscation layer right after the data is retrieved from the database, or when the data is returned to/from the UI layer.

However, this approach is not very secure, as developers can still access the original data by inspecting the data in the database directly. This is not what you want, especially when you are working with sensitive data.

A better solution is to obfuscate the data in the root, in the database itself.

Masking Update Script link

This approach makes use of a user-defined function to transform a value into a masked value, which replaces some (or all) characters with asterisks. You can use this function in an UPDATE statement to obfuscate selected columns containing sensitive data.

Only use this in development (and maybe testing) environments to prevent developers from accessing sensitive data. Do not use this in production, as it will permanently change the data.

For example, many systems I've worked on use, or have been using, datasets copied over from a production environment. While this makes your application easier to test because you can use real data with a representable load, it's also a security risk as it exposes sensitive data to development teams.

The following script, which is generated by GitHub Copilot, creates the mask function. For more variations and other techniques, you can also take a look at the Red Gate blog.

 content_paste
CREATE OR ALTER FUNCTION dbo.MaskData
(
    @inputValue NVARCHAR(MAX), -- The input value to be obfuscated
    @unmaskedLength INT = 2, -- The length of the unmasked portion
    @maskedPosition NVARCHAR(10) = 'middle' -- The position of the unmasked portion (start, end, middle, full)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @maskedValue NVARCHAR(MAX); -- Variable to store the masked value
    DECLARE @inputLength INT; -- Variable to store the length of the input value
    DECLARE @mask NVARCHAR(MAX); -- Variable to store the mask (asterisks)
    DECLARE @startUnmasked INT; -- Variable to store the start position of the unmasked portion
    DECLARE @endUnmasked INT; -- Variable to store the end position of the unmasked portion
 
    -- Get the length of the input value
    SET @inputLength = LEN(@inputValue);
 
    -- If the unmasked length is greater than or equal to the input length, obfuscate everything
    IF @unmaskedLength >= @inputLength
    BEGIN
        RETURN REPLICATE('*', @inputLength);
    END
 
    -- Create the mask with asterisks
    SET @mask = REPLICATE('*', @inputLength - @unmaskedLength);
 
    -- Handle different masked positions
    IF @maskedPosition = 'full'
    BEGIN
        -- Mask the entire input value
        SET @maskedValue = REPLICATE('*', @inputLength);
    END
    ELSE IF @maskedPosition = 'start'
    BEGIN
        -- Unmask the start portion and mask the rest
        SET @maskedValue = LEFT(@inputValue, @unmaskedLength) + RIGHT(@mask, @inputLength - @unmaskedLength);
    END
    ELSE IF @maskedPosition = 'end'
    BEGIN
        -- Mask the start portion and unmask the end
        SET @maskedValue = LEFT(@mask, @inputLength - @unmaskedLength) + RIGHT(@inputValue, @unmaskedLength);
    END
    ELSE IF @maskedPosition = 'middle'
    BEGIN
        -- Calculate the start and end positions for the unmasked portion
        SET @startUnmasked = (@inputLength - @unmaskedLength) / 2;
        SET @endUnmasked = @inputLength - @startUnmasked - @unmaskedLength - 1;
        -- Mask the middle portion and unmask the start and end
        SET @maskedValue = LEFT(@inputValue, @startUnmasked) + REPLICATE('*', @unmaskedLength) + RIGHT(@inputValue, @endUnmasked);
    END
    ELSE
    BEGIN
        -- If the masked position is not recognized, return the input value as is
        SET @maskedValue = @inputValue;
    END
 
    -- Return the masked value
    RETURN @maskedValue;
END

The function retrieves the value, the length of the unmasked portion, and the position of the unmasked portion. It then calculates the mask and the unmasked portion based on the input parameters and returns the masked value. Some examples of how to use the function are shown below.

If you know your database schema in-depth, you can use the dbo.MaskData function within UPDATE statements to obfuscate sensitive data. Otherwise, you will need to inspect the schema for sensitive columns. Luckily, you can use the following query to find columns containing a specific string, for example, 'SocialSecurityNumber'.

 content_paste
SELECT      c.name  AS 'ColumnName' ,(SCHEMA_NAME(t.schema_id) + '.' + t.name) AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%SocialSecurityNumber%'
ORDER BY TableName,ColumnName;

Based on the results, you can write UPDATE statements to mask the data.

 content_paste
UPDATE  dbo.Person
SET SocialSecurityNumber = dbo.MaskData(SocialSecurityNumber, default, default)
WHERE SocialSecurityNumber IS NOT NULL;

For specialized data structures, you may need to use a different masking function to handle the data correctly. For example, for email addresses, you may want to mask the username and keep the domain name. This results in a valid email address.

 content_paste
CREATE OR ALTER FUNCTION [dbo].[MaskEmail] (@email NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @unmaskedLength INT = 2;
    DECLARE @atPosition INT = CHARINDEX('@', @email);
    DECLARE @localPart NVARCHAR(MAX);
    DECLARE @domainPart NVARCHAR(MAX);
    DECLARE @maskedLocalPart NVARCHAR(MAX);
 
    IF @atPosition = 0
    BEGIN
        -- If there is no '@' symbol, return the input as is
        RETURN dbo.MaskData(@email, @unmaskedLength)
    END
 
    SET @localPart = LEFT(@email, @atPosition - 1)
    SET @maskedLocalPart = dbo.MaskData(@localPart, @unmaskedLength)
    SET @domainPart = SUBSTRING(@email, @atPosition, LEN(@email) - @atPosition + 1)
 
    RETURN @maskedLocalPart + @domainPart
END

Dynamic Data Masking link

Of course, you cannot use the above approach in production environments as it will erase the original data. Instead, you can use SQL Server's built-in feature called Dynamic Data Masking to prevent users from seeing PII data.

With Dynamic Data Masking, you can give access to sensitive data to users who need it, while masking the data for users who don't. This is useful to not only prevent developers from accessing sensitive data but also to prevent different user groups from seeing data they shouldn't. For example, the customer service should not see the financial data of a customer.

To add a mask to a column, you can define the mask while creating the column or alter the column to add a mask.

 content_paste
CREATE TABLE [dbo].[User] (
    [Name] NVARCHAR(255) NOT NULL,
    [Email] VARCHAR(100) MASKED WITH (FUNCTION = 'email()') NOT NULL,
    [Age] INT MASKED WITH (FUNCTION = 'random(1, 255)') NULL
);
 
GO
 
ALTER TABLE [dbo].[User]
ALTER COLUMN [Name] ADD MASKED WITH (FUNCTION = 'partial(2, "XX", 0)');

Now, when you query the table as an unauthorized user, you will see the masked data. The Name column is partially masked, showing only the first two characters, the rest is replaced with 'X'. The Email column is masked with the email function, which shows only the first letter and also replaces the domain with XXXX. The Age column is masked with the random function, which replaces the original value with a random number between 1 and 255.

 content_paste
Name    Email               Age
Alxxxxx aXXX@XXXX.com       42
Boxxxxx bXXX@XXXX.com       36

 content_paste
Name    Email               Age
Alice   alice@gmail.com     32
Bob     bob@outlook.com     28

By default, only the admin can query the original data. To give a user access to the masked data, you need to grant the UNMASK permission to the user. The permission can be finely specified from the column level to the entire database, you can give a user access to only the masked data of a specific column, a table, a schema, or the entire database.

 content_paste
- grant on column level
GRANT UNMASK ON dbo.[User]([Name]) TO User1;
 
- grant on table level
GRANT UNMASK ON dbo.[User] TO User1;
 
- grant on schema level
GRANT UNMASK ON SCHEMA::[dbo] TO User1;
 
- grant on database level
GRANT UNMASK TO User1;

Take a look at the official documentation for more information on how to use Dynamic Data Masking, such as more masking options or how to revoke access from a user.

Conclusion link

You have different options to obfuscate data.

While the most comfortable way, for us developers, is to have an obfuscation layer when the data is retrieved from the database, this doesn't fix the root. Doing this still keeps the original data in the database, which can be accessed by developers (or others) with database access.

As I've shown in this post, a better solution is to obfuscate the data in the database itself.

For development environments, you can update the original data with a masked value using the user-defined function MaskData, which I've shown in Masking Update Script. This is handy when the development database is a full or partial copy of the production database.

In production environments, I don't recommend updating the original data, as this is a permanent change that results in data loss (and unhappy users). Instead, you can use SQL Server's built-in feature called Dynamic Data Masking to prevent users from seeing sensitive data, as shown in this post. To give users access to the original data, you can grant the UNMASK permission to the user.

Feel free to update this blog post on GitHub, thanks in advance!

Support me

I appreciate it if you would support me if have you enjoyed this post and found it useful, thank you in advance.

Share this post