-1

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.

Example:-

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.

My queries:-

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.

closed as off-topic by yannis Oct 24 '16 at 11:27

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions asking for assistance in explaining, writing or debugging code are off-topic here. These can be asked on Stack Overflow if they include the desired behavior, a specific problem or error, and the shortest code necessary to reproduce it in the question (see Minimal, Complete, and Verifiable Example)." – yannis
If this question can be reworded to fit the rules in the help center, please edit the question.

0

a) Your script looks good to me. You want to just add authentication in this script, so only that user can access the key & certificate. Also some minor changes. Please change your key, do not post your actual key on forum or any type of websites.

CREATE MASTER KEY ENCRYPTION BY 
PASSWORD = '23987hxJKL#1946kcj$n44ncjhdlj'

CREATE CERTIFICATE Partner_Cert
   WITH SUBJECT = 'Certificate For Encryption',
   START_DATE = '1/1/2011', 
   EXPIRY_DATE = '1/1/2100'

CREATE SYMMETRIC KEY PARTNER_ENCR_KEY
    WITH ALGORITHM = TRIPLE_DES
    ENCRYPTION BY CERTIFICATE [Partner_Cert]


GRANT CONTROL ON SYMMETRIC KEY :: Encryptmy TO myUser;
GRANT CONTROL ON CERTIFICATE::Partner_Cert  TO myUser; 

b) Yes you need backup of each key & certificate. You never know when you lose it. Itb better idea to keep backup. You can keep same key for Dev and QA, I prefer separate key for production

c) I don't know.

d) You can also use the "TRIPLE_DES_3KEY". I think "TRIPLE_DES" is also good enough.

Not the answer you're looking for? Browse other questions tagged or ask your own question.