To speed up process, we should use symmetric key – same key for encryption and decryption. When we add a new record, the key is used to encrypt the data before the record is physically added into data table; when we get the record, the same key is used to decrypt the data before it is returned to client program.
Here are the questions:
1. Where do you save the symmetric key? It is not a good idea to include plain text key in every related SQL statements or stored procedures – it is too easy to hack and too complex to change key!
Ok, I can put the symmetric key (key1) into a central table in SQL server database. To avoid hacking, I also encrypt key1 using another symmetric key (key2) – good :) But where should I put key2 then? The problem to save key2 is very similar to the initial problem to save key1.
2. When the encryption key is changed for security reason, we should decrypt the old data using the old key and then to encrypt all data using the new encryption key. Do we need track which column in which table is encrypted using which key? What will happen if somehow we forget to keep the tracking up-to-date?
Fortunately, SQL Server 2005 builds the encryption feature for us. :)
For question #1, SQL Server uses multiple levels of keys:
Symmetric key --encrypted by--> Database Master Key or certificate --encrypted by--> SQL Server Service Master Key --encrypted by--> Windows DPAPI and the service account credential or the machine key.
The benefit of multiple level keys is that you do not need to provide any password to decrypt a key. SQL Server can decrypt the key by itself. What you need is to use symmetric key name in your code.
Below is a sample from http://msdn2.microsoft.com/en-us/library/ms179331.aspx:
USE AdventureWorks;
GO
--If there is no master key, create one now.
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'
GO
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
USE [AdventureWorks];
GO
-- Create a column in which to store the encrypted data.
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO
-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS 'Encrypted ID Number',
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS 'Decrypted ID Number'
FROM HumanResources.Employee;
GO
To support server cluster, you can export Service Master Key, Database Master Key, symmetric key and certificate to another server.
For question #2, when a key is to be changed, you should use SQL command to change or recreate a key, so that SQL Server can decrypt old data and encrypt data using the new key automatically.
The encrypted data begins with symmetric key GUID. You can define several symmetric keys to encrypt different data columns. SQL Server can get proper key using the GUID part.
