2

I was implemented the Always Encrypted and Dynamic data masking concepts in my azure SQL database on two different tables.

But I have doubt like “Is it possible to apply the dynamic data masking on already encrypted column in same table”.

I tried the above scenario it gives error like “The data type of column 'SSN' does not support data masking function 'partial'.”.

I run the below query for applying the masking on already encrypted column.

ALTER TABLE [dbo].[CustomerTables]
ALTER COLUMN [SSN] ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)');

Is it possible to apply the dynamic data masking on already encrypted column in same table?

halfer
  • 19,824
  • 17
  • 99
  • 186
Pradeep
  • 5,101
  • 14
  • 68
  • 140
  • Do you found a way to mask the data encrypted data in the application? – gotqn Feb 14 '18 at 08:58
  • No @gotqn, I didn't find anyway to mask the data encrypted. – Pradeep Feb 14 '18 at 09:56
  • There might be a way to do this in the application. To check the certificates access or based on the returned type value. I will examine this and let you know if I find something. – gotqn Feb 14 '18 at 09:57

1 Answers1

3

No, currently encrypted columns cannot be masked. And, you cannot encrypt a column that has been masked.

With Dynamic Data Masking, masked values are produced on the server side. To produce a masked value (especially using a partial mask), SQL Server needs to know the original value (in plaintext). If a column is encrypted with Always Encrypted, SQL Server only knows ciphertext and it cannot decrypt it - only a client application can decrypt the values stored in encrypted columns.

Jakub Szymaszek - Microsoft
  • 586
  • 3
  • 3