Browse DevX
Sign up for e-mail newsletters from DevX


Implementing Encrypted SQL Server Database Columns with .NET : Page 2

Many government agencies needing HIPAA compliance, such as HUD, require encryption of certain database columns. For systems tracking victims of domestic abuse, it's critical to encrypt personally identifiable data. Fortunately, implementing encrypted database columns is simple using .NET and SQL Sever 2000.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date