/*============================================================================
  File:     DataEncryptionWithBackupRestore.sql

  Summary:  This script sets up the ClinicDB database. This script 
			originally written as examples from SQL Team. Modifications 
			include, making script more readable as well as including
			the proper restore scenario to show recovery of databases
			that include encrypted data across two instances.

			Use the RestoreEncryptedDatabase.sql script as a follow-on
			to this one. 

  Date:     November 2005

  Version:	SQL Server 2005 RTM
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

-- The following command will ONLY work in SSMS if you are in
-- SQLCMD mode. Make sure to create a SQLskills directory! If you 
-- the !!mkdir it should turn a highlighted gray - to show SQLCMD
-- mode. 
-- !!mkdir c:\SQLskills

--DROP LOGIN Doc1 
--DROP LOGIN Doc2
--DROP DATABASE ClinicDB

USE master
go

--Create two logins that will be used for this demo
--Essentially logins for two doctors in a clinic
CREATE LOGIN Doc1 WITH PASSWORD = 'Yukon90!05r&k'
go
CREATE LOGIN Doc2 WITH PASSWORD = 'Yukon90!05r&k'
go

--Create a database that the clinic uses
CREATE DATABASE ClinicDB
go

--Switch to the clinic database
USE clinicDB
go

--Create users for to the doctors
CREATE USER Doc1
go

CREATE USER Doc2
go

--create a table for patient records
--The SSN and health problems of each patient is meant
--to be encrypted. Only the patients doctor can see their
--full details. Other doctors can see the patients name
--and which doctor is assigned to them

CREATE TABLE PatientTable 
(	Id int
	, name nvarchar(30)
	, Docname varchar(20)
	, SSN varbinary(60)
	, Problem varbinary(4000)
)
go

--Grant access to the table to both doctors
GRANT SELECT,INSERT ON PatientTable TO Doc1
GRANT SELECT,INSERT ON PatientTable TO Doc2

-- In order to allow encryption of data, we first
-- need to create a way in which the keys will be 
-- accessed. In this case (but this is not required)
-- we will use certificates for each of the doctors.

--Create a certificate for each doctor
CREATE CERTIFICATE Doc1Cert 
AUTHORIZATION Doc1 
WITH SUBJECT = 'OU=NorthshoreClinic,CN=Doc1cert'
go

--Notice the error, we need to create a database master key first. 
--So lets do that.
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = '3$4*8#s,jdng#jn35!y9u,pb9u.q92p)4iup(9ys:hmb'

--Now lets create the certificates
CREATE CERTIFICATE Doc1Cert 
AUTHORIZATION Doc1 
WITH SUBJECT = 'OU=NorthshoreClinic,CN=Doc1cert'
go

-- If you're interested in the format of these 
-- certificates, backup the cert and then review 
-- from Windows Explorer.
-- BACKUP CERTIFICATE Doc1Cert 
-- TO FILE = 'C:\SQLskills\Doc1Cert.cer'
go

CREATE CERTIFICATE Doc2Cert 
AUTHORIZATION Doc2 
WITH SUBJECT = 'OU=NorthshoreClinic,CN=Doc2cert'
go

-- If on Windows 2003 Server a better choice for 
-- encryption would be AES. However, AES is not
-- supported on Windows XP. TRIPLE_DES is best in
-- that case and will work across platforms.

--Create symmetric keys for each of the doctors
CREATE SYMMETRIC KEY Doc1Key 
AUTHORIZATION Doc1 
WITH ALGORITHM = TRIPLE_DES 
ENCRYPTION BY CERTIFICATE Doc1Cert
go

CREATE SYMMETRIC KEY Doc2Key 
AUTHORIZATION Doc2 
WITH ALGORITHM = TRIPLE_DES 
ENCRYPTION BY CERTIFICATE Doc2Cert
go

--View the list of keys in the database
SELECT * FROM sys.symmetric_keys

--Simulate connecting as Doctor1
EXECUTE AS LOGIN = 'Doc1'
--open your symmetric key for usage
OPEN SYMMETRIC KEY Doc1Key 
DECRYPTION BY CERTIFICATE Doc1cert;

--view the list of open keys in the session
--select * from sys.dm_exec_open_keys

--use the key to insert doctor's patient records
--including encrypted values into the table
INSERT INTO PatientTable VALUES 
	(1, 'Girish','Doc1'
		, encryptByKey(Key_GUID('Doc1Key'),'111-11-1111')
		, encryptByKey(Key_GUID('Doc1Key'),'Migraine')
	)

INSERT INTO PatientTable VALUES 
	(2, 'Tanmoy','Doc1'
		, encryptByKey(Key_GUID('Doc1Key'),'222-22-2222')
		, encryptByKey(Key_GUID('Doc1Key'),'Stomach Ache')
	)

INSERT INTO PatientTable VALUES 
	(3, 'James','Doc1'
		, encryptByKey(Key_GUID('Doc1Key'),'333-33-3333')
		, encryptByKey(Key_GUID('Doc1Key'),'Insanity')
	)

--close all opened keys
CLOSE ALL SYMMETRIC KEYS
go

--simulate logging out of doctor1's context
REVERT
go

--Simulate connecting as Doctor2
EXECUTE AS LOGIN = 'Doc2'

--open your symmetric key for usage
OPEN SYMMETRIC KEY Doc2Key 
DECRYPTION BY CERTIFICATE Doc2cert

--view the list of open keys in the session
--select * from sys.dm_exec_open_keys

--use the key to insert doctor's patient records
--including encrypted values into the table
INSERT INTO PatientTable VALUES 
	(4, 'Dave','Doc2'
		, encryptByKey(Key_GUID('Doc2Key'),'444-44-4444')
		, encryptByKey(Key_GUID('Doc2Key'),'Throat infection')
	)

INSERT INTO PatientTable VALUES 
	(5, 'Istvan','Doc2'
		, encryptByKey(Key_GUID('Doc2Key'),'555-55-5555')
		, encryptByKey(Key_GUID('Doc2Key'),'Asthma')
	)

INSERT INTO PatientTable VALUES 
	(6, 'Al','Doc2'
		, encryptByKey(Key_GUID('Doc2Key'),'666-66-6666')
		, encryptByKey(Key_GUID('Doc2Key'),'Eye Pain')
	)

--close all opened keys
CLOSE ALL SYMMETRIC KEYS
go

--simulate logging out of doctor1's context
REVERT
go

--select from table as an admin
SELECT * FROM PatientTable

-- Notice how the last two columns show up as encrypted/binary data
-- ONLY those that can "decrypt by" the keys with which the data
-- was encrypted, can decrypt it.
-- In this case an admin can "execute as" and use the certificates
-- authorized to these doctors but instead, the doctors could have
-- encrypted by a password and each time they use a client application
-- they could enter this password once and keep the key open for their
-- session. Additional client side options are available as well.

--simulate connecting as Doc1
EXECUTE AS LOGIN = 'Doc1'

--open the relevant keys for usage
OPEN SYMMETRIC KEY Doc1Key 
DECRYPTION BY CERTIFICATE Doc1Cert

--select from the table including decrypting from 
--encrypted columns
SELECT Id, name, Docname
	, convert(varchar,decryptbykey(SSN)) as SSN
	, convert (varchar,decryptbykey(Problem)) as Ailment 
FROM PatientTable

--simulate logging out
CLOSE ALL SYMMETRIC KEYS
go

REVERT
go

--simulate connecting as Doc1
EXECUTE AS LOGIN = 'Doc2'

--open the relevant keys for usage
OPEN SYMMETRIC KEY Doc2Key 
DECRYPTION BY CERTIFICATE Doc2Cert

--select from the table including decrypting from 
--encrypted columns
SELECT Id, name, Docname
	, convert(varchar,decryptbykey(SSN)) as SSN
	, convert (varchar,decryptbykey(Problem)) as Ailment 
FROM PatientTable

--simulate logging out
CLOSE ALL SYMMETRIC KEYS
go

REVERT
go

--now backup/restore on same server to same location, etc.
USE master
go

BACKUP DATABASE clinicDB 
	TO DISK = 'c:\SQLskills\ClinicDBBackup.bak'
	WITH INIT
go

RESTORE DATABASE clinicDB 
	FROM DISK = 'c:\SQLskills\ClinicDBBackup.bak'
	WITH REPLACE
go

USE ClinicDB
go

--simulate connecting as Doc1
EXECUTE AS LOGIN = 'Doc1'

--open the relevant keys for usage
OPEN SYMMETRIC KEY Doc1Key 
DECRYPTION BY CERTIFICATE Doc1Cert

--select from the table including decrypting from 
--encrypted columns
SELECT Id, name, Docname
	, convert(varchar,decryptbykey(SSN)) as SSN
	, convert (varchar,decryptbykey(Problem)) as Ailment 
FROM PatientTable

--simulate logging out
CLOSE ALL SYMMETRIC KEYS
go

REVERT
go

-- Single Instance restore works just fine.
-- What if you want to restore to another instance?
-- You really have one of two options:
---(1) Backup the Service Master Key and 
------    Restore SMK to second server
---(2) Open the DMK of the database and then
------    re-encrypt it with the second server's SMK

-- USE: RestoreEncryptedDatabase.sql