SQL SERVER 2005 Encryption

In general, encryption is a mechanism for protecting data, which applies to it a specially designed algorithm, effectively obfuscating its content by making it different from the original. Use of the algorithm involves a component known as the encryption key (a sequence of characters). The process can be reversed by applying an appropriate decryption key and equivalent algorithm, yielding the original data.
Microsoft SQL Server 2005 makes use of a key hierarchy, which helps to protect keys that are to be used for encryption. This hierarchy can best be viewed as a series of layers, in which each layer encrypts the layer below it. Figure shows a graphical representation of the key hierarchy.

Service Master Key
Each SQL Server 2005 installation has exactly one Service Master Key (SMK), which is generated at install time. The SMK directly or indirectly secures all other keys on the server, making it the "mother of all SQL Server encryption keys." The Windows Data Protection API (DPAPI), at the higher O/S level, uses the SQL Server service account credentials to automatically encrypt and secure the SMK.
The SMK can be backed up via the BACKUP SERVICE MASTER KEY T-SQL statement. This statement has the following format:
BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file' ENCRYPTION BY PASSWORD = 'password'
you can use the RESTORE SERVICE MASTER KEY statement:
RESTORE SERVICE MASTER KEY FROM FILE = 'path_to_file' DECRYPTION BY PASSWORD = 'password' [FORCE]
Because it is automatically generated by SQL Server, there are no CREATE or DROP statements for the Service Master Key.
Database Master Keys
While each SQL Server has a single Service Master Key, each SQL database can have its own Database Master Key (DMK). The DMK is created using the CREATE MASTER KEY statement:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
The DMK is encrypted using the Service Master Key and stored in the master database; a feature known as "automatic key management."
Like the Service Master Key, you can backup and restore Database Master Keys.
BACKUP MASTER KEY TO FILE = 'path_to_file'
ENCRYPTION BY PASSWORD = 'password'
Restoring the Database Master Key requires that you use the DECRYPTION BY PASSWORD clause, which specifies the password previously used to encrypt the backup file. In addition you must use the ENCRYPTION BY PASSWORD clause, which gives SQL Server a password to encrypt the DMK after it is loaded in the database.
RESTORE MASTER KEY FROM FILE = 'path_to_file' DECRYPTION BY PASSWORD = 'password' ENCRYPTION BY PASSWORD = 'password' [ FORCE ]
To drop a DMK, use the DROP MASTER KEY statement:
DROP MASTER KEY
This statement drops the Database Master Key from the current database.

Certificates

There are a lot of options associated with the CREATE CERTIFICATE statement.
CREATE CERTIFICATE TestCertificate
ENCRYPTION BY PASSWORD = ‘abcd123@’
WITH SUBJECT = 'This is a test certificate',
START_DATE = '1/1/2006',
EXPIRY_DATE = '12/31/2008';

Encryption and Decryption by Certificate

Certificates can be used to encrypt and decrypt data directly by using the built-in EncryptByCert, DecryptByCert and Cert_ID functions.
The DecryptByCert function is used to decrypt data that was previously encrypted by certificate.

EncryptByCert function, which takes two parameters - the certificate identifier (which can be derived from the certificate name using the Cert_ID T-SQL function) and the string of characters (of nvarchar, char, wchar, varchar or nchar datatype) to be encrypted with it, such as in the following sample statement:
EncryptByCert ( Cert_ID('TestCertificate'), 'Test’)

The DecryptByCert T-SQL function, which returns original data, also requires a certificate identifier and the encrypted string of characters (if the certificate definition included a password, you would need to provide it here as well). Note that you can restrict the ability to decrypt encrypted data either by making the password known to a limited number of users or by applying an AUTHORIZATION clause when creating it (which specifies its owner).
DecryptByCert(Cert_ID('TestCertificate'), @decrypt_data)
The whole process can be illustrated as:
DECLARE @encrypted NVARCHAR(100)
SELECT @encrypted = EncryptByCert(Cert_ID('TestCertificate'), N'Test’)
SELECT @encrypted
SELECT CAST(DecryptByCert(Cert_ID('TestCertificate'), @encrypted) AS NVARCHAR)

Symmetric Keys
You can use certificates to create symmetric keys for encryption and decryption within the database.
CREATE SYMMETRIC KEY CCKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE TestCertificate

SQL Server provides a set of functions to encrypt and decrypt data by symmetric key. These functions are EncryptByKey, DecryptByKey and Key_GUID.

The EncryptByKey function requires a reference to the symmetric key GUID in order to encrypt data.
The DecryptByKey function performs the reverse of EncryptByKey. This function decrypts your previously encrypted data.

To see that we have indeed created the intended key and to list all keys within the current database, we can use the following command, which selects all rows from the system catalog view named symmetric_keys:
SELECT * FROM sys.symmetric_keys;

Storing / Retrieving Encrypted Data
CREATE TABLE SymmetricTable(

Id INT IDENTITY(1,1) PRIMARY KEY,

PlainText NVARCHAR(100),

CipherText VARBINARY(MAX)

);
CREATE SYMMETRIC KEY CCKey

WITH ALGORITHM = TRIPLE_DES ENCRYPTION

BY CERTIFICATE TestCertificate;
-- must open the key if it is not already

OPEN SYMMETRIC KEY CCKey DECRYPTION BY PASSWORD = N'Password';
-- declare and set varible @str to store plaintext

DECLARE @str NVARCHAR(100)SET @str = N'Hello DESX';
-- encrypt @str and store in Table

INSERT INTO SymmetricTable (PlainText, CipherText)

VALUES ( @str, EncryptByKey(Key_GUID('CCKey'), @str));
-- select data from TempTable

SELECT * FROM SymmetricTable;
-- decrypt CipherText column and display it

SELECT CONVERT(NVARCHAR(100),

DecryptByKey(CipherText)) AS PlainText

FROM SymmetricTable;
-- close the key and drop it

CLOSE SYMMETRIC KEY CCKey;