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!
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.
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
Function
Description
EncryptByKey
Encrypts data using a symmetric key.
DecryptByKey
Decrypts data using a symmetric key.
EncryptByPassPhrase
Encrypts data using a passphrase.
DecryptByPassPhrase
Decrypts data using a passphrase.
Key_ID
Returns the ID of a symmetric key in the current database.
Key_GUID
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.
-- 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.
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.
-- 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:
-- 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.
