SQL Server 2008 Encryption Management
SQL Server 2008 offers rich support for various types of data encryption using symmetric and asymmetric keys and digital certificates. As an admin, you probably need to manage at least the upper level of keys in the hierarchy. Each protects its child keys, which in turn protect their child keys, down throughout the tree. The one exception is when a password is used to protect a symmetric key or certificate. This is how SQL Server lets users manage their own keys and take responsibility for keeping the key secret.
Each SQL Server has its service master key. The service master key is the one key that rules them all. It is a symmetric key generated automatically during SQL Server installation and is encrypted and protected by the Data Protection API (DPAPI), which is provided by the underlying Windows OS, using the credentials of the SQL Server service account. Protection of this key is critical because if it is compromised, an attacker can eventually decipher every key in the server managed by SQL Server. SQL Server manages the service master key for you, although you can execute maintenance tasks on it to dump it to a file, regenerate it, and restore it from a file. However, most of the time you will not need or want to make any of these changes to the key, although administrators are advised to back up their service master keys in the event of key corruption.
The main purpose of the server master key is to secure system data, such as passwords used in instance-level settings such as linked servers or connection strings. The service master key is also used to secure each of the DB master keys. Within each DB, the DB master key serves as the basis for creating certificates or asymmetric keys, which subsequently can be applied to protect data directly or to further extend the encryption hierarchy (as an example, by creating symmetric keys). Creation, storage, and other certificate and key management tasks can be handled internally, without resorting to features of the OS or third-party products.
Each DB can have a single master key. You need to create a DB master key before using it by using the CREATE MASTER KEY TransacT-SQL commands with a user supplied password:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = Pazzw0rd1
SQL Server encrypts the DB master key using a triple DES key derived from the password as well as the service master key. The first copy is stored in the DB, and the second is stored in the master DB. Having the DB master key protected by the server master key makes it possible for SQL Server to decrypt the DB master key automatically when necessary. The app or user does not need to open the master key explicitly using the password. This is a major benefit of having the keys protected in the hierarchy.
When the DB master key exists, developers can use it to create any of three types of keys, subject on the type of encryption necessary: Asymmetric keys, Symmetric keys and Certificates.