Dynamic data masking with deterministic values

Aravindhan Ravichandran 20 Reputation points

I would like to check for data masking solution when reading data from a SQL Server database.

To elaborate further, the data elements should be encrypted/masked when a set of users querying the tables. The data stored at rest should be clear (i.e. unmasked/ not encrypted).

Also, the masking values should be consistent(deterministic) across the database. E.g. if a username john - masked/encrypted to xyz in schema 1 table a, schema 2 table b should have the same masked/encrypted value for the same user. So that we can join the cross-schema tables.

Key notes:

  1. Data at rest should be in clear form i.e. unchanged from source.
  2. Encryption/masking should be deterministic.

I have explored the SQL Server TDE & DDE options but doesn't suit my requirements.

Would like to hear from experts who handled such a requirement in a SQL Server database.

Thanks in advance!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,892 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 102.3K Reputation points

    You could also use the HASHBYTES function to produce your masked values.

1 additional answer

Sort by: Most helpful
  1. CosmogHong-MSFT 23,716 Reputation points Microsoft Vendor

    Hi @Aravindhan Ravichandran

    How about encrypting data by using a symmetric key.

    See these docs: ENCRYPTBYKEY (Transact-SQL); CREATE SYMMETRIC KEY (Transact-SQL)

    Best regards,

    Cosmog Hong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".