I have several concept questions about encrypting data.
The task
My goal is to encrypt NVARCHAR(3800)
column but I want to encrypt only small percent of the data (less than 1% of it). As the table is referred in so many SQL objects, ASP files, .rdl files and SSIS projects I do not want to create separate table to hold these values there (at least for now I am looking for alternatives).
The plan
I am thinking for one of the following solution:
- Add
bit
column to the table calledisEncrypted
with default constraint0
andwith values
- Rename the current column to
_column_raw_data
- Add new column called
_column_encrypted_data
- Add new computed column with the original column name
_column
which will return the_column_raw_data
ifIsEncrypted
flag is0
, otherwise the_column_encrypted_data
- By default a query will return encrypted data – in order to decrypt it, additional logic will be implemented
or I am wondering is there any considerations to:
- Add
bit
column to the table calledisEncrypted
with default constraint0
andwith values
- When data is going to be encrypted the flag is raised and the output encrypted
varbinary
value will be set in the existing column
I am going to test the both variants of course but before doing that I am wondering is there another option or any issues in the above ones?