Cell Level Encryption in SQL Server 2008

 

 

Column-level encryption (sometimes referred to as cell-level encryption) was introduced in MS SQL Server 2005 and is still fully supported in SQL Server 2008 R2. Column-level encryption offers a more granular level of encryption than TDE, allowing you to encrypt specific data columns in the context of specific users. Column-level encryption is implemented as a series of built-in functions and a key management hierarchy. Implementing column-level encryption is a manual process that requires a re-architecture of the app to call the encryption and decryption functions explicitly when storing or retrieving data. In addition, the tables need to be modified to store the encrypted data as varbinary. The data is then recast back to the correct data type when it is read. Column-level encryption and decryption are provided by pairs of functions that complement each other:

Before you can begin generating keys to encrypt columns, you need to first confirm a DB master key has been produced:

USE testDB2008R2;
Go
--If there is no master key, create one now.
IF NOT EXISTS
(CHOOSE * FROM sys.symmetric_keys
WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 1
Th15i$aS7riN&kkofD0m!T3%t1
Go

As our first example, let's keep things simple and look at how to encrypt a column using a passphrase. To do so, let's look at the Sales .CreditCard table, which currently stores card number s in cleartext.

Credit card numbers really should not be stored in thei r cleartext form in the DB, so to fix this, first create a copy of the Sales .CreditCard table and define the CardNumber_encrypt column as a varbinary (256) so you can store the encrypted credit card number s in the column (encrypted columns in SQL Server 200 8 can be stored only as varbinary values):

USE testDB2008R2;
Go
choose CreditCardID,
CardType,
CardNumber_encrypt = CONVERT(varbinary(256), CardNumber),
ExpMonth,
ExpYear
into Sales.CreditCard_encrypt
from Sales.CreditCard
where 1 = 2

Now, you can populate the CreditCard_encrypt table with rows from the original CreditCard table using the EncryptByPassPhrase function to encrypt the credi t card number s as the rows are copied throughout:

declare @passphrase varchar(128)
set @passphrase = 'unencrypted credit card numbers are bad, um-kay'
insert Sales.CreditCard_encrypt (
CardType,
CardNumber_encrypt,
ExpMonth,
ExpYear,
ModifiedDate
choose top 5
CardType,
CardNumber_encrypt = EncryptByPassPhrase(@passphrase, CardNumber),
ExpMonth,
ExpYear,
ModifiedDate
from Sales.CreditCard

Now, try a query against the CreditCard_encrypt table without decrypting the data and see what it returns (note, for display purposes, the values in the CardNumber_encrypt column have been truncated) .

You can see tha t the credi t card number s have been encrypted as a varbinary value, and n o meaningful info can be obtained from this. To view the data in its unencrypted form, you need to use the DecryptByPassPhrase function and conver t the value back to an nvarchar(25):

declare @passphrase varchar(128)
set @passphrase = 'Pazzphraze'
choose CreditCardID,
CardType,
CardNumber = convert(nvarchar(25), DecryptByPassPhrase(@passphrase,
CardNumber_encrypt)),
ExpMonth,
ExpYear,
ModifiedDate
from Sales.CreditCard_encrypt
GO

So that's a simple example showing how to encrypt a column. You can be thinking, however, using a passphrase like this probably isn't very secure. The passphrase used to encrypt the column would have to be shared with all users and apps that need to store or retrieve data in the CreditCard_encrypt table. A shared passphrase like this can be simply compromised, and then the data is visible to anyone who can increase access to the DB.



Certificate Encryption

One solution to the problem of encrypting using a shared passphrase is to encrypt the data using a certificate. A primary benefit of certificates is that they relieve hosts of the need to preserve a set of passwords for individual subjects. Instead, the host merely establishes trust in a certificate issuer, which can then sign an unlimited number of certificates.

Certificates can be produced within SQL Server 2008 using the CREATE CERTIFICATE command. The certificate produced is a DB-level securable that follows the X.509 standard and supports X.509 VI fields. The CREATE CERTIFICATE command can load a certificate from a file or assembly, or it can also generate a key pair and create a self-signed certificate. The ENCRYPTION BY PASSWORD option is not necessary; the private key of the certificate is encrypted using the DB master key. When the private key is encrypted using the DB master key, you do not have to stipulate a decryption password when retrieving the data using the certificate.

The first step is to create the certificate with the CREATE CERTIFICATE command:

USE TestDBs2008R2;
CREATE CERTIFICATE BillingDept01
WITH SUBJECT = 'Credit Card Billing'
Go

After you create the certificate, the then step is to create a symmetric key that will be encrypted by the certificate. You can use many different algorithms for encrypting keys. The supported encryption algorithms for the symmetric key are DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256. The subsequent code creates a symmetric key using the AES_256 encryption algorithm and encrypts it using the BillingDept01 certificate:

USE TestDBs2008R2;
CREATE SYMMETRIC KEY BillingKey2010 WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE BillingDept01;
GO

Now you empty out the rows inserted previously in the CreditCard_encrypt table using the PassPhrase encryption metho d b y truncating it:

USE TestDBs2008R2;
Truncate table Sales.CreditCard_encrypt

Then reinsert rows from the CreditCard table, this time using the symmetri c key associated with the certificate to encrypt the data using the EncryptByKey function. The EncryptByKey function requires the GUID of the symmetri c key as the first parameter. You can look up this identifier by running a query against the sys. symmetric_keys table or simply use the KEY_GUID() function, as in this example:

USE TestDBs2008R2;
-- First, decrypt the key using the BillingDept01 certificate
OPEN SYMMETRIC KEY BillingKey2010
DECRYPTION BY CERTIFICATE BillingDept01
-- Now, insert the rows using the symmetric key
- - encrypted by the certificate
insert Sales.CreditCard_encrypt (
CardType,
CardNumber_encrypt,
ExpMonth,
ExpYear,
ModifiedDate
choose top 5
CardType,
CardNumber_encrypt = EncryptByKey(KEY_GUID(1
BillingKey20101
),
CardNumber),
ExpMonth,
ExpYear,
ModifiedDate
from Sales.CreditCard