This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(256, 52%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAR%3C/text%3E%3C/svg%3E)
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:
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!
What is the underlying business requirement here? Masking and encryption are somewhat related, but still quite different. So it could help if you could clarify from a business perspective what the requirement is.
Hi @Erland Sommarskog ,
The need is to mask the data with deterministic value while reading the data from tables.
The masking values should be consistent across schemas for analytical use cases.
You are not answering the question. You are only repeating what you said initially. I was asking for the underlying reason why you want to do this. With a better understand of the actual requirements, we may be able of thinking of different ways to go.
At a minimum, you could at least give an example of what you want to achieve.
Our requirement is to obfuscate the PII fields for specific set of users across the database with same masking values. The intention of deterministic masking is to facilitate the analytical needs across the databases or schemas
You could also use the HASHBYTES function to produce your masked values.
Hi Erland, Yes , I have used the hash bytes function and it gives me the consistent masking values.
Now my concern is on managing the user access.
e.g. I have 100 users in total, out of 100 users, the data masking to be applied for 10 users. Initially my thought process was to use case statement to manage this.
---CASE WHEN SYSTEM_USER = 'specific_user' THEN
CONVERT(VARCHAR(MAX), HASHBYTES('SHA2_256', [PERSON_ID_EXTERNAL]), 2)
--ELSE [PERSON_ID_EXTERNAL]
--END
AS EncryptedStaffName
this will increase the workload and can get messy if we have more users comes in the future. Any alternate approach for managing the access control?
I assume you have a user table. In this table you would have a flag to tell whether this users gets the masked or unmasked version.
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".
Hi @CosmogHong-MSFT ,
Thanks for your time.
I have tried with that feature by creating symmetric with a password and created a view with the encrypted column.
By while running the select statement, the views show null values for the encrypted column.
Any issue with key created?
If you go the encryption path, you would not try to decrypt the value as that would defeat the purpose. Rather you would display the binary string.
Hi Erland, I need not decrypt the values. Our intention is just to mask the critical fields at the same time masked values should be consistent across for cross schema analytical use cases.