SQL Server 2005 implements a framework to help protect
encryption keys by using an encryption key hierarchy, as shown in Figure 1. In
this hierarchy each layer encrypts the layers that are below it.
Figure 1 – SQL Server 2005 encryption key hierarchy.
As seen from Figure 5, The Data Protection API (DPAPI) is at
the top of this encryption key hierarchy, which in fact is a pair of function
calls that provide operating system–level data protection services to user and
system processes. Instances of SQL Server 2005 encrypt their primary keys by
the DPAPI service of Windows and then they encrypt the primary key of each
database using these primary keys. Each database's primary key is then used to
encrypt the certificates or asymmetric keys inside the database. Later, the
symmetric keys can continue to be encrypted through these certificates and asymmetric
keys. This forms a full key management architecture.
Asymmetric Key
An asymmetric key consists of a private key and the
corresponding public key. Each of these keys can decrypt data that the other
keys encrypt. In SQL Server 2005 the asymmetric keys are public and private
key pairs. The public key does not have a particular format as a certificate
would have and the developer cannot export it to a file.
In SQL Server 2005 a developer can encrypt asymmetric keys
by using the following two methods:
1. A user key that is derived from a user-supplied password
2. The Database Master Key
Symmetric Key
A symmetric key is a single key that is used for both
encryption and decryption. The encryption and decryption operations perform
quickly with symmetric encryption. Therefore, symmetric encryption is well
suited for encrypting bulk data in SQL Server 2005.
Generally speaking, symmetric encryption is of great
efficiency but of less safety, while asymmetric encryption is much more secure but
with no efficiency. We should, therefore, encrypt the key data, such as a symmetric
key, with asymmetric encryption policy and then use symmetric key to encrypt
large amounts of data.