Implementing Encrypted SQL Server Database Columns with .NET

Implementing Encrypted SQL Server Database Columns with .NET

In 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.

Introducing AES Encryption
AES (Advanced Encryption Standard) is the Federal Information Processing Standard (FIPS) encryption. The National Institute of Standards and Technology (NIST) selected the Rijndael algorithm for AES standard because it can withstand strong encryption breaking attacks yet supports fast software- and hardware-based implementations.

Even if you’re not concerned with compliance to federal standards, the Rijndael algorithm is an outstanding encryption algorithm and you should consider using it. To quote NIST’s AES Fact sheet:

Figure 2. Encrypted Data: The figure shows AES-encrypted data as stored in the database using the SQL Server Query Analyzer application.

“Assuming that one could build a machine that could recover a DES key in a second (i.e., could test 2^55 keys per second) it would take that machine approximately 149 thousand-billion (149 trillion) years to crack a 128-bit AES key. To put that into perspective, the universe is believed to be less than 20 billion years old.”

In lay terms, AES is exceptionally secure. Figure 2 shows an example of how the encrypted data looks in the database.

Simplifying .NET’s Encryption API
.NET’s encryption API is completely stream-based, making it incredibly flexible, but using it is far more complex than you might wish for encrypting and decrypting short character strings. Fortunately it’s very easy to create a wrapper around these stream-based methods to yield a simple?string encryption class for your application as shown in Listing 1.

In the constructor, the SimpleAES class creates an instance of RijndaelManaged (the algorithm used for AES) and then creates encryptor and decryptor transforms using the key and Initialization Vector (IV) passed to it. The code XORs the IV with the first block of the value to be encrypted before encryption actually takes place. The purpose of this exclusive OR is to keep the encrypted values unique even when the values to be encrypted are the same. For an illustration of this process take a look at Figure 3.

Figure 3. The AES Encryption Process: The figure shows the steps in the AES encryption process.

Since you will be encrypting and decrypting text, you will also need a UTF8Encoding object to translate the text to bytes and vice versa since the RijndaelManaged API only works on bytes.

The encrypt and decrypt methods work the same basic way, so I’ll provide the step by step description only for the encrypt method. First, the encrypt method takes the text value passed in and translates it to a byte array using the instance of the UTF8Encoding object created in the constructor. Next, you create a memory stream to pass to a CryptoStream using the EncryptorTransform object created in the constructor. Then you write the UTF encoded bytes to the stream. The act of writing the bytes to the stream performs the encryption.

At this point in the code, the MemoryStream object holds the encrypted bytes, so all you have to do is read the data back out of the MemoryStream and return the encrypted byte array to the caller.

Applying AES Encryption to Databases
AES encryption requires a key and an initialization vector (“IV” in cryptospeak). You cannot encrypt or decrypt anything in AES without having both the key and the vector. From this standpoint, you can keep the encryption key private to your application and generate different IVs for each record you store in the database.

It’s best to have different IVs for each record to prevent people from figuring out what one value means (even though it’s garbled) and then drawing conclusions about the content of similar records. For example, if you encrypt “ABCD” using an encryption key and it changes it to “!S(*Z”, and at a later date you encrypt another “ABCD” record, the second record will also be “!S(*Z”?if you used the same key to encrypt it. The value is still encrypted, but someone can use a little deductive reasoning while looking at encrypted values in the database to infer the content of values in other records.

So, if you have a column called ShelterName that stores the name of the battered women’s shelter the client is staying in, and a person browsing your back end data knows that Client X is in the “Fourth Street Shelter” and that the encrypted value stored in that column is “&@ASD&*A!” then that person also knows that all clients whose record contains “&@ASD&*A!” in the ShelterName column are also staying in that shelter. This defeats the point of encrypting the column in the first place. Using a separate IV for each record?makes the encrypted value unique for each record.

In the Client table, you want to be able to search on LastName so you can store the LastName using a shared IV, but for the rest of the columns you can store the data using AES with a unique IV for each record. Since all records encrypted with the same encryption key and IV will have the same binary value, this will allow you to search on the LastName column.

Column Data Type Nullable
ClientIDInt, IdentityNo

This solution lets you search on the client’s last name but not on any of the other fields, and it encrypts all the fields other than last name in a way that makes the encrypted value unique on a per record basis?even if they share values with other records. It’s perfectly safe to store the IV value in each record because your application holds the encryption key. Because the database doesn’t contain any copy of the encryption key, someone with access to the database would still need your application to decrypt the values. The IV exists merely to keep the encrypted values stored in your database unique even when the value stored is the same.

Encrypting and Storing a Record
To store the data in the database, you first generate the private IV for the record. This will be a unique IV for that record and will keep the encrypted values in the table unique. Then you will store the IV, the columns uniquely encrypted with that IV and the LastName using the shared IV for searching.

Author’s Note: The data access code used in this article uses the Microsoft Data Access Application Block. If you’re not familiar with it, I strongly recommend going to and downloading it. It comes complete with source code and provides a simpler and cleaner API for most data access tasks you’ll want to do with .NET.
   Private void SaveButton_Click(   object sender, System.EventArgs e)   {      object clientID = System.DBNull.Value;         if(this.ClientIDField.Text!="") {          clientID=Convert.ToInt32(         this.ClientIDField.Text); }         byte[] PrivateVector =          SimpleAES.GenerateEncryptionVector();      SimpleAES privateAES = new          SimpleAES(this.Key, PrivateVector);         SqlHelper.ExecuteNonQuery(         DatabaseConnectionString,         "SetClient",         new SqlParameter("@ClientID",             clientID),         new SqlParameter(            "@FirstNameCrypted",             privateAES.Encrypt(            this.FirstNameField.Text)),         new SqlParameter("@LastNameCrypted",      privateAES.Encrypt(      this.LastNameField.Text)),         new SqlParameter("@LastNameShared",             lib.Encrypt(            this.LastNameField.Text)),         new SqlParameter("@Vector",             PrivateVector),         new SqlParameter("@ShelterName",             privateAES.Encrypt(            this.ShelterNameField.Text)));   }  

Finding and Decrypting a Record
Since the encryption scheme uses a shared IV on the LastName field, if a user types in a last name exactly the same as a previously encrypted value, it will yield the same encrypted value, thus making it searchable. The stored procedure below performs a simple SELECT statement on the LastNameShared column using the @LastName parameter as a varbinary value rather than the text value you would use for an un-encrypted column.

   CREATE PROCEDURE [dbo].[FindClient]    @LastName varbinary(100)   AS   BEGIN      SELECT ClientID, Vector, FirstNameCrypted, LastNameCrypted, LastNameShared, ShelterName         FROM Clients WHERE LastNameShared=@LastName   END   GO   

In the application’s code, you take the value the user typed in the last name field, encrypt it using the shared IV and pass it to the FindClient stored procedure. If you get a result, then the first thing you will need to do is get the IV stored on that record and create a new instance of SimpleAES using the encryption key and the IV that you got from the database for that record. You will then use this instance of SimpleAES to decrypt each of the columns and display the result to the user.

Author’s Note: While you can create indexes on varbinary columns, keep in mind that the encrypted columns for which you’re using a private IV can’t be searched; therefore it’s a waste of time to index them. Only index the columns that use a shared IV.
      private void FindByLastNameButton_Click(object sender, System.EventArgs e)   {      using(SqlDataReader reader =          SqlHelper.ExecuteReader(DatabaseConnectionString,          "FindClient",         new SqlParameter("@LastName",             lib.Encrypt(this.LastNameField.Text))))      {      if(reader.Read())      {      byte[] PrivateVector =          reader.GetSqlBinary(         reader.GetOrdinal("Vector")).Value;      SimpleAES privateAES = new SimpleAES(         this.Key, PrivateVector);      this.ClientIDField.Text=reader.GetValue(         reader.GetOrdinal("ClientID")).ToString();      this.FirstNameField.Text=privateAES.Decrypt(         reader.GetSqlBinary(         reader.GetOrdinal("FirstNameCrypted")).Value);      this.LastNameField.Text=privateAES.Decrypt(         reader.GetSqlBinary(         reader.GetOrdinal("LastNameCrypted")).Value);      this.ShelterNameField.Text=privateAES.Decrypt(         reader.GetSqlBinary(         reader.GetOrdinal("ShelterName")).Value);      }      reader.Close();      }   }

Taking It Further
Feel free to use the downloadable source code and database included with this article for study, but keep in mind that you have considerable work still to do before putting encrypted columns into practice, most notably determining where to store and manage your private encryption keys. It’s definitely a lot more work to design your application to support encrypted columns, but if you’re storing data that could cost someone their life if revealed, it’s a critical must.


About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist