Encryption demo for SQL Server 2012

EncryptionPOC.sql

-- Create a Database Master Key
IF NOT EXISTS
(
    SELECT
        SK.*
    FROM
        sys.symmetric_keys AS SK
    WHERE
        SK.symmetric_key_id = 101
)
BEGIN
    -- http://msdn.microsoft.com/en-us/library/ms174382.aspx
   CREATE MASTER KEY
   ENCRYPTION BY PASSWORD = 'pass@word1';
END
GO

-- Encrypt the database master key (DMK) with the server master key (SMK)
-- http://msdn.microsoft.com/en-us/library/ms186937.aspx
ALTER MASTER KEY
ADD ENCRYPTION
BY SERVICE MASTER KEY;

GO

-- Export the databse master key so we can recover
--
-- From BOL
-- The master key must be open and, therefore, decrypted before it is
-- backed up. If it is encrypted with the service master key, the master
-- key does not have to be explicitly opened. But if the master key is
-- encrypted only with a password, it must be explicitly opened.
--
-- We recommend that you back up the master key as soon as it is
-- created, and store the backup in a secure, off-site location.

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

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'pass@word1';
BACKUP MASTER KEY
    TO FILE = 'c:\temp\exportedmasterkey'
    ENCRYPTION BY PASSWORD = 'ADifferentPass@word1';

GO

IF NOT EXISTS
(
    SELECT
        AK.*
    FROM
        sys.asymmetric_keys AS AK
    WHERE
        AK.name = 'STORE_CRYPT_ASYM'
)
BEGIN
    CREATE ASYMMETRIC KEY
        STORE_CRYPT_ASYM
    WITH ALGORITHM = RSA_2048;
END

IF NOT EXISTS
(
    SELECT
        SK.*
    FROM
        sys.symmetric_keys AS SK
    WHERE
        SK.name = 'STORE_CRYPT'
)
BEGIN
    -- http://msdn.microsoft.com/en-us/library/ms188357.aspx
    CREATE SYMMETRIC KEY
        STORE_CRYPT
    WITH ALGORITHM = AES_256
    ENCRYPTION BY ASYMMETRIC KEY STORE_CRYPT_ASYM;
END

IF NOT EXISTS
(
    SELECT
        S.*
    FROM
        sys.sysusers AS S
    WHERE
        S.name = 'DeCryptColumns'
        AND S.issqlrole = 1
)
BEGIN
    CREATE ROLE DeCryptColumns;
    GRANT VIEW DEFINITION ON SYMMETRIC KEY::STORE_CRYPT TO [DeCryptColumns];
    GRANT CONTROL ON ASYMMETRIC KEY::STORE_CRYPT_ASYM TO [DeCryptColumns];

    -- These members will be able to decrypt the data
    ALTER ROLE [DeCryptColumns] ADD MEMBER [domain\SomeGroup];


END

IF NOT EXISTS (SELECT * FROM sys.schemas AS SS WHERE SS.schema_id = SCHEMA_ID('Crypt'))
BEGIN
    EXECUTE (N'
    CREATE SCHEMA
        Crypt AUTHORIZATION DeCryptColumns;');
END
GO

--------------------------------------------------------------------------------
-- Computed column to automagically decrypt
--------------------------------------------------------------------------------
CREATE TABLE
    Crypt.Employee
(
    EmployeeID varchar(11) NOT NULL PRIMARY KEY
    -- This is the raw binary value of a SSN
,   SSN_bin varbinary(68) NOT NULL 
    -- Compute an MD5 hash of the SSN + a salt to allow for quick match
    -- without the need for a decrypted value.  
,   SSN_hash char(30) NOT NULL
    -- Show the last four of the SSN in the open
,   SSN_visible AS
    RIGHT(CONVERT(varchar(20), DECRYPTBYKEYAUTOASYMKEY(ASYMKEY_ID('STORE_CRYPT_ASYM'), NULL, SSN_bin)),4)
    -- Full SSN decrypted
,   employee_ssn AS
    CONVERT(varchar(20), DECRYPTBYKEYAUTOASYMKEY(ASYMKEY_ID('STORE_CRYPT_ASYM'), NULL, SSN_bin))
);


--------------------------------------------------------------------------------
-- POC loading encrypted data to table
--------------------------------------------------------------------------------
OPEN SYMMETRIC KEY
    STORE_CRYPT
DECRYPTION BY ASYMMETRIC KEY 
    STORE_CRYPT_ASYM;

INSERT INTO
    Crypt.Employee
(
    EmployeeID 
,   SSN_bin 
,   SSN_hash
)
SELECT
    '12345678901' AS EmployeeID
,   ENCRYPTBYKEY(KEY_GUID('STORE_CRYPT'), D.ssn) AS SSN_bin
,   CONVERT(char(30), HASHBYTES('MD5', CONCAT(S.SaltValue, RTRIM(D.ssn))), 1) AS SSN_hash
FROM
(
    SELECT '111223333' AS ssn
) D (ssn)
CROSS APPLY
(
    SELECT 'Salt, Salt, where the fine salt'
) S (SaltValue);

-- Pull the inserted value back out
SELECT
    CE.EmployeeID
,   CE.SSN_bin
,   CE.SSN_hash
,   CE.SSN_visible
,   CE.employee_ssn
FROM
    Crypt.Employee AS CE;