SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in


By JohnMagnabosco,

I recently had a conversation regarding data security and encryption, to which the closing statement was "encryption is just another way for data to be lost". It is a worryingly common sentiment. In any given conversation about encryption it is nearly certain that the question "What happens if the key is lost?" will be asked. Of course, it is a valid concern. After all, a lost key means that the encrypted data cannot be decrypted and therefore is lost. However, the fear of the "lost key" is not a valid reason to avoid encryption altogether.

When I was a student, a recurring nightmare of mine was the forgotten locker combination. The scenario would be that I was rushing through the halls on my way to a very important examination; but, first there was "something" I needed out of my locker. As I began to spin the dial on the lock of my locker I soon realized that its combination had slipped my memory. In desperation I began trying random numbers in the hope that I would guess the code. A stream of students making their way into their classrooms buffeted me to a fro elevating my anxiety. The hallway gradually cleared and the din of chatting reduced to the clapping echo of the final student's footsteps. Thankfully, this never happened in real life.

This is the fear of not being able to access something of value when it is needed. It is the fear of the fragile nature of our memories, and of the inability to recall the "special code" in a time of need. It explains why passwords are found scribbled on a Post-it® note and stuck to the monitor screen. It is a key reason that more advanced protection methods for sensitive data, such as encryption, are avoided.

If encryption is implemented without careful planning and without a maintenance strategy, it can become a hairy mess; but isn't this also true of any aspect of data and database administration? Without regular backups and careful attention to data integrity, a database is at a high risk of data loss, regardless of whether or not you use encryption.

Encryption requires careful consideration of what should be protected and the extent of its application. Granting permissions to the keys, and performing any necessary schema modifications to accommodate the encrypted values, are also a part of the implementation process Once encryption is implemented it requires periodic maintenance of retiring aging encryption keys with fresh ones. This practice ensures the continued effectiveness of the keys.

A fundamental aspect of the whole process is backing up the encryption keys and storing them in a safe location. If these practices are followed, the DBA's answer to the question “What happens if the key is lost?” should be exactly the same as the answer they'd give if asked the question "What happens if data is corrupted or lost due to a disk failure": I will restore it from backup. Failure to do so in either case may result in a new DBA job posting.

I'm in the final stages of a book, Protecting Sensitive Data in SQL Server, that I hope will address some of the concerns and confusion surrounding encryption, and other data protection methods. I hope to hear the question "What happens if I do not encrypt my sensitive data?" occur more often in my conversations regarding data security. I hope to see the fear of the "lost key" displaced by fear of data loss due to unauthorized disclosure, which will not only result in the leakage of sensitive data but also the exposure to the data being fraudulently modified. Encryption is one of the most valuable weapons with which that battle can be won.


John Magnabosco (guest editor).

Total article views: 171 | Views in the last 30 days: 1
Related Articles

Advice on data encryption

Need options for encrypting sensitive data


Classifying Sensitive Data

The ability to protect, and perhaps handle, sensitive data separately from other data is becoming mo...


Optimizing Protected Indexes (was Indexing Encrypted Data)

Why bother encrypting? Riddle me this batman? Why bother to Encrypt? As a SQL Server DBA I am pr...


It's Time for Encryption

Encryption is starting to become a necessity, not an option as we work with and store more sensitive...


data protection question

data protection question for outsourced dbs

database weekly