Encryption of a field's value in a Linq-to-Sql

Question

I need to encrypt some fields on my Linq2Sql Entity. I would also like the process of encryption and decryption be transparent to the consumer of the entity, meaning that once the entity is loaded into memory the field is presented as regular value string (decrypted), but the same fields gets encrypted when being persisted to the database.

Answer 1

There is another option: you could "hide" the actual property with e.g. protected access modifier and the add a "fake" public property to the entity partial class which will encrypt/decript this internal in getter/setter, so it will be transparent to the consumer:

.dbml file:

<Column Name="Password" 
    Member="PasswordInternal" 
    AccessModifier="Protected" 
    Type="System.String" 
    DbType="Varchar(64) NOT NULL" 
    CanBeNull="false" />

and then in a partial class:

public partial class YourEntity
{
   public string Password
   {
        get
        {
            return Crypter.Decrypt(this.PasswordInternal)
        }
        set
        {
            this.PasswordInternal = Crypter.Encrypt(value)
        }
   }
}

Answer 2

well , SQL 2008 can encrypt a colunm of a table, and the app doesnt have to handle that. heres the link. mind you this has a performance price on the sql server`s CPU.