Encryption and Decryption within Azure SQL database

Question

Hey,
We have a requirement wherein we need to append a string character to every value in a column and then use hashbyte on the derived column values.
eg: column has value: 123

Requirement : hashbyte(sh1, 123abcdef)

Now we do not want the value abcdef exposed to anyone having read access on the database or within database DACPAC project.

Is there any way via which we can have this value stored in database like database scoped credentials and using the master key decrypt it during job execution via a specific account only ?

Is it possible to decrypt back the value?

Answer 1

@Nandan Hegde I believe you should be using a separate column to store the "string character" and then use Hashbyte(sh1, Col1 + Col2) to calculate the hash instead of concatenating the column value.
You can then use dynamic data masking to protect data in the column by configuring UNMASK permissions accordingly.
Other users can still have SELECT permissions on other columns in the table.

Please let me know if you have further questions.

----------

If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.