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'
[ FORCE ]
More information on the syntax is available here.
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.
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:
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
ALTER TABLE Employee_symm
ADD EncryptedNationalIDNumber varbinary(128);
--- Lets create symmetric key with protection by password first
CREATE SYMMETRIC KEY symm_key_combo
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!';
--- Encrypt data
OPEN SYMMETRIC KEY symm_key_combo
DECRYPTION BY PASSWORD = 'Avc#ptNO$cf@o!'
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('symm_key_combo'), NationalIDNumber);
--- Now let’s drop symmetric key
DROP SYMMETRIC KEY symm_key_combo
--- Re-create symmetric key with protection by certificate.
--- Also let’s use different name for symmetric key
CREATE SYMMETRIC KEY symm_key_combo_new
-- Values here should be exactly the same
--- 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',
AS 'Decrypted ID Number'
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
Use the same values to re-create the symmetric key in future.
Hope this helps!
Posted By: Sergey Pustovit