Column Level Encryption

• gshyam23 92006

  Mr or Mrs. 500

  Points: 550

  More actions

  October 20, 2016 at 9:09 am

  #314537

  Hello All,

  I am trying to learn about column level encryption as we are planning to implement in our environment, I tried to configure it in our lab environment and I was able to add encryption on couple of columns.

  However, I got a doubt about if we delete the Certificate/Symmetric key, what will be the case. I thought it will not allow to delete the certificate as encryption is enabled on columns already. After I deleted the Certificate & Symmetric key, I tried to create the certificate/symmetric key manually using the same scripts and tried to read encrypted data but it just shows NULL values. I tried restoring the Certificate using the backup and it is still giving NULL values.

  So in real time situation, if somebody deletes the Certificate accidentally, are we going to lose encrypted data? Please let me know if there is an option to restore the Certificate/symmetric key to retrieve the encrypted data.

  I appreciate your help.

  Thanks,

  Shyam

• Perry Whittle

  SSC Guru

  Points: 233900

  More actions

  October 20, 2016 at 9:49 am

  #1907701

  gshyam23 92006 (10/20/2016)

  ---

  Hello All,

  I am trying to learn about column level encryption as we are planning to implement in our environment, I tried to configure it in our lab environment and I was able to add encryption on couple of columns.

  However, I got a doubt about if we delete the Certificate/Symmetric key, what will be the case. I thought it will not allow to delete the certificate as encryption is enabled on columns already. After I deleted the Certificate & Symmetric key, I tried to create the certificate/symmetric key manually using the same scripts and tried to read encrypted data but it just shows NULL values. I tried restoring the Certificate using the backup and it is still giving NULL values.

  So in real time situation, if somebody deletes the Certificate accidentally, are we going to lose encrypted data? Please let me know if there is an option to restore the Certificate/symmetric key to retrieve the encrypted data.

  I appreciate your help.

  Thanks,

  Shyam

  If you lose the cert then access to the data is blocked.

  Restoring the cert from a backup should restore the access to the data.

  Note creating a new cert of the same name will not work as the thumbprint\signature will be different, you need the original cert

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉

• gshyam23 92006

  Mr or Mrs. 500

  Points: 550

  More actions

  October 20, 2016 at 10:04 am

  #1907708

  Hello,

  Thanks for your response. I tried restoring the certificate and I was still not able to read the data, not sure if some thing is wrong in our environment.

  -Shyam.

• Perry Whittle

  SSC Guru

  Points: 233900

  More actions

  October 20, 2016 at 10:27 am

  #1907714

  gshyam23 92006 (10/20/2016)

  ---

  Hello,

  Thanks for your response. I tried restoring the certificate and I was still not able to read the data, not sure if some thing is wrong in our environment.

  -Shyam.

  A backup of the original certificate?

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉

• gshyam23 92006

  Mr or Mrs. 500

  Points: 550

  More actions

  October 20, 2016 at 12:08 pm

  #1907744

  Yes, I restored the backup of original certificate and when I tried to read the data it gives Null values.