Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


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

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.

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.

David Talbot is the vice president of development for Data Systems International, a company that develops case-management software for the social services industry. His experience ranges from license-plate recognition using neural networks to television set-top boxes to highly scalable Web applications. He is also the author of Applied ADO.NET.
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