TestSql2012AesEnc_EncryptColumn.sql

USE TestDb
;
--If there is no master key, create one now. 
IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY 
  PASSWORD = 'WeakPassword1234'
GO

CREATE CERTIFICATE Sales09
   WITH SUBJECT = 'Customer Credit Card Numbers';
GO

CREATE SYMMETRIC KEY CreditCards_Key11
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Sales09;
GO

-- Create a column in which to store the encrypted data.
ALTER TABLE dbo.CreditCard 
    ADD CardNumber_Encrypted varbinary(128); 
GO

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE Sales09;

-- Encrypt the value in column CardNumber using the
-- symmetric key CreditCards_Key11.
-- Save the result in column CardNumber_Encrypted.  
UPDATE dbo.CreditCard
--SET CardNumber_Encrypted = CONVERT(varbinary, null) -- to delete and retry
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')
    , CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary
    , CardNumber))) -- hashing with self is pointless but just for testing
WHERE CardNumber = '1'
GO

SELECT * from dbo.CreditCard
WHERE CardNumber = '1'