Professional Microsoft® SQL Server® 2008 Administration

9.8. Encryption

A common request in the days of SQL Server 2000 was for a way to encrypt data inside columns. Industries such as health care and banking have to encrypt their sensitive data. With the advent of laws like Sarbanes-Oxley, all publicly owned companies must encrypt sensitive data such as social security numbers or passwords. Developers would typically push the functionality into the application, rather than attempt to write an extended stored procedure. Each application then would have its own logic for the encryption and decryption. Beginning with SQL Server 2005, the functionality is built into the core Database Engine and can be used with minimal effort. SQL Server 2008 goes a step further with transparent data encryption, which can encrypt the data without any reworking of application or database code.

9.8.1. Setting Up an Encryption Methodology

The first question that security individuals ask when encrypting data is where do you want to store the keys for encryption? Essentially, the key is what allows encryption and decryption of the data to occur. SQL Server stores a hierarchy of keys. The top of the hierarchy is the service master key, an asymmetric key protected by the Windows Data Protection API. Beneath that, each database can have a master key that protects all the individual keys that can be created. In addition, SQL Server 2008 now supports external keys with the Extensible Key Management (EKM) module. You'll look at EKM later in the section "Extensible ...