In the previous edition (Data security enhancements in SQL Server 2005) , we talked about data encryption/decryption in general.

 

In this installment, let’s talk about key maintenance. It is important to ensure that we can decrypt our data, even in case of user mistake or corruption.

 

For this, we need to protect our keys in our database. We have couple of options to do this.

 

Master key and certificate

You can use the following steps to backup Master key and Certificates

 

·         Backing Master key

 

          BACKUP MASTER KEY TO FILE = 'path_to_file'

      ENCRYPTION BY PASSWORD = 'password'

 

      RESTORE MASTER KEY FROM FILE = 'path_to_file'

      DECRYPTION BY PASSWORD = 'password'

      ENCRYPTION BY PASSWORD = 'password'

      [ FORCE ]

 

More information on the syntax is available here.

 

  • Backing Certificate 

      BACKUP CERTIFICATE certname TO FILE = 'path_to_file'

      [ WITH PRIVATE KEY

      (

        FILE = 'path_to_private_key_file' ,

        ENCRYPTION BY PASSWORD = 'encryption_password'

        [ , DECRYPTION BY PASSWORD = 'decryption_password' ]

      )

  ]

 

To restore a backed up certificate, use the CREATE CERTIFICATE statement. More information is available here.

 

Symmetric key

In order to maintain symmetric key properly and have the ability to restore it, you will have to use the same values for the following during key creation:

 

·         algorithm,

·         key_source,

·         identity_value

 

Key can be protected by password or certificate, but values above should exactly be the same. You may also use different name of symmetric key.

Let’s take an example to better understand this.

SELECT * INTO Employee_symm FROM AdventureWorks.HumanResources.Employee

GO

 

ALTER TABLE Employee_symm

    ADD EncryptedNationalIDNumber varbinary(128);

GO

 

 

--- Lets create symmetric key with protection by password first

CREATE SYMMETRIC KEY symm_key_combo

WITH

      ALGORITHM = triple_des,

      IDENTITY_VALUE = 'Example of encryption',

      KEY_SOURCE = 'Put here some unique and long enough phrase.'

ENCRYPTION BY PASSWORD = 'Avc#ptNO$cf@o!';

GO

 

 

--- Encrypt data

OPEN SYMMETRIC KEY symm_key_combo

DECRYPTION BY PASSWORD = 'Avc#ptNO$cf@o!'

GO

 

UPDATE Employee_symm

SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('symm_key_combo'), NationalIDNumber);

GO

 

 

--- Now let’s drop symmetric key

DROP SYMMETRIC KEY symm_key_combo

GO

 

 

--- Re-create symmetric key with protection by certificate.

--- Also let’s use different name for symmetric key

CREATE SYMMETRIC KEY symm_key_combo_new

WITH

      -- Values here should be exactly the same

      ALGORITHM = triple_des,

      IDENTITY_VALUE = 'Example of encryption',

      KEY_SOURCE = 'Put here some unique and long enough phrase.'

 

--- Protection can be done by certificate now though

ENCRYPTION BY CERTIFICATE HumanResources037;

 

 

--- Now let’s decrypt here using new symmetric key

OPEN SYMMETRIC KEY symm_key_combo_new

DECRYPTION BY CERTIFICATE HumanResources037;

 

SELECT NationalIDNumber, EncryptedNationalIDNumber 

    AS 'Encrypted ID Number',

    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))

    AS 'Decrypted ID Number'

FROM Employee_symm;

CLOSE SYMMETRIC KEY symm_key_combo_new

DROP SYMMETRIC KEY symm_key_combo_new

DROP TABLE Employee_symm

 

 

So in order to be able to restore symmetric key following 3 values need to be set

 

      ALGORITHM = triple_des,

      IDENTITY_VALUE = 'Example of encryption',

      KEY_SOURCE = 'Put here some unique and long enough phrase.'

 

Use the same values to re-create the symmetric key in future.

 

Hope this helps!

Posted By: Sergey Pustovit