Encrypt your password and save to database

There are two sets of functions provided by MS SQL Server to encrypt and decrypt your data. I am using MS SQL 2008, But I think 2005 provide the same functionality. What I want to do is insert my data into database in encrypted form and when I get it out, it is un-encrypt data. Kind of common thing people would like to do. For in-deep explaination, please see these great articles from 4GuysFromRolla (Article1, Article2), I always get great walk-through there. I am going to just use one from two sets because they are pretty much the same. And something different from most examples you’ve got from web resource, I am doing it in C# code, not T-SQL code. Because I really don’t like to use Store-Procedure. Wherever you insert your data with SqlCommand, use this to pass parameters.

//Instead of
sqlCmd.CommandText = "...@ColumnName...";
//Use this one
sqlCmd.CommandText = "...EncryptByPassPhrase(@PassPhrase,@ColumnName)...";

Wherever you want to get encrypted data out, do this

//Instead of
sqlCmd.CommandText = "...TableName.ColumnName...";
//Use this one
sqlCmd.CommandText = "...CONVERT(ColumnDataType, DecryptByPassPhrase(@PassPhrase, TableName.ColumnName) ) AS ColumnName)...";

PassPharse is a string that you want to use as a key to mask your data. That’s all, cheers.


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s