O'Reilly logo

Professional Microsoft® SQL Server® 2008 Administration by Steven Wort, Ross LoForte, Wayne Snyder, Ketan Patel, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required