SQL Server Column Encryption

Question

I have a couple of columns in my application that need to encrypted for security reasons. How is this possible with SQL Server 2008? Also, would I need to always manually decrypt them or will it happen automatically?

Thanks,

Sachin

Answer 1

You can create a Certificate and then a Symmetric Key that uses the certificate:

CREATE CERTIFICATE YourCertificate
 WITH SUBJECT = 'Encrypted Content';
GO

CREATE SYMMETRIC KEY SSN_Key
  WITH ALGORITHM = AES_256
   ENCRYPTION BY CERTIFICATE YourCertificate;
GO

To Decrypt: You can use DecryptByKey:

The example from MSDN is:

SELECT CardNumber, CardNumber_Encrypted AS 'Encrypted card number',
   CONVERT(nvarchar,
   DecryptByKey(CardNumber_Encrypted, 1 , 
   HashBytes('SHA1', CONVERT(varbinary, CreditCardID)))) AS 'Decrypted card number'  
FROM Sales.CreditCard;

http://msdn.microsoft.com/en-us/library/ms179331(v=sql.105).aspx

Answer 2

Take a look MSDN post How to: Encrypt a Column of Data