Industry guidance such as the Payment Card Industry Data Security Standard (PCI-DSS), Healthcare Insurance Portability and Accountability Act (HIPAA) and numerous state privacy breach notification laws require the use of encryption for sensitive data such as credit card numbers, security related data etc. To meet these compliance and regulatory requirements, SQL Server provides users the choice between transport-level encryption, cell-level encryption, full database-level encryption by using TDE, or the file-level encryption options provided by Windows. I’ve already discussed the database-level and transport-level encryption in my previous blog posts here and here respectively. In this blog post, I’m going to show you the implementation of cell-level encryption.

What is Cell-level encryption?

The Cell-level encryption is a series of built-ins and the key management hierarchy. This option follows a manual process that requires a redesign of the application to call the encryption and decryption functions. In addition, you also must modify the schema to store the data as varbinary and then recast back to appropriate data type when read.

The SQL Server encryption key hierarchy

Microsoft Reference: http://technet.microsoft.com/en-us/library/cc966395.aspx

Comparing the Cell-level encryption options

When encrypting data, you need to choose the most appropriate encryption options. The options available are described as follow:

• Passphrase – A password that can contain spaces. A passphrase is the least secure option. It requires you to use the same passphrase when encrypting and decrypting the data. If stored procedures and functions aren’t encrypted, the passphrase is accessible through metadata.
• Asymmetric key – Offers strong protection by using a different key to encrypt and decrypt the data. However, it provides poor performance and shouldn’t be used to encrypt large values. Can be signed by the database master key or created using a password.
• Symmetric key – Offers good performance and is strong enough for most requirements. Uses the same key to encrypt and decrypt the data.
• Certificate – Offers strong protection and good performance. Can be associated with a user. A certificate must be signed by the database master key.

Important Built-In functions for Cell-level encryption

Encryption

• ENCRYPTBYKEY
• ENCRYPTBYCERT
• ENCRYPTBYPASSPHRASE
• ENCRYPTBYASYMKEY

Decryption

• DECRYPTBYKEY
• DECRYPTBYCERT
• DECRYPTBYPASSPHRASE
• DECRYPTBYASYMKEY
• DECRYPTBYKEYAUTOASYMKEY
• DECRYPTBYKEYAUTOCERT

Demo: Cell-level Encryption

For the purpose of this demo, I’m going to use AdventureWork2012 database.

Create Database Master Key (DMK):

USE AdventureWorks2012
GO
-- Creates a database master key encrypted by password $Str0nGPa$$w0rd
CREATE MASTER KEY ENCRYPTION BY PASSWORD  = '$tr0nGPa$$w0rd' 
GO

 
Encrypting data using Symmetric Key encrypted by Asymmetric Key – The purpose of creating is the Asymmetric key is to encrypt our Symmetric Key.

Create Asymmetric Key:

USE AdventureWorks2012
GO
-- Creates an asymmetric key encrypted by password '$e1ectPa$$w0rd'
CREATE ASYMMETRIC KEY MyAsymmetricKey 
    WITH ALGORITHM = RSA_2048
    ENCRYPTION BY PASSWORD  = '$e1ectPa$$w0rd'
GO

Execute the query below, to view the information about asymmetric key:

USE [AdventureWorks2012]
GO
SELECT * FROM [sys].[asymmetric_keys] 
GO

Create Symmetric Key:

USE [AdventureWorks2012]
GO
-- Creates an symmetric key encrypted by asymmetric key
CREATE SYMMETRIC KEY MySymmetricKey
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY ASYMMETRIC KEY MyAsymmetricKey
GO

Execute the query below, to view the information about symmetric key:

USE [AdventureWorks2012]
GO
SELECT * FROM [sys].[symmetric_keys] 
GO

For demonstration purposes, I’m going to create a table called TestEncryption. This table has three columns Name, CreditCardNumber and EncryptedCreditCardNumnber. The EncryptedCreatedCardNumber stores the encrypted credit card number stored in CreditCardNumber column. Also insert some dummy data into this table for demonstration purposes. Execute the following code to create this table and insert dummy data:

USE [AdventureWorks2012]
GO
CREATE TABLE TestEncryption
([Name]                            [varchar] (256)
,[CreditCardNumber]                [varchar](16)
,[EncryptedCreditCardNumber]       [varbinary](max))
GO
INSERT INTO TestEncryption ([Name], [CreditCardNumber])
SELECT 'Simon Jones', '9876123456782378'
UNION ALL
SELECT 'Kim Brian', '1234567898765432'
GO
SELECT * FROM TestEncryption
GO

 
ResultSet:

USE [AdventureWorks2012]
GO
-- Opening the symmetric key
OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY ASYMMETRIC KEY MyAsymmetricKey 
WITH PASSWORD  = '$e1ectPa$$w0rd'
GO

Execute the following query returns the list of opened key:

USE [AdventureWorks2012]
GO
SELECT * FROM [sys].[openkeys]
GO

Now execute the following script update the TestEncryption table to insert the values in EncryptedCreditCardNumbers column from CreditCardNumbers column:

USE [AdventureWorks2012]
GO
--As you can see we are using ENCRYPTBYKEY function to encrypt the column values
UPDATE TestEncryption
SET [EncryptedCreditCardNumber] = ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'), CreditCardNumber)
GO

Once successfully executed, Verify the value inside EncryptedCreditCardNumber column by running the following query:

USE [AdventureWorks2012]
GO
SELECT * FROM [TestEncryption]
GO

 
ResultSet:

Executing the following query to retrieve the data inside EncryptedCreditCardNumber column using DECRYPTBYKEY encryption function:

USE [AdventureWorks2012]
GO
SELECT CONVERT([varchar](16), DECRYPTBYKEY([EncryptedCreditCardNumber]))
 FROM [TestEncryption]
GO

 
ResultSet:

Encrypting data using symmetric key encrypted using Passphrase – In this script, The data is encrypted using symmetric key using Phaseprase (see below):

USE [AdventureWorks2012]
GO
-- Creating symmetric key encrypted by password
CREATE SYMMETRIC KEY MySymmetricKeyPwd
    WITH ALGORITHM = AES_256    
    ENCRYPTION BY PASSWORD = 'RememberMe!' 
GO
-- Opening the symmetric key
OPEN SYMMETRIC KEY MySymmetricKeyPwd
DECRYPTION BY PASSWORD = 'RememberMe!' 
GO
-- Add EncryptedCreditCardNumber2 column in the Test encryption table. 
-- This column stores the data encrypted using key encrypted by Passphrase. 
ALTER TABLE TestEncryption
ADD [EncryptedCreditCardNumber2] [varbinary](max)
GO
--As you can see we are using ENCRYPTBYKEY function to encrypt the column values
UPDATE [TestEncryption]
SET [EncryptedCreditCardNumber2] = ENCRYPTBYKEY(KEY_GUID('MySymmetricKeyPwd'), CreditCardNumber)
GO
SELECT * FROM [TestEncryption]
GO

 
ResultSet:

Encrypting data using Certificate – You can also encrypt the data using Certificate as follow:

USE [AdventureWorks2012]
GO
-- Creates a certificate 
CREATE CERTIFICATE MyCertificate
   WITH SUBJECT = 'Demo Cert', 
   EXPIRY_DATE = '10/31/2050'
GO
-- Creating symmetric key encrypted by password
CREATE SYMMETRIC KEY MySymmetricKeyCert
    WITH ALGORITHM = AES_256    
    ENCRYPTION BY CERTIFICATE MyCertificate
GO
-- Opening the symmetric key
OPEN SYMMETRIC KEY MySymmetricKeyCert
DECRYPTION BY CERTIFICATE MyCertificate 
GO
-- Add two more columns in the Test encryption table. 
-- EncryptedCreditCardNumber3 column stores the data encrypted directly using certificate. 
-- EncryptedCreditCardNumber4 column stores the data encrypted key encrypted using certificate.
ALTER TABLE TestEncryption
ADD [EncryptedCreditCardNumber3] [varbinary](max),
    [EncryptedCreditCardNumber4] [varbinary](max)
GO
--You either encrypt the data directly using certificate
UPDATE [TestEncryption]
SET [EncryptedCreditCardNumber3] = ENCRYPTBYCERT(CERT_ID('MyCertificate'), CreditCardNumber)
GO
SELECT * FROM sys.certificates
--You either encrypt the data directly using certificate
UPDATE [TestEncryption]
SET [EncryptedCreditCardNumber4] = ENCRYPTBYKEY(KEY_GUID('MySymmetricKeyCert'), CreditCardNumber)
GO
-- Examine the encrypted columns
SELECT [EncryptedCreditCardNumber3] 
      ,[EncryptedCreditCardNumber4]
FROM [TestEncryption]
GO
-- Reading data by decrypting data of [EncryptedCreditCardNumber3] using DECRYPTBYCERT function
SELECT CONVERT([varchar](16), 
            DECRYPTBYCERT(CERT_ID('MyCertificate')
           ,[EncryptedCreditCardNumber3])) AS [CreditCardNumber]
 FROM [TestEncryption]
GO
-- Reading data by decrypting data of [EncryptedCreditCardNumber4] using DECRYPTBYKEY function
SELECT CONVERT([varchar](16)
            ,DECRYPTBYKEY([EncryptedCreditCardNumber4])) AS [CreditCardNumber]
 FROM [TestEncryption]
GO

 
ResultSet:

Benefits of using Cell-level encryption

• Cell-level encryption offers more granular level of encryption.
• Data is not decrypted until it is used so that even the page is loaded into memory, sensitive data is not in clear text.
• Key can be assigned to users and protect by password to prevent automatic decryption.

Drawbacks of using Cell-level encryption

• Administrator is burdened with maintaining the keys.
• For cell-level encryption, the performance impact on SQL Server optimization is 20% more than it is for Transparent Data Encryption (TDE).

Conclusion

Encryption is the process of hiding data using a key or password. This can make the data useless without the corresponding decryption key or password. Encryption does not solve the problems of access control. However, it improves security by limiting data loss even if access controls are omitted.

References

• SQL Server Optimization and the Performance Impact of Encryption
• Encrypting File System