Published: 26 Feb 2008
By: Pinal Dave

Pinal Dave talks about encryption in SQL Server 2005.

Introduction

SQL Server 2005 provides encryption as a new feature to protect data against the attacks of hackers. Hackers may be able to get hold of the database or tables, but they wouldn't understand the data or be able to use it. It is very important to encrypt crucial security related data when stored in the database, as well while transmitting across a network between the client and the server.

There are three levels of encryption hierarchy. These levels provide different mechanisms for securing data across networks and local servers. Different level of hierarchies allows 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
  • Database Level - Lower Level - Uses Database Master Key for encryption

There are two different kind of keys used in encryption.

  • Symmetric Key - Symmetric cryptography system in which the sender and 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 the sender and receiver either 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 relatively complex system and the sender can use its key to encrypt the message, but he can't decrypt it. The receiver can use its key to decrypt the message but he can't encrypt it. Due to its complexity, this is a resource intensive process.

Certificates are used as well for encrypting data. 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.

Please create a sample database that we will use for testing Encryption. There are two different kinds of encryption available in SQL Server:

  • Database Level - This will secure all the data in database. However, every time data is written or read from database, the whole database has 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 preferred method of encryption. Only columns containing important data should be encrypted; this will result in less CPU load than the whole database level encryption. If a column is used as primary key, or used in comparison clauses (WHERE clauses, JOIN conditions) the database will have to decrypt the whole column to do operations involving those columns.

Let's go over a simple example that demonstrates the encryption and decryption process done with Symmetric Key and Triple DES encryption algorithm.

Create a sample table and populate it with sample data. We will encrypt one of the two columns of the table.

The previous code will return the result shown in the next figure.

Figure 1: Result of the SQL query

images/image1.gif

Every database can have one master key. The 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 along with user provided password to encrypt the keys.

Certificates are used to protect encryption keys, which are used to encrypt data in the database. SQL Server 2005 has the ability to generate self-signed X.509 certificates.

The symmetric key can be encrypted by using any of the certificate, password, and symmetric key, asymmetric key options. We can use many different algorithms for encrypting key. Supported algorithms are DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256.

Now add a column of type varbinary to original table, which will store the encrypted value for the SecondCol.

Before using the key, it needs to be decrypted by the same method with which it was encrypted. In our example we had used a certificate for encrypting the key. Due to the same reason, we are using the same certificate for opening the key and make it available for use. After it is open and available to use, we can use the encryptkey function and store the encrypted values in the database, in the EncryptSecondCol column.

We can drop the original SecondCol column, which we have now encrypted in the EncryptSecondCol column. If you don't want to drop the column, you can keep it for future comparison of the data when we decrypt the column.

We can run a SELECT query on our database and verify that our data in the table is protected, and hackers will have no understanding of it if they manage to reach the data.

Figure 2: Result of the previous SQL query

images/image2.gi

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 same certificate which was used to encrypt it. One thing to keep in mind here is that the original column and the decrypted column should have the same data types. If they are of different data types, incorrect values could be reproduced. In our case, we have used a VARCHAR data type for SecondCol and EncryptSecondCol.

Figure 3: Result of the previous SQL query

images/image3.gif

If you drop the database after all the 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 usage is over, they can be dropped as well. Dropping a database will drop everything it contains - table, keys, certificates, all the data etc.

Summary

Encryption is a very important security feature of SQL Server 2005. Long keys as well as asymmetric keys create strong encryption and stronger encryption uses lots of CPU to encrypt data. Stronger encryption is slower to process. When there is lots 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 further protection and adds the advantage of a stronger encryption. It is also advisable to compress data before encryption, as encrypted data can't be compressed.

<<  Previous Article Continue reading and see our next or previous articles Next Article >>

About Pinal Dave

Pinalkumar Dave is Microsoft SQL Server MVP and author of several hundreds SQL Server articles. He has six years experience as Principal Database Administrator in MS SQL Server 2008/2005, .NET (C#) and ColdFusion MX. He has a Masters of Science degree in Computer Networks, along with MCDBA, MCAD(.NE...

This author has published 16 articles on DotNetSlackers. View other articles or the complete profile here.

Other articles in this category


Lucene.NET vs SQL Server Full-text – Generating a million records and a full-text index
In this article we will take a look at how SQL Server performs with one million records in a table. ...
Identifying currently running SQL queries
This article is taken from the book SQL Server DMVs in Action. The author discusses a routine that i...
What's blocking my running SQL?
This article is taken from the book SQL Server DMVs in Action. The author discusses a routine that i...
Easy Insert, Update and Retrieve Values for Microsoft SQL Database with C#, Visual Studio
This article will provide you with the small amount of code required to insert, update and retrieve ...
SQL Azure to Developers: Part 1
In this part we will focus on overview of SQL Azure along with a first look on SQL Azure Management ...

You might also be interested in the following related blog posts


Script to Inventory Print Servers read more
Automated Attendant with the OCSDK Wrapper and WPF read more
Repairing SQL 2005 Business Intelligence Studio after uninstalling VS2005 read more
New Sophisticated SQL Injection Attack read more
SQL Server Compact Edition 3.5 read more
Five New Security Tutorials Now Available read more
Did You Know... There are 3 Versions of Keyframe Animation? read more
How to debug a stored procedure in your Sql Server 2005 read more
Query Notifications and LINQ to SQL - Well I'll be, you *can* do it (with caveats) read more
Database Access Using The .NET Data Providers read more
Top
 
 
 

Discussion


Subject Author Date
placeholder Sql Server Encryption with C# Curtis Lesperance 1/26/2010 12:40 PM
Symmetric Key Decryption Senthilkumar Sundaram 3/26/2010 12:22 PM
placeholder Transparent Data Encryption in Linq Yogesh Rathod 8/24/2011 5:31 AM
Other alternatives Neil Weicher 10/5/2011 10:39 AM
placeholder Comment Venu G 7/12/2013 6:12 AM
ss2005 encryption Stan Teitelbaum 2/28/2008 10:04 AM
placeholder Restoring Database to another Server Lee Barker 4/23/2008 9:36 AM
RE: Restoring Database Lee Barker 4/23/2008 9:54 AM
placeholder Question about the db key password Naomi Zorn 6/9/2008 7:30 AM
Encryption On One Machine and Decryption on Other Machine Yagnesh Tailor 9/26/2008 1:57 AM
placeholder How can I restrict sql user not to decrypt data ? Nauman Ikram 2/9/2009 1:57 AM

Please login to rate or to leave a comment.