Recently, i got the task of doing some research into the data encryption at the cell level. I tried using the Symmetric key (by passphrase) & second option i took is Symmetric key (by Certificate). I need the encryption to be performed only on 3 columns inside the customer related data table.
ColumnX1 - Char(9) datatype ColumnX2 - Char(9) datatype ColumnX3 - Char(9) datatype
Three new columns:-
ColumnX1Crypt - varbinary(200) datatype ColumnX2Crypt - varbinary(200) datatype ColumnX3Crypt - varbinary(200) datatype
So far, it worked fine when i used the option of using the Certificates. But i have some open doubts and questions on using the certificates.
a) How do i perform the creation of a master key, certificate & the symmetric in various testing regions such as DEV, UAT, QA and finally to production ? If i prepare the sql script & ask the DBA's to run it in various regions, will it fine or i need to convey some additional information along with it. ? Please suggest.
b) Do i need to keep the backups of the master key, certificate & the symmetric key for every region such as DEV, UAT, QA etc. ? If i create a master key with the same password in all the regions. Will it be fine ? Will the backup files of the various objects taken in the Prod db work in lower regions db's ?
c) Are there any drawbacks of using the Certificate option with the Symmetric key?
d) Which encyption algo is best to choose.? In my case, the maximum data length is 9 characters for encryption.
Some Misc statements used in the MS SQL Server:-
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJKL#1946kcj$n44ncjhdlj' CREATE CERTIFICATE Partner_Cert WITH SUBJECT = 'Certificate For Encryption', Expiry_Date = '20180101'; CREATE SYMMETRIC KEY PARTNER_ENCR_KEY WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE [Partner_Cert]
Please suggest your views for the soln approach.