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.