I recently met Michael Coles and Rodeney Landrum the author of one of the kind book Expert SQL Server 2008 Encryption at SQLPASS in Seattle.

During the conversation we ended up how Microsoft is evolving encryption technology. The same discussion lead to talking about history of encryption tools in SQL Server. Michale pointed me to page 18 of his book of encryption. He explicitly give me permission to re-produce relevant part of history from his book.

Encryption in SQL Server 2000

Built-in cryptographic encryption functionality was nonexistent in SQL Server 2000 and prior versions. In order to get server-side encryption in SQL Server you had to resort to purchasing or creating your own SQL Server XPs. Creating your own cryptographic XPs could be a daunting task owing to the fact that XPs had to be compiled as native DLLs (using a language like C or C++) and the XP application programming interface (API) was poorly documented. In addition there were always concerns around creating wellbehaved XPs that “played nicely” with the SQL Server process.

Encryption in SQL Server 2005

Prior to the release of SQL Server 2005 there was a flurry of regulatory activity in response to accounting scandals and attacks on repositories of confidential consumer data. Much of this regulation centered onthe need for protecting and controlling access to sensitive financial and consumer information. With the release of SQL Server 2005 Microsoft responded to the increasing demand for built-in encryption byproviding the necessary tools to encrypt data at the column level. This functionality prominently featured the following:

• Support for column-level encryption of data using symmetric keys or passphrases.
• Built-in access to a variety of symmetric and asymmetric encryption algorithms, including AES, DES, Triple DES, RC2, RC4, and RSA.
• Capability to create and manage symmetric keys. Key creation and management.
• Ability to generate asymmetric keys and self-signed certificates, or to install external asymmetric keys and certificates.
• Implementation of hierarchical model for encryption key management, similar to the ANSI X9.17 standard model.
• SQL functions to generate one-way hash codes and digital signatures, including SHA-1 and MD5 hashes.
• Additional SQL functions to encrypt and decrypt data.
• Extensions to the SQL language to support creation, use, and administration of encryption keys and certificates.
• SQL CLR extensions that provide access to .NET-based encryption functionality.

Encryption in SQL Server 2008

Encryption demands have increased over the past few years. For instance, there has been a demand for the ability to store encryption keys “off-the-box,” physically separate from the database and the data it contains. Also there is a recognized requirement for legacy databases and applications to take advantage of encryption without changing the existing code base. To address these needs SQL Server 2008 adds the following features to its encryption arsenal:

• Transparent Data Encryption (TDE): Allows you to encrypt an entire database, including log files and the tempdb database, in such a way that it is transparent to client applications.
• Extensible Key Management (EKM): Allows you to store and manage your encryption keys on an external device known as a hardware security module (HSM).
• Cryptographic random number generation functionality.
• Additional cryptography-related catalog views and dynamic management views.
• SQL language extensions to support the new encryption functionality.

The encryption book covers all the tools in its various chapter in one simple story. If you are interested how encryption evolved and reached to the stage where it is today, this book is must for everyone.

You can read my earlier review of the book over here.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Filed under: SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority Book Review, SQLAuthority News, T SQL, Technology Tagged: Encryption, SQL Server Encryption, SQLPASS