Implementing Encrypted SQL Server Database Columns with .NET

n a normal application development environment, you will likely need to use encrypted columns to store passwords if you are using a custom users table. The need for encrypted columns is even greater in the world of HIPPA compliant applications. HIPPA requires encryption of any data transmitted over public networks. Fortunately HTTPS handles that requirement quite well.

Although HIPPA doesn't explicitly require encrypted database columns, many government agencies that derive their requirements from HIPPA, such as HUD, do require encrypted columns on client-identifiable data.

Some folks out there are rolling their eyes a bit at requiring encrypted database columns, but it really is good policy for extremely sensitive data. For example, victims of domestic violence need to know they can get help from a domestic violence shelter and that any data gathered will be so secure that only people who truly need to know which shelter they are staying at have access to that information. Encrypted columns help ensure that even if someone physically steals the database, the data is secure. Not even the database administrator can tell anyone what shelter the victim is staying at.

Figure 1. Sample Tracking Application: The sample application tracks client names and shelter locations, but stores its data in an encrypted format.

In this article, you'll see how to create a simple application that is capable of loading, creating, and updating encrypted records as shown in Figure 1. This simple Windows Forms application will track the client's first name, last name, and the shelter they are staying in and keep the data encrypted in the database.

Limitations on Encrypted Columns
Some security paranoid folks out there are probably thinking, great I'll encrypt all of my columns, but—trust me—that's the last thing you want to do. Encrypting columns can have severe application performance and functionality limitations.

The performance penalty arises because encrypted values are stored in binary format rather than text or numeric format. In addition, the binary column will also be larger (requiring more storage space) than the data type of the actual data you are storing.

From an application-functionality standpoint, searching and reporting on encrypted data is problematic at best. If you're encrypting passwords, you're probably using a one way method such as MD5. Even with MD5, you have to have the entire value you want to search for; otherwise you won't be able to search on the encrypted value—in other words, you can't search for partial matches. If you need to be able to read the data back out later to display to the user, you'll need to use a two-way (symmetric) encryption method such as AES. Even with symmetric encryption, there is no practical way to do partial searches; you'll need the entire value to execute your search.

Third-party products offer whole-database encryption, but fail the requirement for preventing DBAs from seeing the contents of the database while still being able to administer and maintain it.