"I hold it that a little rebellion now and then is a good thing..." – Thomas Jefferson

Introduction

HIPAA. SOX. FACTA. Every DBA needs to get compliant. However, not every DBA has the resources to migrate corporate databases to the new version of SQL Server overnight. Some of us need to be in compliance *yesterday*, but for one reason or another we won't be able to take advantage of the additional security and other high-speed functionality of SQL 2005 until after tax season.

Or after the start of the next fiscal year.

Or after the next presidential election.

You get the point.

So what do we do - those of us who are stuck with good old SQL 2000 with no foreseeable upgrade in sight? One option is to roll your own solutions. Or you could let someone like me do the dirty work for you.

This DBA Toolkit includes over two dozen extended stored procedures to help DBA's and developers who are committed to the SQL 2000 platform to some degree. Some of the tools are designed to add badly needed encryption to SQL 2000; others provide specific SQL Server functionality I've found useful over the years. All are designed to make SQL development and administration a little easier by extending the functionality of your SQL Server.

This series of articles provides the introduction to the Toolkit:

1. This first article will start with a look at the re-birth of SQL 2000 encryption.
2. The second article will discuss the addition of regular expressions to SQL Server via the Toolkit.
3. A third article will address phonetic matching tools in the Toolkit.
4. Finally, I will wrap up this series with a discussion of additional functionality in the toolkit that fits into none of the above categories.

Feel free to use these tools as you see fit. If you find them useful, please drop me a line and let me know which ones you use, how you use them, and what you find most useful.

Enjoy.

A Note About Extended Stored Procedures

Extended stored procedures are binary DLL's, usually written in C++, that are loaded into the SQL Server address space to perform a specific task. Extra care was taken to make sure the extended stored procedures in this toolkit work well within SQL Server's address space, including additional parameter checking and exception handling.

These stored procedures were created for, and tested on, SQL Server 2000 Service Pack 3a or higher. These procedures were designed to run on SQL 2000. Specifically, I tested the Toolkit on SQL Server 2000 running on Windows XP Professional, Windows 2000 Server and Windows 2003 Server.

A final note - these extended procedures were compiled for, and tested on, the SQL/Windows 32-bit platform, not the 64-bit platform.

Encryption Improvements

With the Free Encryption Toolkit (also released on SQL Server Central), I provided a set of AES and Blowfish encryption and decryption extended stored procedures (XP's) for SQL 2000. One of the items I left on the table for developers and DBA's to handle on an individual basis was encryption key management. This also happens to be one of the hardest areas to manage within the scope of security and encryption. With this Toolkit, I've added a basic encryption key management system to the mix.

The previous encryption tools demonstrated how to use extended procedures to encrypt data in SQL 2000. I designed those tools as a basic demo to be built upon by others on an individual basis, and I left a lot of the exception handling out to prevent complicating the thrust of the code. Based on feedback, I have made some changes in this area:

• I re-wrote the encryption code to better check parameters and to handle exceptions much more gracefully than the previous demo.
• I added more descriptive usage and error messages.
• I revisited the encryption routines themselves and chose sturdiness over speed when selecting algorithms.

I also added a few new algorithms. Previously it was a choice between Blowfish and AES. Based on feedback, I have added DES, TripleDES (3DES) and Twofish algorithms.

This toolkit relies on the built-in Windows CryptoAPI for key security. One of the design considerations during the creation of this toolkit was how much of the CryptoAPI's functionality should be used.

As it turns out Microsoft made that choice easy. There are several flavors of CryptoAPI - Base, Strong, AES and Enhanced, etc. These factors were taken into consideration when designing the Toolkit:

• The Base version is the most widespread, appearing in many different versions of Windows, and
• The Strong, Enhanced and AES CryptoAPI’s are all backwards compatible with the Base version.

Therefore, I used only functionality available in the Base CryptoAPI for this toolkit. Because of the limited number of algorithms available in the Base CryptoAPI, I was severely in what I could do directly in the CryptoAPI. The role of the CryptoAPI within the scope of this toolkit is limited to encrypting and decrypting master keys for secure storage.

Installation

To install the extended procedures and associated user-defined functions and stored procedures:

• Copy all of the DLL files from the \INSTALL directory of the downloaded ZIP file to your MSSQL\BINN directory.
• Run the INSTALL.SQL script (also found in the \INSTALL directory) inside Query Analyzer.

The INSTALL.SQL script installs all extended procedures, stored procedures and user-defined functions for you. It details the installation steps with a short installation log in the QA output window, as in the following screenshot.

Fig. 1. INSTALL.SQL script installation log output

To test your installation, run one of the samples in the \SAMPLES directory. In this example, I'm using the DEMO_DES.SQL sample:

Fig. 2. DEMO_DES.SQL sample script output

If the sample script doesn't produce results similar to the above, or you receive errors from SQL Server when trying to run the samples provided, don’t panic! I’ve included some basic installation troubleshooting steps in the README.RTF file.

The Toolkit also includes a Windows CHM (Compiled Help) file called TOOLKIT.CHM. This file contains descriptions of all the functions, extended procedures, stored procedures, etc., in the database. Complete listings of parameters, return types, and some code samples and other notes are all included in the Help File. The Help File was designed to be your first line for Q&A.

Key Management Tools

A major improvement over the encryption samples previously offered in the Free Encryption Tools is built-in secure key management. I chose to handle key management in the following manner:

• Master keys are used to encrypt local keys. Master keys, in turn are encrypted by the CryptoAPI and stored in the master database.
• Local keys are used to encrypt data. These local keys are encrypted by their associated master keys and stored in the master database as well.

If you've read my previous article on Symmetric Encryption in SQL 2005, this design might seem familiar to you. Here are some of the differences between my implementation and the SQL 2005 model:

• SQL 2005 uses the CryptoAPI exclusively for encryption and decryption of all keys and data. For the Base CryptoAPI, this limits you to the three built-in symmetric encryption algorithms, which do not include AES, Blowfish, TripleDES, Twofish, etc.
• SQL 2005 ties data-encrypting keys to specific databases. The Toolkit does not. You can use data-encrypting keys in the Toolkit (local keys) on any data in any database.

The SQL 2005 implementation does offer advantages over the Toolkit, however:

• SQL 2005 offers tightly integrated extensions to T-SQL, including new T-SQL keywords.
• SQL 2005 supports asymmetric encryption, including self-signed certificates and asymmetric keys.
• SQL 2005 supports layer upon layer of key-encrypting keys and certificates to secure data-encrypting keys. The Toolkit supports a single layer of master keys to encrypt your local keys.
• SQL 2005 supports many combinations of padding and encryption/decryption mode. The Toolkit currently supports Cipher-Block Chaining (CBC) mode and ANSI X.923 style padding only.

When master keys are created, they are encrypted by the Crypto API, using either a password or the current user's login credentials (if you don't supply a password). Each local key and master key must have a unique name since the functions in the Toolkit accessed keys by name.

The INSTALL.SQL script installs the following key management tools in the master database:

Master_Key_Vault Local_Key_Vault

These two tables are used to store encrypted master keys and encrypted local keys, respectively. Keys are retrieved from these tables by name when encrypting and decrypting data.

Create_Master_Key @masterkeyname, @password1, @password2

The Create_Master_Key stored procedure invokes the xp_create_master_key extended procedure to create a new master key. If you supply a password (@password1) xp_create_master_key uses the password as key material to generate the master key. If you do not supply @password1 (i.e., pass in NULL), the master key is generated randomly for you.

After the master key is created, xp_create_master_key connects with the Windows CryptoAPI. It tries to locate the appropriate key container. If the key container can't be located, xp_create_master_key creates it. Once a proper CryptoAPI context is acquired, xp_create_master_key calls the CryptoAPI to encrypt the master key. If you supply @password2, it will be used as key material for the CryptoAPI. If not, your login credentials are used to generate a key for CryptoAPI to encrypt the master key; effectively tying this master key to a single login.

Finally, your encrypted key is stored in the Master_Key_Vault table. Note that if your Master_Key_Vault table does not exist, Create_Master_Key creates it for you.

Create_Local_Key @masterkeyname, @localkeyname, @password

The Create_Local_Key stored procedure creates a randomized local key by invoking xp_create_local_key. The master key you specify (by key name) is used to encrypt the local key after it is generated.

If you supplied @password2 when you called Create_Master_Key, you will need to supply the same password when creating a local key so that the master key can be decrypted properly by the Crypto API.

Rename_Master_Key @oldkeyname, @newkeyname

This stored procedure will rename a previously created master key.

Rename_Local_Key @old_keyname, @newkeyname

This stored procedure will rename a previously created local key.

Key Creation and Security

When you create a master key using the Create_Master_Key stored procedure, you can specify both the "key material" that is used to generate the key, and the key material used by the CryptoAPI to generate a session key to secure the master key (i.e., @password1 and @password2). If you pass NULL as @password1, the master key is generated randomly. If you pass NULL as @password2, your login credentials will be used by the CryptoAPI to secure the master key.

A local key, however, is always generated randomly. It is tied to a specified master key, and you must pass the same password specified as @password2 when the local key is created. You need to specify the same password whenever the local key is used to encrypt or decrypt data. If you specify NULL as the password when creating a local key, your login credentials are used by the CryptoAPI.

The master keys are secured using the CryptoAPI's RC4 encryption algorithm, and stored in the Master_Key_Vault table. Local keys are encrypted by their associated master keys and stored in the Local_Key_Vault table.

Encryption and Decryption

As with the previous sample encryption toolkit, I've included several algorithms in extended procedure/user-defined function format for encryption and decryption.

fn_encrypt_3des (@plaintext, @localkeyname, @password) fn_decrypt_3des (@encryptedtext, @localkeyname, @password)

These functions encrypt your plaintext, or decrypt your encrypted text, respectively using 3-key TripleDES. You supply your plain text in VARBINARY format, and the result of the encryption is returned to you in VARBINARY format. The TripleDES algorithm encrypts in 8-byte blocks, so if your plain text is a multiple of 8 bytes in length, another 8 bytes of padding will be added. Always ensure that your encrypted text columns are at least 8 bytes longer than your plain text columns. If you supplied a password when creating your master key, you must supply the password again here for the CryptoAPI; if not, pass in NULL as the @password parameter.

TripleDES is based on DES, and was created as a way to strengthen DES, using what is known as an "encrypt-decrypt-encrypt" (EDE) strategy. This function extrapolates three different 56-bit keys from your local key. It then uses the first key to encrypt the plain text; the second key to decrypt the encrypted text; and finally, it re-encrypts with the third key. TripleDES is considered more secure than DES, and less vulnerable to various attacks, like the infamous "meet-in-the-middle" attack. The strength, measured in key length, is considered to be somewhere between 112 bits (2x56 bits) and 168 bits (3x56 bits).

fn_encrypt_aes(@plaintext, @localkeyname, @password, @keybits) fn_decrypt_aes(@encryptedtext, @localkeyname, @password, @keybits)

The federal government recently adopted the Rijndael algorithm as the Advanced Encryption Standard (AES) for securing information within the federal government. It was designed specifically to replace the aging DES algorithm. AES encrypts in 16-byte blocks, so plain text that is a multiple of 16 bytes will have another 16 bytes appended to it. Make sure that your encrypted columns are 16 bytes longer than your plain text columns. If you supplied a password when creating your master key, you must supply the same password when calling the fn_encrypt_aes and fn_decrypt_aes functions.

AES supports three different encryption key sizes: 128-bit, 192-bit and 256-bit. Specify the keysize you wish to use with the @keybits parameter. The proper size of key is automatically extrapolated from the local key you specify by name.

The @plaintext and @encryptedtext parameters passed to these functions are VARBINARY, and the results returned are VARBINARY.

fn_encrypt_blowfish(@plaintext, @localkeyname, @password, @keybits) fn_decrypt_blowfish(@encryptedtext, @localkeyname, @password, @keybits)

Bruce Schneier's Blowfish algorithm supports key sizes of 32 to 448 bits, in multiples of 8 bits. As with the AES functions, you use the @keybits parameter to specify the key size in bits. Blowfish encrypts in 8 byte blocks, so ensure that your encrypted data column is 8 bytes larger than your plain text column. If you specified a password when creating your master key, you have to supply it here as well.

The @plaintext and @encryptedtext input for these functions are VARBINARY and the functions return VARBINARY data.

fn_encrypt_des (@plaintext, @localkeyname, @password) fn_decrypt_des (@encryptedtext, @localkeyname, @password)

The Data Encryption Standard (DES) was created by IBM and adopted by the federal government as a Federal Information Processing Standard (FIPS) in 1976. Since that time, it has become the most widely analyzed and researched encryption algorithm in the world. Only recently has DES begun to show its age, with several reported attacks and analyses exposing weaknesses in the algorithm.

DES uses a fixed key length of 56 bits, and encrypts in blocks of 8 bytes; so be sure that your data column is 8 bytes larger than your plain text column. The 56-bit key is automatically extrapolated from the local key you specify when these functions are called. If you supplied a password when creating your master key, you have to supply it here also.

The @plaintext input to these functions is VARBINARY data, and a VARBINARY result is returned.

fn_encrypt_twofish(@plaintext, @localkeyname, @password, @keybits) fn_decrypt_twofish(@encryptedtext, @localkeyname, @password, @keybits)

Twofish was created by Bruce Schneier and company as a submission for the National Institute of Standards and Technology (NIST) Advanced Encryption Standard competition. Although Rijndael was chosen as the AES, NIST announced that the other finalists in the competition (including Twofish) were adequate to secure unclassified information.

Twofish supports three different encryption key lengths: 128-bit, 192-bit and 256-bit. Specify the keysize you wish to use with the @keybits parameter. The proper size of key is automatically extrapolated from the local key you specify by name.

The @plaintext and @encryptedtext parameters passed to these functions are VARBINARY, and the results returned are VARBINARY.

Data Types

The Encryption and Decryption algorithms return data in VARBINARY format, of up to 8000 bytes. Because of padding considerations, the maximum size of plain text that can be passed into the encryption functions is 7,984 bytes.

NVARCHAR and NCHAR data can be encrypted as well using these routines; however, like any plain text passed into these routines, the data must first be cast to VARBINARY. After decryption, the VARBINARY returned must be cast to NCHAR or NVARCHAR. Because of the way NCHAR and NVARCHAR data is stored in memory, you must take the following considerations into account when encrypting/decrypting NCHAR or NVARCHAR data:

• NCHAR and NVARCHAR data is stored with two bytes per character. When cast to VARBINARY format, your NCHAR or NVARCHAR data will take up twice as much storage space.
• Because of the way NCHAR and NVARCHAR data are stored, you must cast the VARBINARY decryption results back to NCHAR or NVARCHAR. Casting the decrypted result back to plain old CHAR or VARCHAR can cause SQL Server to truncate the result.

Backups/Restores

Because the encryption master and local keys are stored in tables, backing up and restoring them is as easy as performing a normal database backup, or DTS Import/Export to the tables. Be careful to limit access to the key vault tables.

Examples

There are several sample scripts included in the \SAMPLES directory of the download file. Let’s discuss the first example from the DEMO_AES.SQL script.

First this script creates a table variable to hold our plain text and encrypted text, and then populates it with some data:

DECLARE @AES_Name_Test TABLE
        (Plain_Name VARCHAR(25) NOT NULL PRIMARY KEY,
         Enc_Name VARBINARY(50) NULL,
         Dec_Name VARCHAR(25) NULL,
         Success_Flag CHAR(1) NULL)
INSERT INTO @AES_Name_Test (Plain_Name)
SELECT 'JOSE'
UNION SELECT 'CESAR'
UNION SELECT 'RICHARD'
UNION SELECT 'ANTONIO'
UNION SELECT 'JENNY'
UNION SELECT 'JUSTINE'
UNION SELECT 'MAXINE'
...

Notice that the Enc_Name (encrypted name) column is VARBINARY, and that it is larger than the Plain_Name column. Do not use VARCHAR, CHAR or other related data types on your encrypted data columns... SQL has a nasty habit of stripping trailing white-space characters from these data types, and trailing whitespace is significant in encrypted data.

Next we call the fn_encrypt_aes function to encrypt our plain names. We are calling fn_encrypt_aes with the default local key (Local Key 1) that was created by the INSTALL.SQL script:

UPDATE @AES_Name_Test
SET Enc_Name = dbo.fn_encrypt_aes(CAST(Plain_Name AS VARBINARY(25)),
        'Local Key 1',
        NULL,
        256)

Notice that in this function call, we explicitly CAST the plain name from a VARCHAR to a VARBINARY. We also specify NULL in the @password parameter. The reason we don’t specify a password is because we did not specify one when creating the default master key. In this case the master key was auto-generated with no password by the INSTALL.SQL script.

Finally, we are setting the @keybits to 256 – the maximum key length for AES.

The next step is to decrypt the encrypted data:

UPDATE @AES_Name_Test
SET Dec_Name = CAST(dbo.fn_decrypt_aes(Enc_Name,
        'Local Key 1',
        NULL,
        256) AS VARCHAR(25))

Notice that we wrap the entire fn_decrypt_aes function call in a CAST to VARCHAR. Also notice that we are using the same key we used to encrypt the data (Local Key 1), and that the other options (@password and @keybits) are the same as for fn_encrypt_aes. If you use different values for decryption than the ones you used for encryption, you will end up with garbage results.

UPDATE @AES_Name_Test
SET Success_Flag =
        CASE WHEN Plain_Name = Dec_Name
        THEN 'Y'
        ELSE 'N' END

Here we run a simple test. The result of encryption and subsequent decryption needs to be the plain text we started with. If it's different, we have a problem. Because of that, we run a simple comparison test, checking Plain_Name against Dec_Name to make sure they are exactly the same. If not, 'N' is displayed.

SELECT *
FROM @AES_Name_Test

Finally, we SELECT the results to the screen. The results look like the following:

Fig. 3. Demo_AES.SQL Output

DBA Toolkit (Encryption) Quick Reference

*NOTE: The extended procedures are listed here for reference. While they can be called directly from your code, bypassing the user-defined functions and stored procedures that rely on them is not recommended.

Summary

This is the end of the first part of this series on the DBA Toolkit. In this article we talked about encryption/decryption and key management tools included in the toolkit. In Part Two we will discuss regular expressions.

Download the DBA Toolkit Here