Using Symmetric Encryption in a SQL Server 2005 DatabaseBy 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:|
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!
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.
Figure 5: SQL Server 2005 Key Hierarchy 
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 .
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
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 .
Table 1: Symmetric Functions
|Encrypts data using a symmetric key.|
|Decrypts data using a symmetric key.|
|Encrypts data using a passphrase.|
|Decrypts data using a passphrase.|
|Returns the ID of a symmetric key in the current database.|
|Returns 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.
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
SYMMETRIC KEY statement, as shown in the code snippet below:
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 KEYExample
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
Note: More advanced algorithms exist (such as AES), but can only be used with Windows Server 2003 or above (which includes Windows Vista) - see  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
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
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
Key_GUID function, passing it the
name of the key.
To decrypt the data we use the
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
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
Last in our discussion of asymmetric key encryption are the T-SQL functions
DecryptByPassPhrase. These funcions
act similarly to the
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
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
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.
 Microsoft, "Improving data security by using SQL Server 2005: Using SQL Server 2005 to help protect data," Technical White Paper, October 2005.
 W. Stallings, "Cryptography and network security principles and practices", Fourth Edition, Prentice Hall, 2005.
 Application Security, Inc., "Encryption of data at rest," White Paper, Accessed November 2006, http://www.appsecinc.com/presentations/Encryption_of_Data_at_Rest.pdf.
 Microsoft, "Encryption Hierarchy," SQL Server 2005 Books Online, Accessed November 2006, http://msdn2.microsoft.com/en-us/library/ms189586.aspx.
 Microsoft, "Cryptographic Functions," SQL Server 2005 Books Online, Accessed November 2006, http://msdn2.microsoft.com/en-us/library/ms173744.aspx.
 Microsoft, "CREATE SYMMETRIC KEY (Transact-SQL)", SQL Server 2005 Books Online, Accessed January 2007, http://msdn2.microsoft.com/en-us/library/ms188357.aspx.
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.