SQL Server Encryption and Symmetric Key Encryption

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.

view source

1.-- Create Database

2.USE master

3.GO

4.CREATE DATABASE EncryptTest ON  PRIMARY

5. ( NAME = N'EncryptTest', FILENAME = N'C:\EncryptTest.mdf')

6. LOG ON

7. ( NAME = N'EncryptTest_log', FILENAME = N'C:\EncryptTest_log.ldf')

8.GO

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

view source

01.-- Create table and insert data in the table

02.USE EncryptTest

03.GO

04.CREATE TABLE TestTable (FirstCol INT, SecondCol VARCHAR(50))

05.GO

06.INSERT INTO TestTable (FirstCol, SecondCol)

07.SELECT 1,'First'

08.UNION ALL

09.SELECT 2,'Second'

10.UNION ALL

11.SELECT 3,'Third'

12.UNION ALL

13.SELECT 4,'Fourth'

14.UNION ALL

15.SELECT 5,'Fifth'

16.GO

17.-- Check the content of the TestTable

18.USE EncryptTest

19.GO

20.SELECT *

21.FROM TestTable

22.GO

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

Figure 1: Result of the SQL query

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.

view source

1.-- Create Database Master Key

2.USE EncryptTest

3.GO

4.CREATE MASTER KEY ENCRYPTION BY

5.PASSWORD = 'SQLAuthority'

6.GO

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.

view source

1.-- Create Encryption Certificate

2.USE EncryptTest

3.GO

4.CREATE CERTIFICATE EncryptTestCert

5.    WITH SUBJECT = 'SQLAuthority'

6.GO

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.

view source

1.-- Create Symmetric Key

2.USE EncryptTest

3.GO

4.CREATE SYMMETRIC KEY TestTableKey

5.    WITH ALGORITHM = TRIPLE_DES

6.    ENCRYPTION BY CERTIFICATE EncryptTestCert

7.GO

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

view source

1.-- Encrypt Data using Key and Certificate

2.-- Add Columns which will hold the encrypted data in binary

3.USE EncryptTest

4.GO

5.ALTER TABLE TestTable

6.ADD EncryptSecondCol VARBINARY(256)

7.GO

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.

view source

1.-- Update binary column with encrypted data created by certificate and key

2.USE EncryptTest}

3.GO

4.OPEN SYMMETRIC KEY TestTableKey

5.DECRYPTION BY CERTIFICATE EncryptTestCert

6.UPDATE TestTable

7.SET EncryptSecondCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SecondCol)

8.GO

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.

view source

1.-- DROP original column which was encrypted for protect the data

2.USE EncryptTest

3.GO

4.ALTER TABLE TestTable

5.DROP COLUMN SecondCol

6.GO

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.

view source

1.-- Check the content of the TestTable

2.USE EncryptTest

3.GO

4.SELECT *

5.FROM TestTable

6.GO

Figure 2: 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 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.

view source

1.-- Decrypt the data of the SecondCol

2.USE EncryptTest

3.GO

4.OPEN SYMMETRIC KEY TestTableKey

5.DECRYPTION BY CERTIFICATE EncryptTestCert

6.SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol)) AS DecryptSecondCol

7.FROM TestTable

8.GO

Figure 3: Result of the previous SQL query

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.

view source

01.-- Clean up database

02.USE EncryptTestGO

03.CLOSE SYMMETRIC KEY TestTableKey

04.GO

05.DROP SYMMETRIC KEY TestTableKey

06.GO

07.DROP CERTIFICATE EncryptTestCert

08.GO

09.DROP MASTER KEY

10.GO

11.USE [master]

12.GO

13.DROP DATABASE [EncryptTest]

14.GO

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.