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.
“What is your opinion on encryption? What I mean is: In a world filled with data, how do you see encryption?” This is the precise question Michael Coles posed to me on March 3rd of this year, while we were heading to Starbucks in Seattle. We were both attending the Microsoft MVP Summit there.
In the information era, security has become one of the most vital aspects of life. Although the topic may seem a little mundane, its importance cannot be overemphasized. It is the pillar of the information age and I shudder to think where we would be without it. We don’t leave our houses unlocked and risk thieves or opportunists taking off with our valuables. We also often take precautions, not only to preserve the precious, but also to avoid the sheer hassle of replacement and misuse. So too it should be with our information.
Encryption’s roots are extremely old and it has resolved numerous security problems over the years. In days gone by, couriers were entrusted with letters sealed with a royal wax stamp. If on delivery, the seal was broken, it was obvious that there had been a security breach. This very concept evolved as CRC checksum and developed into a complex algorithm. While CRC checksum alerted the end user to the fact that content had been modified, its limitation was that it allowed manipulation of the content to occur in the first place. With encryption, only the authenticated owner can access and modify content.
In response to Michael’s question, I began to tell him what I knew about public and private keys. He looked at me doubtfully and asked me directly if I had ever used encryption in my career. My reluctant answer to this was “No”. He strongly suggested that I not underestimate its capabilities and explore its possibilities. I took his advice and have since implemented encryption for many of my clients, who are now far safer from unauthorized access to data.
To be very honest, in my experience, not many people know much about the subject beyond a little about public and private keys. You do not often find experts discussing symmetric and asymmetric keys, which are just the tip of the iceberg. SQL Server has come a long way with regard to security. Encryption has taken on a whole new meaning in SQL Server 2008. There are many new features such as Extensible Key Management, Transparent Data Encryption, not to mention the pre-2008 ones such as cryptographic hashing, SQL CLR and many more. In performance terms, these are great enhancements.
The one exception is Transparent Data Encryption, where the whole database is encrypted. This can considerably reduce performance if the SQL Server box is not sufficiently powerful. It this is the case, it is a good time to offload all the encryption and decryption to third-party hardware. SQL Server allows third-party management of encryption and decryption through Extensible Key Management.
Extending the earlier courier analogy, consider the fact that even if our letter is secure and safe in our hands, as soon as we hand it to the courier it is exposed to risk and can be compromised. SQL Server 2008 has many new features, which secure data while it is being communicated between applications. A number of features were introduced that check whether data is manipulated during transmission.
Data is everywhere and taking in terms of Terra Bytes (TB) is the current reality. When a large amount of data needs to be handled, there are two major challenges. The first challenge is the actual encryption process and the resources needed to perform it. The second challenge is how to use the data once it has been encrypted. In a regular database searching through TBs of data can take a very long time. Imagine how long this could take in and encrypted database?
In Seattle, Michael and I discussed these challenges and a few more subjects. The discussion lasted more than four hours. I have always known Michael to be an excellent author. He is renowned in the industry for his expertise of XML and Full Text Search. To my mind, he is an expert who has the extraordinary ability of relating complex concepts in simple terms. No matter how long, boring or complicated the topic, his delivery is always sweet, like chocolate that melts in the mouth.
Michael always addresses uncommon subjects. Perhaps his experience as a Sergeant in the army has given him the spirit to explore the unexplored. I have never before encountered a single book on the subject of encryption for SQL Server and Michael’s will a “first”.
I recently had the pleasure of reading it and especially like the manner in which he and his co-author, Rodney, explain the significance of encryption. While many of the concepts covered are domain-specific, quite a few topics are common to all and the appendix is a “must read” for anyone planning a security strategy. One thing that really makes this book special is the fact that each module is written independently and you can find solutions by simply reading the relevant one.
I am a hands-on developer and only like books that have a lot of workable examples. With the exception of the first chapter, the book is filled with examples and hands-on experiments. The first chapter in itself is quite unique, as it not only provides a introduction to encryption, but also the very interesting history of encryption. Even non-technical readers will enjoy this.
It is my great pleasure to welcome this one-of-a- kind book to the SQL Server world. There is no doubt that this book is exceptional and will inspire anyone one who is ready to take their current security mechanism to the next level using encryption.
Database Encryption in SQL Server 2008 Enterprise Edition
SQL Server Technical Article
Writers: Sung Hsueh
Technical Reviewers: Raul Garcia, Sameer Tejani, Chas Jeffries, Douglas MacIver, Byron Hynes, Ruslan Ovechkin, Laurentiu Cristofor, Rick Byham, Sethu Kalavakur
Published: February 2008
TDE does not replace cell-level encryption, EFS, or BitLocker. This white paper compares TDE with these other encryption methods for application developers and database administrators. While this is not a technical, in-depth review of TDE, technical implementations are explored and a familiarity with concepts such as virtual log files and the buffer pool are assumed. The user is assumed to be familiar with cell-level encryption and cryptography in general. Implementing database encryption is covered, but not the rationale for encrypting a database.
SQL Server 2005 and SQL Server 2008 provide encryption as a new feature to protect data against hackers’ attacks. Hackers might be able to penetrate the database or tables, but owing to encryption they would not be able to understand the data or make use of it. Nowadays, it has become imperative to encrypt crucial security-related data while storing in the database as well as during transmission across a network between the client and the server.
Encryption hierarchy is marked by three-level security. These three levels provide different mechanisms for securing data across networks and local servers. Different levels of hierarchies allow multiple instances of services (e.g., SQL Server Services) to run on one physical server.
Windows Level – Highest Level – Uses Windows DP API for encryption
SQL Server Level – Moderate Level – Uses Services Master Key for encryption
Symmetric Key – In Symmetric cryptography system, the sender and the receiver of a message share a single, common key that is used to encrypt and decrypt the message. This is relatively easy to implement, and both the sender and the receiver can encrypt or decrypt the messages.
Asymmetric Key – Asymmetric cryptography, also known as Public-key cryptography, is a system in which the sender and the receiver of a message have a pair of cryptographic keys – a public key and a private key – to encrypt and decrypt the message. This is a relatively complex system where the sender can use his key to encrypt the message but he cannot decrypt it. The receiver, on the other hand, can use his key to decrypt the message but he cannot encrypt it. This intricacy has turned it into a resource-intensive process.
Yet another way to encrypt data is through certificates. A public key certificate is a digitally signed statement that binds the value of a public key to the identity of the person, device, or service that holds the corresponding private key. A Certification Authority (CA) issues and signs certifications. Download complete script here.
Please create a sample database that we will be use for testing Encryption. There are two different kinds of encryptions available in SQL Server:
Database Level – This level secures all the data in a database. However, every time data is written or read from database, the whole database needs to be decrypted. This is a very resource-intensive process and not a practical solution.
Column (or Row) Level – This level of encryption is the most preferred method. Here, only columns containing important data should be encrypted; this will result in lower CPU load compared with the whole database level encryption. If a column is used as a primary key or used in comparison clauses (WHERE clauses, JOIN conditions) the database will have to decrypt the whole column to perform operations involving those columns.
Let’s go over a simple instance that demonstrates the encryption and the decryption process executed with Symmetric Key and Triple DES encryption algorithm.
/* Create Database */ USE master
GO CREATE DATABASE EncryptTest ON PRIMARY ( NAME = N'EncryptTest', FILENAME = N'C:\EncryptTest.mdf') LOG ON ( NAME = N'EncryptTest_log', FILENAME = N'C:\EncryptTest_log.ldf') GO
First, let’s create a sample table and then populate it with sample data. We will now encrypt one of the two columns of the table.
/* Create table and insert data in the table */ USE EncryptTest
GO CREATE TABLE TestTable (FirstCol INT, SecondCol VARCHAR(50)) GO INSERT INTO TestTable (FirstCol, SecondCol) SELECT 1,'First' UNION ALL SELECT 2,'Second' UNION ALL SELECT 3,'Third' UNION ALL SELECT 4,'Fourth' UNION ALL SELECT 5,'Fifth' GO /* Check the content of the TestTable */ USE EncryptTest
GO SELECT * FROM TestTable
The preceding code will return the result depicted in the subsequent figure.
Result of the SQL query
Every database can have one master key. Database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys present in the database. It uses Triple DES algorithm together with user-provided password to encrypt the keys.
/* Create Database Master Key */ USE EncryptTest
GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLAuthority' GO
Certificates are used to safeguard encryption keys, which are used to encrypt data in the database. SQL Server 2005 has the capability to generate self-signed X.509 certificates.
/* Create Encryption Certificate */ USE EncryptTest
GO CREATE CERTIFICATE EncryptTestCert WITH SUBJECT = 'SQLAuthority' GO
The symmetric key can be encrypted by using various options such as certificate, password, symmetric key, and asymmetric key. A number of different algorithms can be employed for encrypting key. The supported algorithms are DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256.
/* Create Symmetric Key */ USE EncryptTest
GO CREATE SYMMETRIC KEY TestTableKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE EncryptTestCert
Now add a column of type varbinary to the original table, which will store the encrypted value for the SecondCol.
/* Encrypt Data using Key and Certificate
Add Columns which will hold the encrypted data in binary */ USE EncryptTest
GO ALTER TABLE TestTable ADD EncryptSecondCol VARBINARY(256) GO
Before the key is used, it needs to be decrypted using the same method that was used for encrypting it. In our example, we have used a certificate for encrypting the key. Because of the same reason, we are using the same certificate for opening the key and making it available for use. Subsequent to opening it and making it available for use, we can use the encryptkey function and store the encrypted values in the database, in the EncryptSecondCol column.
/* Update binary column with encrypted data created by certificate and key */ USE EncryptTest
GO OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCert UPDATE TestTable SET EncryptSecondCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SecondCol) GO
We can drop the original SecondCol column, which we have now encrypted in the EncryptSecondCol column. If you do not want to drop the column, you can keep it for future comparison of the data when we decrypt the column.
/* DROP original column which was encrypted for protect the data */ USE EncryptTest
GO ALTER TABLE TestTable DROP COLUMN SecondCol
We can run a SELECT query on our database and verify if our data in the table is well protected and hackers will not be able to make use of it even if they somehow manage to reach the data.
/* Check the content of the TestTable */ USE EncryptTest
GO SELECT * FROM TestTable
Result of the previous SQL query
Authorized user can use the decryptbykey function to retrieve the original data from the encrypted column. If Symmetric key is not open for decryption, it has to be decrypted using the same certificate that was used to encrypt it. An important point to bear in mind here is that the original column and the decrypted column should have the same data types. If their data types differ, incorrect values could be reproduced. In our case, we have used a VARCHAR data type for SecondCol and EncryptSecondCol.
/* Decrypt the data of the SecondCol */ USE EncryptTest
GO OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCert SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol)) AS DecryptSecondCol FROM TestTable
If you drop the database after the entire processing is complete, you do not have to worry about cleaning up the database. However, in real world on production servers, the database is not dropped. It is a good practice for developers to close the key after using it. If keys and certificates are used only once or their use is over, they can be dropped as well. Dropping a database will drop everything it contains – table, keys, certificates, all the data, to name a few.
/* Clean up database */ USE EncryptTest
GO CLOSE SYMMETRIC KEY TestTableKey
GO DROP SYMMETRIC KEY TestTableKey
GO DROP CERTIFICATE EncryptTestCert
GO DROP MASTER KEY GO USE [master]
GO DROP DATABASE [EncryptTest]
Encryption is a very important security feature of SQL Server 2005. Long keys and asymmetric keys create unassailable, stronger encryption and stronger encryption uses lots of CPU to encrypt data. Stronger encryption is slower to process. When there is a huge amount of data to encrypt, it is suggested to encrypt it using a symmetric key. The same symmetric key can be encrypted further with an asymmetric key for additional protection, thereby adding the advantage of a stronger encryption. It is also recommended to compress data before encryption, as encrypted data cannot be compressed.