PART II - Data security enhancements in SQL Server 2005

  • Article

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;