Microsoft® SQL Server 2008 R2 Unleashed

Encrypting Columns Using a Passphrase

As our first example, let’s keep things simple and look at how to encrypt a column using a passphrase. To do so, let’s look at the Sales.CreditCard table, which currently stores card numbers in cleartext:

Credit card numbers really should not be stored in their cleartext form in the database, so to fix this, first create a copy of the Sales.CreditCard table and define the CardNumber_encrypt column as a varbinary(256) so you can store the encrypted credit card numbers in the column (encrypted columns in SQL Server 2008 can be stored only as varbinary values):

Now, you can populate the CreditCard_encrypt