CODEWITHSUNDEEP
sqlsql-server-2008encryption
Rabindra
Rabindra

Reputation: 58

Encrypt column of existing tables in SQL Server 2008 r2

I need to encrypt columns of existing tables. I've tried updating with encrypted value but columns become blank. Is there a way so that I can see binary value in columns instead of blank.

I don't want to dump data to the temp table, encrypt it and dump back to original because I don't have more space in DB as it is historical DB.

Here is my Encryption and Decryption code.

  OPEN SYMMETRIC KEY PCI_Key DECRYPTION
   BY CERTIFICATE Cert
   GO
       UPDATE dbo.Table1
        SET column1=ENCRYPTBYKEY(KEY_GUID('PCI_Key'), column1)
       CLOSE  SYMMETRIC KEY PCI_Key

   OPEN SYMMETRIC KEY PCI_Key DECRYPTION
   BY CERTIFICATE Cert
   GO
      SELECT 
       CONVERT(VARCHAR,DEcryptByKey(Column1)) as column1 from dbo.table1
    CLOSE SYMMETRIC KEY PCI_Key

Upvotes: 2

Views: 556

Answers (1)

Rahul Neekhra
Rahul Neekhra

Reputation: 810

For encryption you have to have VARBINARY data type of column that where you can use SQL SERVER encryption feature.

Here is the step you can follow these steps if table existing else you can change directly change the datatype of column and insert\update the data.

For existing data follow these steps (If data is not huge in a table else approach will be different)

  1. Create new column in table with _1 column name with VARBINARY data type
  2. update the values of _1 new column using encryption from exciting values.
  3. Once done and verify then delete old column and rename new column with existing column name.

Cheers!

Upvotes: 3

Related Questions