Column level encryption in SQL Server 2005

A. Simple symmetric encryption

USE AdventureWorks;
GO

–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 = ‘23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj’
GO

CREATE CERTIFICATE HumanResources037
   WITH SUBJECT = ‘Employee Social Security Numbers’;
GO

CREATE SYMMETRIC KEY SSN_Key_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE HumanResources037;
GO

USE [AdventureWorks];
GO

— Create a column in which to store the encrypted data.
ALTER TABLE HumanResources.Employee
    ADD EncryptedNationalIDNumber varbinary(128);
GO

— Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;

— Encrypt the value in column NationalIDNumber with symmetric
— key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID(‘SSN_Key_01’), NationalIDNumber);
GO

— Verify the encryption.
— First, open the symmetric key with which to decrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;
GO

— Now list the original ID, the encrypted ID, and the
— decrypted ciphertext. If the decryption worked, the original
— and the decrypted ID will match.
SELECT NationalIDNumber, EncryptedNationalIDNumber
    AS ‘Encrypted ID Number’,
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
    AS ‘Decrypted ID Number’
    FROM HumanResources.Employee;
GO

B. Symmetric encryption that includes an authenticator

USE AdventureWorks;
–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 = ‘23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj’
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 Sales.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 Sales.CreditCard
SET CardNumber_Encrypted = EncryptByKey(Key_GUID(‘CreditCards_Key11’)
    , CardNumber, 1, HashBytes(‘SHA1’, CONVERT( varbinary
    , CreditCardID)));
GO

— Verify the encryption.
— First, open the symmetric key with which to decrypt the data.

OPEN SYMMETRIC KEY CreditCards_Key11
   DECRYPTION BY CERTIFICATE Sales09;
GO

— Now list the original card number, the encrypted card number,
— and the decrypted ciphertext. If the decryption worked,
— the original number will match the decrypted number.

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;
GO

 

Source : http://msdn.microsoft.com/en-us/library/ms179331.aspx

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s