Download the Toolkit.

Introduction

"When cryptography is outlawed, bayl bhgynjf jvyy unir cevinpl."

-- Kevin McCurleys Thought for the day, June 24, 1997

This article demonstrates how to take advantage of SQL Server extended stored procedures to help protect your confidential data.

A Very, Very Short History of Cryptography

Cryptography has been around for thousands of years. It was reported that even Caesar encrypted sensitive military messages before sending them out. Caesar used a method of encryption that has come to be known as a "Caesar Shift", named in his honor. The Caesar Shift is quite simple. Just pick a number, then shift each of the letters in your message by that amount. The most common method, using the Modern Latin alphabet, is ROT13. Using this method, you rotate each letter forward 13 letters. You end up with a garbled message, like this:

This is a sample of ROT13 "Caesar Shift" encryption

Doing ROT13 on an ROT13-encrypted message will give you back your original message. This is the basis of the entire 'secret decoder ring' industry as we know it today.

EXEC master..xp_rot13 @text [, @encrypted OUTPUT]

Encoding

Encoding (not to be confused with Encrypting) is the conversion of data from one format to another, without changing the underlying characteristics of the message. As an example, when you convert the number 256 (decimal) to 0x100 (hexadecimal), you are not changing the actual number; you are just representing it in a different format. This is the essence of encoding. The method of encoding that I chose for the SQL Encryption Toolkit is Base64 encoding. I chose this method because it is more compact than equivalent hexadecimal strings, and it only includes printable characters (important if you are going to store your encrypted data in CHAR or VARCHAR columns). If you want to learn more about Base64, there's a pretty good article over at Wikipedia (http://en.wikipedia.org/wiki/Base64). A technical discussion of the details of Base64 are outside the scope of this article. Just keep in mind that Base64 encoded messages use up about 33% more storage space than straight binary data. Be sure to account for that when you encode/decode Base64 messages.

EXEC master..xp_base64encode @plaintext [, @encodedtext OUTPUT]
EXEC master..xp_base64decode @encodedtext [, @plaintext OUTPUT]

BlowFish

For this article, I decided on the following criteria for my chosen encryption algorithm:

• It had to be well-established and reasonably secure
• It had to be freely available for public use and distribution
• It had to be able to use a good-sized encryption key
• The algorithm had to be fast

I finally decided on Bruce Schneier's BlowFish encryption algorithm (http://www.schneier.com/blowfish.html). BlowFish has been around since the early 1990s and is considered reasonably secure, it is available for public use and redistribution without royalties or licensing restrictions, it uses a variable length encryption key of up to 448 bits (56 bytes), and it is fast.

I won't go into a detailed explanation of how BlowFish operates internally; for those interested in pursuing the subject in greater detail, Mr. Schneier's website has plenty of further reading: http://www.schneier.com/blowfish.html.

BlowFish and SQL Server

I decided to encapsulate the BlowFish algorithm for SQL Server 2000 in an extended stored procedure primarily for speed purposes. BlowFish (and most encryption algorithms) require a lot of bit-level manipulations for which C and C++ are optimized. Trying to implement these bit-level manipulations in T-SQL would bring your SQL Server to a crawl.

Again, I won't dive too deep into the details of the implementation. The source code is available for those who wish to take a look; whether you just to see how it does what it does, or if you're more adventurous and want to improve on it. The actual BlowFish algorithm itself is implemented entirely - with very, very few minor modifications - from the source code made available by Bruce Schneier http://www.schneier.com/blowfish-download.html. Mr. Schneier makes the source code available in several other languages at his site if you are interested in continuing researching BlowFish, but aren't familiar with C/C++.

EXEC master..xp_blowfishencrypt @plaintext, @key [, @encryptedtext OUTPUT]
EXEC master..xp_blowfishdecrypt @encryptedtext, @key [, @plaintext OUTPUT]
EXEC master..xp_generatekey @bits [, @key OUTPUT]

The @key parameter is always a Base64 encoded encryption key. You can use xp_base64encode to encode your own plaintext key, or use xp_generatekey to generate a random one for you.

xp_blowfishencrypt takes a plain text message and encrypts it using your key. The result is Base64-encoded.

xp_blowfishdecrypt takes a Base64 encoded, previously encrypted message and decrypts it using your key. You must use the same key to encrypt and decrypt a message.

xp_generatekey takes the # of bits as input. This # needs to be between 32 and 448 for BlowFish, and it must be a multiple of 8 (i.e., 128, 192, 200, etc.) The returned value is an encryption key in Base64 format.

Install and Use

The tools provided with this article are extended stored procedures that encrypt and decrypt CHAR and VARCHAR variables/columns of a database using the BlowFish algorithm. To install them, follow these directions:

1. Copy all of the DLLs from the \Install directory of the ZIP file to your Microsoft SQL Server\MSSQL\Binn directory.
2. In Query Analyzer, run the ADD_XP.SQL script from the \Install directory of the ZIP File.

The ADD_XP.SQL script adds all of the above mentioned extended stored procs to your master database. In addition, it adds two user defined functions to the master database:

master.dbo.udf_blowfishencrypt(@plaintext, @key)
master.dbo.udf_blowfishdecrypt(@encryptedtext, @key)

These UDF's wrap the xp_blowfishencrypt and xp_blowfishdecrypt extended stored procedures, so that you can use them in queries.

I've included several sample scripts in the ZIP file, under the \Sample_SQL directory of the ZIP file.

Download the toolkit.

Conclusion

This toolkit is just a basic toolkit for SQL Server 2000 DBA's and Developers. It demonstrates many concepts, including how to create your own extended stored procedures, and how to use XP's to help make your SQL Server more secure. This is just a start, and the toolkit can definitely be improved upon in the future. I'm already considering adding AES encryption, and will decide based on the feedback from the readers. If you want more features added to the toolkit, post a message for me on the board and we'll look into it for the next round.

Finally, I'd like to thank Bruce Schneier, whose BlowFish algorithms and source code made this possible; and to Anders Molin for his Base64 encoding/decoding algorithm.

Please note that I have tested these routines with CHAR and VARCHAR data only. Your mileage may vary with other SQL data types.

Further Reading

• Bruce Schneier's BlowFish Website
• Anders Molin's AMMimeUtils
• Wikipedia Base64
• SSA Website