Programming4us
         
 
 
SQL Server

SQL Server 2008 : Data Encryption - Column-Level Encryption

5/19/2011 4:48:06 PM
Column-level encryption (sometimes referred to as cell-level encryption) was introduced in Microsoft 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 application to call the encryption and decryption functions explicitly when storing or retrieving data. In addition, the tables must be modified to store the encrypted data as varbinary. The data is then recast back to the appropriate data type when it is read.

Column-level encryption and decryption are provided by pairs of functions that complement each other:

  • EncryptByCert() and DecryptByCert()— Encrypts and decrypts data using the public key of a certificate to generate a private asymmetric key

  • EncryptByAsymKey() and DecryptByAsymKey()— Encrypts and decrypts data using an asymmetric key

  • EncryptByKey() and DecryptByKey()— Encrypts and decrypts data by using a symmetric key

  • EncryptByPassphrase() and DecryptByPassphrase()— Encrypts and decrypts data by using a passphrase to generate a symmetric key

Before you can begin generating keys to encrypt columns, you must first make sure a database master key has been created:

USE AdventureWorks2008R2;
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 = 'Th15i$aS7riN&ofR@nD0m!T3%t'
GO


Encrypting Columns Using a Passphrase

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 numbers in cleartext:

select top 5 * from Sales.CreditCard
go

CreditCardID CardType CardNumber ExpMonth ExpYear ModifiedDate
------------ -------------- -------------- -------- ------- -----------
1 SuperiorCard 33332664695310 11 2006 2007-08-30
2 Distinguish 55552127249722 8 2005 2008-01-06
3 ColonialVoice 77778344838353 7 2005 2008-02-15
4 ColonialVoice 77774915718248 7 2006 2007-06-21
5 Vista 11114404600042 4 2005 2007-03-05

Credit card numbers really should not be stored in their cleartext form in the database, 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 numbers in the column (encrypted columns in SQL Server 2008 can be stored only as varbinary values):

USE AdventureWorks2008R2;
GO

select CreditCardID,
CardType,
CardNumber_encrypt = CONVERT(varbinary(256), CardNumber),
ExpMonth,
ExpYear,
ModifiedDate
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 credit card numbers as the rows are copied over:

declare @passphrase varchar(128)
set @passphrase = 'unencrypted credit card numbers are bad, um-kay'
insert Sales.CreditCard_encrypt (
CardType,
CardNumber_encrypt,
ExpMonth,
ExpYear,
ModifiedDate
)
select 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):

select * from Sales.CreditCard_encrypt
go

CreditCardID CardType CardNumber_encrypt ExpMonth ExpYear ModifiedDate
------------ ------------- --------------------- -------- ------- -----------
1 SuperiorCard 0x010000007C65089E... 11 2006 2007-08-30
2 Distinguish 0x010000000C624987... 8 2005 2008-01-06
3 ColonialVoice 0x01000000AA8761A0... 7 2005 2008-02-15
4 ColonialVoice 0x010000002C2857CC... 7 2006 2007-06-21
5 Vista 0x0100000095F6730D... 4 2005 2007-03-05



In the preceding results, you can see that the credit card numbers have been encrypted as a varbinary value, and no meaningful information can be obtained from this. To view the data in its unencrypted form, you need to use the DecryptByPassPhrase function and convert the value back to an nvarchar(25):

declare @passphrase varchar(128)
set @passphrase = 'unencrypted credit card numbers are bad, um-kay'
select CreditCardID,
CardType,
CardNumber = convert(nvarchar(25), DecryptByPassPhrase(@passphrase,
CardNumber_encrypt)),
ExpMonth,
ExpYear,
ModifiedDate
from Sales.CreditCard_encrypt
GO
CreditCardID CardType CardNumber ExpMonth ExpYear ModifiedDate
------------ -------------- -------------- -------- ---------------------
1 SuperiorCard 33332664695310 11 2006 2007-08-30
2 Distinguish 55552127249722 8 2005 2008-01-06
3 ColonialVoice 77778344838353 7 2005 2008-02-15
4 ColonialVoice 77774915718248 7 2006 2007-06-21
5 Vista 11114404600042 4 2005 2007-03-05

So that’s a simple example showing how to encrypt a column. You may 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 applications that need to store or retrieve data in the CreditCard_encrypt table. A shared passphrase like this can be easily compromised, and then the data is visible to anyone who can gain access to the database. It is usually more secure to encrypt data using a symmetric key or certificate.

Encrypting Columns Using a Certificate

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 maintain a set of passwords for individual subjects. Instead, the host merely establishes trust in a certificate issuer, which may then sign an unlimited number of certificates.

Certificates can be created within SQL Server 2008 using the CREATE CERTIFICATE command. The certificate created is a database-level securable that follows the X.509 standard and supports X.509 V1 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 required; the private key of the certificate is encrypted using the database master key. When the private key is encrypted using the database master key, you do not have to specify a decryption password when retrieving the data using the certificate.

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

USE AdventureWorks2008R2;
CREATE CERTIFICATE BillingDept01
WITH SUBJECT = 'Credit Card Billing'
GO

After you create the certificate, the next 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 following code creates a symmetric key using the AES_256 encryption algorithm and encrypts it using the BillingDept01 certificate:

USE AdventureWorks2008R2;
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 method by truncating it:

USE AdventureWorks2008R2;
Truncate table Sales.CreditCard_encrypt

Next reinsert rows from the CreditCard table, this time using the symmetric key associated with the certificate to encrypt the data using the EncryptByKey function. The EncryptByKey function requires the GUID of the symmetric 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 AdventureWorks2008R2;
-- 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
)
select top 5
CardType,
CardNumber_encrypt = EncryptByKey(KEY_GUID('BillingKey2010'),
CardNumber),
ExpMonth,
ExpYear,
ModifiedDate
from Sales.CreditCard

If you examine the contents of the CreditCard_encrypt table, you can see that they have been encrypted:

select * from Sales.CreditCard_encrypt
go

CreditCardID CardType CardNumber_encrypt ExpMonth ExpYear ModifiedDate
------------ ------------- --------------------- -------- ------- -----------
1 SuperiorCard 0x0046C380E7A27749... 11 2006 2007-08-30
2 Distinguish 0x0046C380E7A27749... 8 2005 2008-01-06
3 ColonialVoice 0x0046C380E7A27749... 7 2005 2008-02-15
4 ColonialVoice 0x0046C380E7A27749... 7 2006 2007-06-21
5 Vista 0x0046C380E7A27749... 4 2005 2007-03-05



Now, an authorized user that specifies the appropriate certificate can retrieve the data by using DecryptByKey function:

USE AdventureWorks2008R2;
OPEN SYMMETRIC KEY BillingKey2010
DECRYPTION BY CERTIFICATE BillingDept01
select CardType,
CardNumber = convert(nvarchar(25), DecryptByKey(CardNumber_encrypt)),
ExpMonth,
ExpYear,
ModifiedDate
from Sales.CreditCard_encrypt
go

CreditCardID CardType CardNumber ExpMonth ExpYear ModifiedDate
------------ -------------- -------------- -------- ------- -----------
1 SuperiorCard 33332664695310 11 2006 2007-08-30
2 Distinguish 55552127249722 8 2005 2008-01-06
3 ColonialVoice 77778344838353 7 2005 2008-02-15
4 ColonialVoice 77774915718248 7 2006 2007-06-21
5 Vista 11114404600042 4 2005 2007-03-05

When you are done using a key, it is good practice to close the key using the CLOSE SYMMETRIC KEY statement:

CLOSE SYMMETRIC KEY BillingKey2010

The keys defined in a database can be viewed through the system catalog table, sys.symmetric_keys:

select name,
pvt_key_encryption_type,
issuer_name,
subject,
expiry_date = CAST(expiry_date as DATE),
start_date = CAST(start_date as DATE)
from sys.certificates
go

name pvt_key_encryption_type issuer_name
subject expiry_date start_date
------------- --------------------- -------------------
------------------- ----------- ----------
BillingDept01 MK Credit Card Billing
Credit Card Billing 2011-05-01 2010-05-01

The certificates defined in a database can be viewed through the system catalog tables, sys.certificates:

select name,
key_length,
key_algorithm,
algorithm_desc,
create_date = CAST(create_date as DATE),
modify_date = CAST(create_date as DATE),
key_guid
from sys.symmetric_keys
go

name key_length key_algorithm algorithm_desc
create_date modify_date key_guid
------------------------ ----------- ------------- --------------
----------- ----------- ------------------------------------
##MS_DatabaseMasterKey## 128 D3 TRIPLE_DES
2010-04-30 2010-04-30 A3550B00-6BAE-41E2-A1BC-D784DC35779E
BillingKey2010 256 A3 AES_256
2010-04-30 2010-04-30 10C5C800-0B4C-44C2-9F71-5415007C2E81

If the usage of the key and certificate are no longer needed, they should be dropped from the database:

DROP SYMMETRIC KEY BillingKey2010
DROP CERTIFICATE BillingDept01
Other -----------------
- SQL Server 2008 : Data Encryption - SQL Server Key Management
- SQL Server 2008 : Data Encryption
- SQL Server 2008 : Client Data Access Technologies
- SQL Server 2008 : Client Configuration
- SQL Server 2008 R2 : Client Installation
- SQL Server 2008 R2 : Client and Server Networking Considerations
- Upgrading to SQL Server 2008 : Upgrading Other SQL Server Components
- Upgrading to SQL Server 2008 : Slipstreaming Upgrades
- Upgrading to SQL Server 2008 : Upgrading Using a Configuration File
- Destination: SQL Server 2008 or SQL Server 2008 R2 (part 2) - Upgrading In-Place
- Destination: SQL Server 2008 or SQL Server 2008 R2 (part 1) - Side-by-Side Migration
- Upgrading to SQL Server 2008 : Using the SQL Server Upgrade Advisor (UA)
- SQL Server 2008 : Developing Custom Managed Database Objects (part 7) - Using Transactions & Using the Related System Catalogs
- SQL Server 2008 : Developing Custom Managed Database Objects (part 6) - Developing Managed Triggers
- SQL Server 2008 : Developing Custom Managed Database Objects (part 5) - Developing Managed User-Defined Aggregates
- SQL Server 2008 : Developing Custom Managed Database Objects (part 4) - Developing Managed User-Defined Types
- SQL Server 2008 : Developing Custom Managed Database Objects (part 3) - Developing Managed User-Defined Functions
- SQL Server 2008 : Developing Custom Managed Database Objects (part 2) - Developing Managed Stored Procedures
- SQL Server 2008 : Developing Custom Managed Database Objects (part 1)
- SQL Server 2008 : Profiler Usage Scenarios (part 2)
 
 
Most View
- ASP.NET 4 in VB 2010 : Site Maps (part 1) - Defining a Site Map
- Making Your Site Accessible to Search Engines
- Exchange 2007: How Do I Modify a Database Size Limit?
- Exchange Server 2007: Configure a Unified Messaging Server - Configure the UM Mailbox Policy
- Windows Phone 7 : Subscribing to Podcasts
- Programming Windows Phone 7: Orientation Events
- Windows 7 : Securing the File System - Encrypting Files and Folders
- SQL Server 2008 : Full-Text Searches (part 2)
- Windows Server 2008 : Controlling Access to Web Services (part 4) - Configuring Authentication Settings
- BizTalk Server 2009 : Consuming WCF services without orchestration
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS