SQL SERVER – Transparent Data Encryption and Frequently Asked Questions

In the recent past, I have been traveling to Delhi to meet customers and also attend a few conferences which my company has been sponsoring. These travel experiences give me an opportunity to meet folks who read my blog regularly as a face-to-face interaction. Most of these interactions mean I get to spend time with really smart people who quiz me for which I get back to my blog for answers. Last week, when I was at a conference, one DBA walked up to me and said – “Hey Pinal, I am a DBA from a reputed banking company. We are in the process of deploying TDE for one of our databases which is in SQL Server 2008 R2. I had a few doubts, can I ask you?” Well, as simple as this interaction went, the whole conversation spanned for close to 30 mins and I am doing a summary of the conversation in this blog for your reference.

When does TDE encrypt the DB?

Once Transparent Data Encryption is enabled by issuing the “Alter Database” command, SQL Server performs basic checks such as Edition Check, Read-only Filegroups, and presence of DEK etc. Once the checks are complete, the command returns immediately with success. This does not mean that the database encryption is complete. Since Encryption is done in the I/O path, all the data pages that are already written to the disk prior to enabling the Encryption have to be read into the memory and then written back to the disk after encrypting the page. This process is also referred to as “Encryption scan”. This task is carried out by Background processes (system SPIDS). The encryption scan, which runs asynchronously to the DDL, takes a shared lock on the database. All normal operations that do not conflict with these locks can proceed without being blocked.

You mentioned READONLY DB, tell me more?

Transparent data Encryption does not work on a database that contains any filegroups that are marked Read-Only or any files that are marked as Read-only. Alter Database fails with an error message (33118) clearly indicating the reason for the failure. Users can enable read-only property on the filegroups once the encryption scan is completed. However, no DDL related to TDE (DEK change) can be run on the database until the read-only property is removed.

What happens to TLog files?

Encryption works differently on a Transaction Log and is complicated. Since Transaction Log is designed to be Write-Once fail safe, TDE does not attempt to Encrypt the contents of the Log file that were written to the disk already. Similarly, the log header cannot be re-written because of this write-once principle so there is no guarantee that log records written to the log even after TDE is enabled will be encrypted. The smallest unit of encryption for log files is a virtual log file (VLF). So either an entire virtual log file (VLF) is encrypted or it’s not. Also the entire VLF is encrypted with the same key. When encryption is turned on for a database, the next time the log manager moves to a new VLF, this new VLF will be encrypted. So there is no deterministic order between when data pages are encrypted by the scan vs. when the log is encrypted.

I thought these were some of the interesting conversations I have had in the recent past that are worth a mention to share. If you have used TDE in your environment, do let me know. I would love to know your experiences in working with it. Is there any catch that you want me to explain next time? Write it over as comments and would love to explore more.

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

SQL SERVER – History of SQL Server Database Encryption

I recently met Michael Coles and Rodeney Landrum the author of one of the kind bookencryptionbook SQL SERVER   History of SQL Server Database Encryption 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)

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)