Professional SQL Server™ 2005 Programming

22.7. Certificates and Asymmetric Keys

We have, at a few different points in the book (including earlier in this chapter), mentioned the notion of encryption. Certificates and asymmetric keys are the primary mechanism for defining the encryption keys for the different levels of your server architecture. Both of these are different methods of doing the same basic thing, and they are largely interchangeable. Whether you use certificates or asynchronous keys, you need to keep in mind that these are much like the keys to your house—if you let everyone have them, then they quickly lose their value (now anyone can get in, so why bother locking anyone out?).

SQL Server supports the notion of keys at several different levels based on the notion that you may want to separate several different silos of control under different encryption keys. SQL Server maintains a Service Master Key that goes with each server installation. It is encrypted by the Windows-level Service Master Key. Likewise, each database contains a Database Master Key, which can, if you choose, itself be encrypted based on the Service Master Key. Then, within each database, you can define certificates and/or asymmetric keys (both of which are a form of key). Overall, the hierarchy looks something like Figure 22-3.

Figure 22.3. Figure 22-3

22.7.1. Certificates

Since SQL Server 2000, SQL Server has included its own certificate ...