Seamless SQL Server Encryption

The main customer-payment database (SQL Server 2000) at e-Money holds more than 2 million customer records and more than 16 million transaction records. These records contain sensitive credit-card and bank-account information that must be encrypted to protect customers' privacy and the company's property. Implementing an application-side encryption solution would be difficult because it would necessitate the rewrite of more than 50 core applications in ASP, Visual Basic 6.0 (VB6), and .NET. The company needed a seamless database server-side encryption solution that would have minimal impact on performance and business processes. I developed and implemented this solution based on the Microsoft .NET Framework and SQL Server extended stored procedures. The solution required minimal application modification and had minimal system-wide performance impact.

The Process

At first, I was planning to use an existing commercial product to achieve the goal. I evaluated the available SQL-encryption products, focusing mainly on three products: XP_Crypt from ActiveCrypt, DbEncrypt from Application Security, and Encryptionizer from NetLib. They all provided good data-encryption functionality, but none of them could provide the seamless data-encryption solution we needed. If we chose to go with one of them, we'd have to modify or completely rewrite all of our 50+ applications, and we just didn't have that kind of time and resources.

Because the Microsoft .NET Framework provides cryptographic services, I looked into .NET to see whether I could integrate its encryption support into SQL server. I figured out a way to use SQL Server's OLE Automation stored procedures to call a VB6 COM DLL, which was a wrapper for .NET DLLs. In this way, I integrated .NET's cryptographic services into SQL Server.

Meanwhile, I started working on a seamless encryption solution. The basic idea was to change an encrypted table's name and create a view of the table data under the table's old name. The view calls user-defined functions (UDFs) to decrypt the data so that the applications still have access to the unencrypted data. For data inserts, updates, and deletes, I used INSTEAD OF triggers on the view to encrypt the data in the tables.

I did encounter some problems as I was devising my solution. The basic idea for the seamless encryption solution was simple and straightforward, but in practice, it caused a lot of performance problems, especially on huge tables such as the customer and transaction tables, each of which contains millions of records. To solve those problems, I worked closely with my DBA. To make the seamless solution work, we had to come up with quite a few database-design tricks, such as creating additional columns to store duplicated data specifically for indexing.

The most important part of all encryption solutions is key management. My solution uses RSA to encrypt the encryption keys and stores the encrypted encryption keys in an XML file in the \system32 folder. RSA is a very strong asymmetric algorithm, so the encrypted encryption keys are safe, but RSA is extremely slow. (According to my tests, it's more than 1,000 times as slow as typical symmetric algorithms.) So I made the program decrypt the key the first time it's used and store the decrypted key in memory.

Another problem is related to encryption speed. The .NET symmetric algorithm I used, Rijndael-256, is pretty quick—about 20,000 encrypt/decrypt actions per second on my test machine. But when SQL Server calls the algorithm through OLE Automation stored procedures and the VB6 wrapper, encryption speed drops to about 200 per second. I later figured out that extended stored procedures are much faster than OLE Automation stored procedures at calling .NET DLLs and that using them would eliminate the VB6 COM wrapper DLL. So I wrote a Visual C++ DLL as extended stored procedures to implement encryption. This adjustment increased the encryption speed by 350 percent.

The Solution

As Figure 1 shows, the solution I implemented includes four basic components. First, it contains .NET DLLs based on the System.Security.Cryptography namespace for symmetric data-encryption processes (encrypt/decrypt/hash) and asymmetric algorithm−based encryption-key management. Second, a Visual C++ DLL wraps the .NET DLLs and exposes itself to SQL Server as extended stored procedures. A .NET WinForm application manages the encryption keys, and a .NET WinForm application dynamically generates SQL schema-change scripts for a seamless SQL Server-side solution.

Instead of modifying more than 50 homegrown applications, I had to modify only one ASP application and some Data Transformation Services (DTS) packages and stored procedures. The ASP application is the extranet site that our customer service representatives use; I had to modify it because it needs to search on encrypted data columns. Searching for a particular account number would cause the SQL Server to decrypt all encrypted account numbers, which would take more than an hour for e-Money's more than 2 million customer records. To fix this problem, I used a new column to store the last four digits of the account number, unencrypted, and modified the search query to use the new column to narrow down the search.

The Benefits

This solution provided stronger customer privacy and company property protection while complying with data privacy protection laws. It also reduced the costs for fixing potential data piracy. Under the California Database Protection Act of 2003 (CDPA), if a breach of unencrypted customer data happens, the company must notify all customers "in the most expedient time possible and without unreasonable delay." Therefore, by encrypting customer data, the company doesn't have to notify all customers. Because the solution is homegrown, the encrypted data is more difficult for intruders to compromise. Since the sensitive data is all encrypted, it's useless to unauthorized users. The solution incurs no noticeable performance hit, and e-Money saved more than $20,000 on the purchase of database-encryption products and even more money by not having to modify the existing applications.