SQL Field Encryption

If you need to encrypt some data for security in SQL here is how you go about it...

1. You need to create a master key in your database if one does not already exist
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourPassword';

2. Create a certificate.
CREATE CERTIFICATE YourCertificateName WITH SUBJECT = 'Some Description';

3. Create a symmetric key.
CREATE SYMMETRIC KEY YourKeyName WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE YourCertificateName;

4. To encrypt a field. Example below will encrypt each YourField column in table YourTable. NOTE: Be sure to set your encrypted fields to varbinary(128) so you can see the field value. Otherwise the field may appear blank.
OPEN SYMMETRIC KEY YourKeyName DECRYPTION BY CERTIFICATE YourCertificateName;
UPDATE YourTable SET YourField = EncryptByKey(Key_GUID('YourKeyName'), YourFieldName);
CLOSE SYMMETRIC KEY YourKeyName;

5. To decrypt your field. This will put the field YourField back to plain text in the output query.

OPEN SYMMETRIC KEY YourKeyName DECRYPTION BY CERTIFICATE YourCertificateName;

SELECT CONVERT(VARCHAR, DecryptByKey(YourField)
    AS 'Decrypted Field'
    FROM YourTAble;

 CLOSE SYMMETRIC KEY YourKeyName;