/*============================================================================
  File:     RestoreEncryptedDatabase.sql

  Summary:  This script is a follow-on to the DataEncryptionWithBackupRestore.sql
			script where the ClinicDB database is setup. Use this script as a 
			walk through in order to test how databases are restored.

  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.
============================================================================*/

--USE master
--go
--DROP DATABASE ClinicDB
--go
--DROP LOGIN Doc1
--go
--DROP LOGIN Doc2
--go

USE master
go

--You need the logins first... 
CREATE LOGIN Doc1 
WITH PASSWORD = 'Yukon90!05r&k'
go
CREATE LOGIN Doc2 
WITH PASSWORD = 'Yukon90!05r&k'
go

RESTORE DATABASE clinicDB 
	FROM DISK = 'c:\SQLskills\ClinicDBBackup.bak'
	WITH MOVE N'ClinicDB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\ClinicDBData.mdf'
		, MOVE N'ClinicDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\ClinicDBLog.LDF'
go

USE ClinicDB
go

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

-- FAILS WITH 
-- Msg 916, Level 14, State 1, Line 1
-- Server principal 'Doc1' is not able to access database 'clinicDB' 
-- under the current security context.

-- To "reconnect" the two SQL Authentication Logins...
EXECUTE sp_change_users_login  'Update_One', 'Doc1', 'Doc1'
EXECUTE sp_change_users_login  'Update_One', 'Doc2', 'Doc2'
-- to match up these two SQL Auth users...
go

-- try again...
--simulate connecting as Doc1
EXECUTE AS LOGIN = 'Doc1' -- we get past the previous error

--open the relevant keys for usage
OPEN SYMMETRIC KEY Doc1Key 
DECRYPTION BY CERTIFICATE Doc1Cert
-- Generates: Msg 15581, Level 16, State 1, Line 1
-- Please create a master key in the database or open the master key in the session before performing this operation.

REVERT
go

-- So, then we might think that maybe that will help...
USE ClinicDB
go

CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'j8#4u)tng(458u7$pjna;weit@g29P-UP9A3^5JHZRJ;SD'
go

--simulate connecting as Doc1
EXECUTE AS LOGIN = 'Doc1' -- we get past the previous error

--open the relevant keys for usage
OPEN SYMMETRIC KEY Doc1Key 
DECRYPTION BY CERTIFICATE Doc1Cert
-- Same error
-- Msg 15581, Level 16, State 1, Line 1
-- Please create a master key in the database or open the master key in the session before performing this operation.

CLOSE ALL SYMMETRIC KEYS
go

REVERT
go

-- So, for Option #2, we need to:
USE clinicDB
go

-- Open the DMK with the SAME password used when created:
OPEN MASTER KEY 
DECRYPTION BY PASSWORD = '3$4*8#s,jdng#jn35!y9u,pb9u.q92p)4iup(9ys:hmb'
go

-- Reassociate the DMK with the SMK of the new instance:
ALTER MASTER KEY 
ADD ENCRYPTION BY SERVICE MASTER KEY
go

-- Everything should work again!

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