SQL SERVER – History of SQL Server Database Encryption

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)

About these ads

SQL SERVER – Explanation of WITH ENCRYPTION clause for Stored Procedure and User Defined Functions

This article is written to answer following two questions I have received in last one week.

Questions 1) How to hide code of my Stored Procedure that no one can see it? 2) Our DBA has left the job and one of the function which retrieves important information is encrypted, how can we decrypt it and find original code?

Answers 1) Use WITH ENCRYPTION while creating Stored Procedure or User Defined Function. 2) Sorry, unfortunately there is no simple way to decrypt the code. Hard way is too hard to even attempt.

Explanations of WITH ENCRYPTION clause If SP or UDF are created WITH ENCRYPTION it is one way street and it is not possible to decrypt it using SQL Server commands. It is always advised to save a copy of the script used to create the SP or UDF on other media than SQL Server. There is no way to get the original source code once it is executed on Server. Only privileged users who can access system tables over the DAC port or directly access database files as well as can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime.

CLR SP and UDF can not be encrypted. SQL Server replication can not replicate encrypted SP or UDF.

I believe in fair programming techniques and strongly recommend against encryption. There should be no need to encrypt any code. If there is need to hide any code from certain users in that case user should be restricted using user login permissions.

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