When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
•Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article
Jobs




ASP ASP.NET ASP FAQs Message Board Feedback ASP Jobs
 
Print this Page!
Published: Wednesday, February 21, 2007

Using Symmetric Encryption in a SQL Server 2005 Database

By Erich Peterson and Siqing Li

A Multi-Part Article on Cryptographic Systems and Encrypting Database Data
This article is the second of a three-part series exploring cryptographic systems and encrypting data within a Microsoft SQL Server 2005 database. It explores techniques for encrypting data in a Microsoft SQL Server 2005 database using symmetric encryption. The three parts proceed as follows:

Introduction


As the attacks in which hackers use become more and more sophisticated, and the programs in which they attack become increasingly complex, encryption is becoming the last line of defense in database management system (DBMS) security. A previous article of ours, An Overview of Cryptographic Systems and Encrypting Database Data, compared and contrasted symmetric and asymmetric encryption and discussed key database encryption concepts, including key management, authentication, authorization, and so on. The article also briefly touched upon encryption capabilities of Microsoft SQL Server 2005.

In this article we will expand upon the first and look at how, specifically, to encrypt the data in a SQL Server 2005 database using symmetric encryption techniques. We'll start with examining the facilities for managing keys in SQL Server and then explore symmetric encryption in SQL Server 2005. A future article will look at using asymmetric encryption. Read on to learn more!

- continued -

Key Management in SQL Server 2005


As discussed in An Overview of Cryptographic Systems and Encrypting Database Data, encryption requires the use of key(s) for encrypting and decrypting. With symmetric encryption, the same key is used for both operations; with asymmetric encryption, different keys are used. When encrypting or decrypting data, then, these keys must be made available, but where are these keys stored?

Microsoft SQL Server 2005 makes use of a key hierarchy, which helps to protect keys that are to be used for encryption. This hierarchy can best be viewed as a series of layers, in which each layer encrypts the layer below it. Figure 5 shows a graphical representation of the key hierarchy.

SQL Server 2005 Key Hierarchy
Figure 5: SQL Server 2005 Key Hierarchy [4]

At the top of this hierarchy is the operating system-level DPAPI (Data Protection API). The DAPI consists of function calls that can be used to provide operating-system level data protection to both user and system processes [1]. The next level down in the hierarchy is the SQL Server 2005 Service Master Key (SMK). A SMK is a symmetric key which is generated when an instance of SQL Server 2005 is installed, which is done automatically during installation and uses of the function cryptGenKey. Then, the DPAPI uses the password of the account under which SQL Server 2005 runs to encrypt the SMK. Because of the way in which SMK is generated, the DBA must be careful if he/she changes the account under which SQL Server 2005 runs. If it is changed, the SMK must be decrypted using the original credentials, and afterwards, encrypted using the new credentials.

If within a specific database a user wants encrypt data using a symmetric, asymmetric key or certificate, without supplying a password to encrypt that key, a Database Master Key (DMK) must be explicitly created (it is not automatically created). Each DMK created is then encrypted by the SMK. It is this DMK, which can be used to automatically encrypt and/or decrypt a key (this is known as Automatic Key Management), if a database developer so chooses. Also, it is possible for those keys encrypted with the DMK to encrypt other keys as well. Lastly, the careful reader will notice that this means the creation of a DMK is not mandatory, in order to use encryption within a database. If a DMK is not created, then keys created will have to be encrypted using a supplied password.

T-SQL Support for Symmetric Encryption


While all the cryptographic functionalities of SQL Server 2005 sound great, they would be utterly useless if the tools needed to implement them were not provided and easy to use. Many of these capabilities are exposed to the user as extensions to T-SQL. This section will serve to document and demonstrate SQL Server 2005's symmetric encryption mechanisms using T-SQL. Table 1 summarizes the symmetric encryption functions [5].

Table 1: Symmetric Functions

FunctionDescription
EncryptByKeyEncrypts data using a symmetric key.
DecryptByKeyDecrypts data using a symmetric key.
EncryptByPassPhraseEncrypts data using a passphrase.
DecryptByPassPhraseDecrypts data using a passphrase.
Key_IDReturns the ID of a symmetric key in the current database.
Key_GUIDReturns the GUID of a symmetric key in the database.

Let us now look at examples of using these functions and statements. If you'd like to follow along, you will need to load SQL Server Management Studio (Start --> All Programs --> SQL Server 2005), connect to an instance of SQL Server, and click on New Query in the upper left of the window. This will bring up a blank T-SQL query window that can be used to enter the following examples. Once the code has been entered into an empty query window, execute it by clicking the red exclamation point with the words Execute next to it. Figure 6 highlights these two buttons and shows a blank T-SQL query window.

SQL Server Management Studio
Figure 6: SQL Server Management Studio

Note: It is a good idea to either click on New Query to create a blank query window to work with for each of the following code snippets/examples (or to delete all previous code in the same sheet before running the next example). This will keep you from running the same code twice inadvertently.

A symmetric key can be created using the CREATE SYMMETRIC KEY statement, as shown in the code snippet below: 

-- use the master database
USE master;

CREATE SYMMETRIC KEY SecureSymmetricKey
        WITH ALGORITHM = DESX
        ENCRYPTION BY PASSWORD = N'StrongPassword';

If you enter the above code into the blank T-SQL query window and execute it, you should get confirmation of its successful execution, as shown in Figure 7.

CREATE SYMMETRIC KEY Example
Figure 7: CREATE SYMMETRIC KEY Example

The above statement will create the symmetric key known by the name SecureSymmetricKey, uses the DESX encryption algorithm to create a key, and then encrypts that key (via the triple DES encryption algorithm - not DESX) using the supplied password StrongPassword. In other words, the supplied password is used to help derive a key which is used by the triple DES algorithm to encrypt the SecureSymmetricKey created using DESX.

To see that we have indeed created the intended key and to list all keys within the current database, we can use the following command, which selects all rows from the system catalog view named symmetric_keys:

SELECT * FROM sys.symmetric_keys;

Note: More advanced algorithms exist (such as AES), but can only be used with Windows Server 2003 or above (which includes Windows Vista) - see [6] for more information.

From looking at the resultset (with too many columns to show a picture of), we can gather some useful information about the keys in our database, such as a key's length, the algorithm used to generate it, its creatation time, and its global unique identifier (GUID). If executed while still in the master database, we can see the service master key listed as well. Now let us demonstrate just how we go about encrypting and decrypting data based on a symmetric key, and the functions we must use in order to facilitate in those processes. Listing 1 shows an example of how this can be done.

Listing 1: T-SQL Symmetric Key Encryption/Decryption 

-- use the database tempdb
USE tempdb;

-- create symmetric key 'SecureSymmetricKey'
-- using the DESX encryption algorithm 
-- and encrypt the key using the password
-- 'StrongPassword'
CREATE SYMMETRIC KEY SecureSymmetricKey
	WITH ALGORITHM = DESX
	ENCRYPTION BY PASSWORD = N'StrongPassword';

-- must open the key if it is not already
OPEN SYMMETRIC KEY SecureSymmetricKey
	DECRYPTION BY PASSWORD = N'StrongPassword';

-- declare and set varible @str to store plaintext
DECLARE  @str NVARCHAR(100)
SET @str = N'Hello DESX';

-- declare and set varible @encrypted_str to store
-- ciphertext
DECLARE @encrypted_str VARBINARY(MAX)
SET @encrypted_str = 
          EncryptByKey(Key_GUID('SecureSymmetricKey'), @str);


-- display ciphertext
SELECT @encrypted_str AS CipherText;

-- declare and set varible @decrypted_str to store
-- decrypted ciphertext
DECLARE @decrypted_str VARBINARY(MAX)
SET @decrypted_str = DecryptByKey(@encrypted_str);

-- display decrypted text
SELECT CONVERT(NVARCHAR(100), @decrypted_str) AS PlainText;

-- close and drop the key
CLOSE SYMMETRIC KEY SecureSymmetricKey;
DROP SYMMETRIC KEY SecureSymmetricKey;

Since the comments found in Listing 1 provides most of the explaination needed, we will only point out a few important things here. To encrypt data using a symmetric key, we must first open the key using the OPEN SYMMETRIC KEY statement (if it is not already open in the current session), and then use the EncryptByKey function to encrypt the actual data. EncryptByKey expects the GUID of the key and the data you wish to encrypt as parameters. To retrieve the GUID of the key you would like to use to encrypt the data with, one can make use of the Key_GUID function, passing it the name of the key.

To decrypt the data we use the DecryptByKey function, whose only parameter need be the data you wish to decrypt. We do not need to pass the key name to the decryption function, SQL Server 2005 will determine which open key needs to be used (so long as it is open). Also, after we are done with the key, it is a good idea to close it since it takes up memory resources and could theortically be read from memory (if a bad guy could somehow access it). Lastly, we delete the key using the DROP SYMMETRIC KEY statement, but if you will want to use it later you shouldn't delete it.

In the above listing, we never actually stored the encrypted data anywhere; however, it is more likely the database developer will want to store encrypted data. Listing 2 shows how to encrypt and store data in a database table, and then retrieve the decrypted text from it.

Listing 2: Storing / Retrieving Encrypted Data 

-- use the database tempdb
USE tempdb;

-- create permanent temp table
CREATE TABLE SymmetricTempTable
(
    Id INT IDENTITY(1,1) PRIMARY KEY, 
    PlainText NVARCHAR(100),
    CipherText VARBINARY(MAX)
);

-- create symmetric key 'SecureSymmetricKey'
-- using the DESX encryption algorithm 
-- and encypt the key using the password
-- 'StrongPassword'
CREATE SYMMETRIC KEY SecureSymmetricKey
	WITH ALGORITHM = DESX
	ENCRYPTION BY PASSWORD = N'StrongPassword';

-- must open the key if it is not already
OPEN SYMMETRIC KEY SecureSymmetricKey
	DECRYPTION BY PASSWORD = N'StrongPassword';

-- declare and set varible @str to store plaintext
DECLARE  @str NVARCHAR(100)
SET @str = N'Hello DESX';

-- encrypt @str and store in TempTable
INSERT INTO SymmetricTempTable (PlainText, CipherText)
VALUES (
	@str,
	EncryptByKey(Key_GUID('SecureSymmetricKey'), @str)
);

-- select data from TempTable
SELECT * FROM SymmetricTempTable;

-- decrypt CipherText column and display it
SELECT CONVERT(NVARCHAR(100), 
               DecryptByKey(CipherText)) AS PlainText
FROM SymmetricTempTable;

-- close the key and drop it
CLOSE SYMMETRIC KEY SecureSymmetricKey;
DROP SYMMETRIC KEY SecureSymmetricKey;
DROP TABLE SymmetricTempTable;

Last in our discussion of asymmetric key encryption are the T-SQL functions EncryptByPassPhrase and DecryptByPassPhrase. These funcions act similarly to the EncryptByKey and DecryptByKey functions mentioned already - with a few subtle differences. One difference is that the these two new functions do not require the explicit creation of a key with the CREATE SYMMETRIC KEY statement, nor do they require you to open the symmetric key before its use. These new functoins use a "pass-phrase" (i.e. a medium to long sentence), which is use to generate a symmetric key at run-time, to be used for encryption an decryption.

The difference between the previously mentioned symmetric functions and these new ones is a subtle one. Previously a key was generated using the CREATE SYMMETRIC KEY statement, and then that key was encrypted using another symetric key generated by a user-supplied password. Then, whenever data needed to be encrypted or decrypted, the supplied password would be used to decrypt the originaly created symmetric key, and it woluld be the one which encrypted or decrypted the actual data.

With the "pass-phrase" based functions, a symmetric key is generated from the "pass-phrase", which is then used to encrypt and decrypt data. In other words, it is the key that is generated from the "pass-phrase" which is doing the encryption and decryption. These two new functions are easy to use and an example of their syntax is as follows:

Listing 3: EncryptByPassPhrase/DecryptByPassPhrase 

-- declare and set varible @str to store plaintext
DECLARE  @str NVARCHAR(100)
	SET @str = N'Secret Message';

DECLARE @passphrase NVARCHAR(100)
	SET @passphrase = N'This is a sample pass-phrase!!!';

-- declare and set varible @encrypted_str to store
-- ciphertext
DECLARE @encrypted_str VARBINARY(MAX)
SET @encrypted_str = EncryptByPassPhrase(@passphrase, @str);

-- display ciphertext
SELECT @encrypted_str AS CipherText;


-- declare and set varible @decrypted_str to store
-- decrypted ciphertext
DECLARE @decrypted_str VARBINARY(MAX)
SET @decrypted_str = DecryptByPassPhrase(@passphrase, @encrypted_str);

-- display decrypted text
SELECT CONVERT(NVARCHAR(100), @decrypted_str) AS PlainText;

Conclusion


SQL Server 2005 provides a key hierarchy for managing the keys used for encryption, as well as corresponding T-SQL statements for creating, listing, and using these keys. In this article we explored SQL Server 2005's symmetric encryption features. In a future article we'll explore the asymmetric functions along with accessing encrypted data from an ASP.NET web page. SQL Server 2005 provides a variety of T-SQL statements that can be used to implement symmetric encryption.

Happy Programming!

  • By Erich Peterson and Siqing Li

    References


    [1] Microsoft, "Improving data security by using SQL Server 2005: Using SQL Server 2005 to help protect data," Technical White Paper, October 2005.
    [2] W. Stallings, "Cryptography and network security principles and practices", Fourth Edition, Prentice Hall, 2005.
    [3] Application Security, Inc., "Encryption of data at rest," White Paper, Accessed November 2006, http://www.appsecinc.com/presentations/Encryption_of_Data_at_Rest.pdf.
    [4] Microsoft, "Encryption Hierarchy," SQL Server 2005 Books Online, Accessed November 2006, http://msdn2.microsoft.com/en-us/library/ms189586.aspx.
    [5] Microsoft, "Cryptographic Functions," SQL Server 2005 Books Online, Accessed November 2006, http://msdn2.microsoft.com/en-us/library/ms173744.aspx.
    [6] Microsoft, "CREATE SYMMETRIC KEY (Transact-SQL)", SQL Server 2005 Books Online, Accessed January 2007, http://msdn2.microsoft.com/en-us/library/ms188357.aspx.

    Bibliography


    Michael Coles, "SQL 2005 Symmetric Encryption," SQL Server Central, March 2006, http://www.sqlservercentral.com/columnists/mcoles/sql2005symmetricencryption.asp.
    Don Kiely, "Hackers Beware: Keep Bad Guys at Bay with the Advanced Security Features in SQL Server 2005," MSDN Magazine, June 2005, http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.aspx.


    • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article