SQL Server 2008 provides a number of mechanisms to encrypt column data. Once a column has been encrypted, it cannot be read without providing the appropriate credential. Encrypted columns cannot be used in search criteria or indexes.

SQL Server 2008 supports Extensible Key Management to allow integration with external key management systems, and therefore gaining the benefit these solutions may provide such as key rotation.

Encryption Master Keys

The Service Master Key provides the foundation for all encryption with an instance. It is automatically created the first time a credential needs to be encrypted. The Service Master Key can only be decrypted by the service account under which it was created - the SQL Server Service account or by a principal with access to the service account credentials

A Database Master Key must be explicitly generated:

CREATE MASTER KEY ENCRYPTION
  BY PASSWORD = 'password'

The Database Master Key is unique to each database and is used to protect any certificates, symmetric or asymmetric keys stored in the database. The Database Master Key is encrypted using Triple DES and the supplied password. A copy of the Database Master Key is also encrypted using the Service Master Key and stored in the master database. The master database copy allows for automatic decryption within an instance.

The decryption of data occurs in the following order:

1. The Service Master Key is used to decrypt the Database Master Key
2. The Database Master Key is used to decrypt the supplied credential
3. The decrypted credential is used to decrypt the requested data

One-Way Hash Encryption

A hash algorithm provides one-way encryption: Data can be encrypted but no algorithm exists to decrypt the data. A hash algorithm when applied to the same input will always produce the same output. Thus hash algorithms can be used for password comparisons. SQL Server 2008 uses an MD5 hashing algorithm.

Hash algorithms are vulnerable to brute force attacks and are thus not suitable encrypting data sets with limited values such as bank account details, credit card numbers, etc.

Data can be hash encrypted with the HashBytes function:

DECLARE @hash_var VARCHAR(100)
SET @hash_var = 'The keys to the front door are under the door mat'
SELECT HashBytes('MD2', @hash_var)
SELECT HashBytes('MD4', @hash_var)
SELECT HashBytes('MD5', @hash_var)
SELECT HashBytes('SHA', @hash_var)
SELECT HashBytes('SHA1', @hash_var)

Adding a static value (salting) to data before hashing it, dramatically increase the effectiveness of the encryption, but the salt value will need to be stored safely from potential intruders

Symmetric Key Encryption

Symmetric Key encryption provides two-way encryption, using the same key to encrypt and decrypt data. Although not as secure as asymmetric encryption they have less processing overhead

CREATE SYMMETRIC KEY my_symmetric_key WITH ALGORITHM = RC4
ENCRYPTION BY PASSWORD = 'NoOneWillEverGuessThis'
GO

SELECT * FROM sys.symmetric_keys
GO

OPEN SYMMETRIC KEY my_symmetric_key DECRYPTION BY PASSWORD = 'NoOneWillEverGuessThis'
GO

DECLARE @secret_data VARCHAR(30), @safe_data VARBINARY(80), @cracked VARCHAR(30)
SET @secret_data = 'The keys are now in the aspidistra pot'
SET @safe_data = EncryptByKey(Key_GUID('my_symmetric_key'), @secret_data)
SET @cracked = cast(DecryptByKey(@safe_data) AS VARCHAR(30))

SELECT @secret_data AS PlainText, @safe_data AS EncryptedText, @cracked AS OhNo
GO

Asymmetric Key Encryption

Asymmetric Key Encryption uses a private key to encrypt data and a public key to decrypt the data. Certificates work in much the same way: a Certifying Authority issues a public key certificate to identify an entity. The entity identified by the public certificate keeps a private key used to encrypt data. SQL Server provides a means for database administrators to issue self-signed certificates for local use.

USE Robots
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'NoOneHasAChanceOfGuessingThis'
GO

CREATE CERTIFICATE my_certificate AUTHORIZATION dbo
  WITH SUBJECT = 'Test Certificate'
GO

SELECT * FROM sys.certificates
GO

DECLARE @plain_text VARCHAR(30), @encrypted VARBINARY(500), @decrypted VARCHAR(MAX)
SET @plain_text = 'Key is with the neighbours'
SELECT @encrypted = ENCRYPTBYCERT(CERT_ID('my_certificate'), @plain_text)
SELECT @decrypted = CAST(DECRYPTBYCERT(CERT_ID('my_certificate'), @encrypted) AS VARCHAR(MAX))

SELECT @plain_text, @encrypted, @decrypted

Transparent Data Encryption

TDE provides real-time encryption/decryption for the entire contents of databases on disk or tape. TDE stores an encryption key in the database boot record. The TDE key is encrypted with a certificate stored in the master database. Thus a copy of your database can not be decrypted without a copy of the key in the master database:

USE master
GO

CREATE MASTER KEY ENCRYPTION 
  BY PASSWORD = 'ThisOneIsMuchMoreSecureThanAnyPasswordUsedSoFar'
GO

CREATE CERTIFICATE my_server_cert WITH SUBJECT = 'TDE Certificate'
GO

USE Robots
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE my_server_cert
GO

ALTER DATABASE Robots
SET ENCRYPTION ON 
GO