0

I would like to check with you regarding dynamic data encryption/masking when reading 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 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 consistent

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.

Improve this question
3
  • You can't join on masked value because masking loses information. If John becomes XXXX and Mark becomes XXXX, the join between them will be very bad. Commented Apr 22, 2024 at 6:05
  • With that being said, i think dynamic data masking is the feature you want. Why not give it a spin? Note that it's not 100% secure because some clever tricks can be used to fish out the masked data Commented Apr 22, 2024 at 11:26
  • I need the masking values to be consistent across, so that cross - schema analysis can be obtained. Dynamic data masking option is giving consistent masking values. Hence it's ruled out from my option Commented Apr 23, 2024 at 4:59
Related questions
4342
How do I UPDATE from a SELECT in SQL Server?
3327
How to add a column with a default value to an existing table in SQL Server?
2112
LEFT JOIN vs. LEFT OUTER JOIN in SQL Server
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.