-- First, we'll create some test databases
USE master

-- drop any databases left around from previous run
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'encrypt_test1')
    DROP DATABASE encrypt_test1

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'encrypt_test2')
    DROP DATABASE encrypt_test2

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'encrypt_test3')
    DROP DATABASE encrypt_test3

-- create two test databases
CREATE DATABASE encrypt_test1
CREATE DATABASE encrypt_test2

-- Next, create a simple table in each
USE encrypt_test1
CREATE TABLE Color (Name NVARCHAR(255), EncryptedName VARBINARY(255))
USE encrypt_test2
CREATE TABLE Color (Name NVARCHAR(255), EncryptedName VARBINARY(255))
GO

-- get a random file name for our tests
DECLARE @FILE_NAME NVARCHAR(255)
DECLARE @SQL NVARCHAR(MAX)

SET @FILE_NAME = CAST(CAST((RAND() * 1000000) AS INT) AS NVARCHAR(255))

-- create master key the master key using a password
USE encrypt_test1
IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE [name] = 'CodeKey01')
    CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = 'asldkjfaseiorujkhadfjkleio203948as;ehfjk'

-- create certificate
IF NOT EXISTS
    (SELECT * FROM sys.certificates WHERE [name] = 'AimsCert01')
    CREATE CERTIFICATE AimsCert01
    WITH SUBJECT = 'Codes';

-- create symmettric key.  The KEY_SOURCE, ALGORITHM, AND IDENTITY_VALUE must be
-- specified, and be identical between databases.
IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE [name] = 'CodeKey01')
    CREATE SYMMETRIC KEY CodeKey01 
        WITH KEY_SOURCE = 'a;sli98754;uy98asjh',
            ALGORITHM = AES_256,
            IDENTITY_VALUE = '435987;klasjdf'
        ENCRYPTION BY CERTIFICATE AimsCert01;

-- store some encrypted data
DECLARE @Name NVARCHAR(255);
DECLARE @EncryptedName VARBINARY(255);

SET @Name = 'RED';

-- open/close the symmetric key surrounding all encrypted database activity
OPEN SYMMETRIC KEY CodeKey01 DECRYPTION BY CERTIFICATE AimsCert01;

SET @EncryptedName = EncryptByKey(Key_GUID('CodeKey01'), @Name);
INSERT INTO Color(Name, EncryptedName) VALUES(@Name, @EncryptedName);

CLOSE SYMMETRIC KEY CodeKey01;

-- verify the data was stored as expected
OPEN SYMMETRIC KEY CodeKey01 DECRYPTION BY CERTIFICATE AimsCert01;

SELECT Name, EncryptedName, CONVERT(NVARCHAR(255), DecryptByKey(EncryptedName)) AS DecryptedName FROM Color;

CLOSE SYMMETRIC KEY CodeKey01;

-- copy data into encrypt_test2
INSERT INTO encrypt_test2..Color(Name, EncryptedName)
    SELECT Name, EncryptedName
        FROM Color

-- retrieve data using encrypt_test1's key to verify
OPEN SYMMETRIC KEY CodeKey01 DECRYPTION BY CERTIFICATE AimsCert01;

SELECT Name, EncryptedName, CONVERT(NVARCHAR(255), DecryptByKey(EncryptedName)) AS DecryptedName FROM encrypt_test2..Color;

CLOSE SYMMETRIC KEY CodeKey01;

-- now, to share encrypted data between databases, we must
--    a) use the same SERVICE MASTER KEY
--    b) use the same DATABASE MASTER KEY
--    c) use the same CERTIFICATE
--    d) use the same SYMMETRIC KEY

-- this means we need to back up all of these.  Since our tests
-- are on the same server, we don't really need the SERVICE MASTER KEY,
-- but it's provided for completeness

-- backup SMK
SET @SQL = 'BACKUP SERVICE MASTER KEY 
    TO FILE = ''c:\encrypt_test\' + @FILE_NAME + '.smk''
    ENCRYPTION BY PASSWORD = ''3dH85Hhk003GHk2597gheij4'';'

EXEC sp_ExecuteSQL @SQL

-- backup DMK
SET @SQL = 'BACKUP MASTER KEY 
    TO FILE = ''c:\encrypt_test\' + @FILE_NAME + '.dmk'' 
    ENCRYPTION BY PASSWORD = ''sd092735kjn$&adsg'';'

EXEC sp_ExecuteSQL @SQL

-- backup CERT
SET @SQL = 'BACKUP CERTIFICATE AimsCert01 
    TO FILE = ''c:\encrypt_test\' + @FILE_NAME + '.cer''
    WITH PRIVATE KEY ( 
    FILE = ''c:\encrypt_test\' + @FILE_NAME + '.pvk'' , 
    ENCRYPTION BY PASSWORD = ''9n34khUbhk$w4ecJH5gh'' );'

EXEC sp_ExecuteSQL @SQL

-- restore SMK
-- NOTE!!!! DON'T DO THIS IF YOU ALREADY HAVE ANY ENCRYPTED DATA
--        ON THIS SERVER USING A DIFFERENT SERVICE MASTER KEY.  YOU
--        WILL NO LONGER BE ABLE TO DECRYPT ANY DATA ENCRYPTED USING
--        THE PREVIOUS SERVICE MASTER KEY
USE encrypt_test2
SET @SQL = 'RESTORE SERVICE MASTER KEY 
    FROM FILE = ''c:\encrypt_test\' + @FILE_NAME + '.smk''
    DECRYPTION BY PASSWORD = ''3dH85Hhk003GHk2597gheij4'';'

EXEC sp_ExecuteSQL @SQL

-- restore DMK
SET @SQL = 'RESTORE MASTER KEY 
    FROM FILE = ''c:\encrypt_test\' + @FILE_NAME + '.dmk''
    DECRYPTION BY PASSWORD = ''sd092735kjn$&adsg'' 
    ENCRYPTION BY PASSWORD = ''asldkjfaseiorujkhadfjkleio203948as;ehfjk'';'

EXEC sp_ExecuteSQL @SQL

-- We want to change the encryption mode to use the SERVICE MASTER KEY.
-- This way we don't have to open the MASTER KEY every time we want to
-- do any encryption.
OPEN MASTER KEY
    DECRYPTION BY PASSWORD = 'asldkjfaseiorujkhadfjkleio203948as;ehfjk';

ALTER MASTER KEY
    ADD ENCRYPTION BY SERVICE MASTER KEY;

CLOSE MASTER KEY;

-- restore CERT
SET @SQL = 'CREATE CERTIFICATE AimsCert01
    FROM FILE = ''c:\encrypt_test\' + @FILE_NAME + '.cer''
    WITH PRIVATE KEY (
    FILE = ''c:\encrypt_test\' + @FILE_NAME + '.pvk'', 
    DECRYPTION BY PASSWORD = ''9n34khUbhk$w4ecJH5gh'');'

EXEC sp_ExecuteSQL @SQL

-- create symmetric key.  NOTE: Use identical settings from above.
IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE [name] = 'CodeKey01')
    CREATE SYMMETRIC KEY CodeKey01 
        WITH KEY_SOURCE = 'a;sli98754;uy98asjh',
            ALGORITHM = AES_256,
            IDENTITY_VALUE = '435987;klasjdf'
        ENCRYPTION BY CERTIFICATE AimsCert01;

-- read data
OPEN SYMMETRIC KEY CodeKey01 DECRYPTION BY CERTIFICATE AimsCert01;

SELECT Name, EncryptedName, CONVERT(NVARCHAR(255), DecryptByKey(EncryptedName)) AS DecryptedName FROM Color;

CLOSE SYMMETRIC KEY CodeKey01;

-- Attempt to backup and restore a database and verify that you can
-- still read the encrypted data
USE master
SET @SQL = 'BACKUP DATABASE encrypt_test1
    TO DISK = ''c:\encrypt_test\' + @FILE_NAME + '.bak'';'

EXEC sp_ExecuteSQL @SQL

--SET @SQL = 'RESTORE FILELISTONLY
--    FROM DISK = ''c:\encrypt_test\' + @FILE_NAME + '.bak'';'
--
--EXEC sp_ExecuteSQL @SQL

-- restore as encrypt_test3
SET @SQL = 'RESTORE DATABASE encrypt_test3
    FROM DISK=''C:\encrypt_test\' + @FILE_NAME + '.bak''
    WITH MOVE ''encrypt_test1'' TO ''C:\encrypt_test\' + @FILE_NAME + '.mdf'',
    MOVE ''encrypt_test1_log'' TO ''C:\encrypt_test\' + @FILE_NAME + '.ldf'';'

EXEC sp_ExecuteSQL @SQL
GO
USE encrypt_test3

-- retrieve data from encrypt_test3 using its keys
OPEN SYMMETRIC KEY CodeKey01 DECRYPTION BY CERTIFICATE AimsCert01;

SELECT Name, EncryptedName, CONVERT(NVARCHAR(255), DecryptByKey(EncryptedName)) AS DecryptedName FROM Color;

CLOSE SYMMETRIC KEY CodeKey01;