/*============================================================================
  File:     EncryptionPerfNumbers.sql

  Summary:  This script creates keys with all different supported
			types of encryption used. It also creates a table in
			the master database into which performance numbers can
			be logged. You should use this script ONLY in a testing
			environment AND you must be running on Windows 2003 server
			in order to support all encryption types. AES is not 
			supported on Windows XP.

  Date:     July 2005

  Tested on SQL Server Version: 9.00.1187.07 (IDW15 - June CTP)
------------------------------------------------------------------------------
  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.
============================================================================*/


SET NOCOUNT ON
go

USE master
go

DROP LOGIN Kimberly 
DROP DATABASE TestEncryption
go

DROP TABLE dbo.MasterEncryptionPerformanceTestNumbers
go

CREATE TABLE dbo.MasterEncryptionPerformanceTestNumbers
(
	TestNum				int		IDENTITY,
	TestEncryptionType	char(6),
	TestTime			int
)
-- GRANT INSERT, SELECT 
-- ON MasterEncryptionPerformanceTestNumbers 
-- TO public
go

CREATE LOGIN Kimberly 
WITH PASSWORD = 'Rafal''s Code Monkey 2005@TE-AMS!'
go

CREATE DATABASE TestEncryption
	ON PRIMARY 
	(NAME = N'TestEncryptionData', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\TestEncryptionData.MDF' , 
	SIZE = 10,
	FILEGROWTH = 10%,
	MAXSIZE = 15)
LOG ON 
	(NAME = N'TestEncryptionLog', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\TestEncryptionLog.LDF' , 
	SIZE = 100, FILEGROWTH = 10%)
go

ALTER DATABASE [TestEncryption] ADD FILEGROUP [fg] 
go

ALTER DATABASE [TestEncryption] 
	ADD FILE
		(NAME = N'TestEncryptionFile1', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\TestEncryptionFile1.NDF' , 
		SIZE = 60, FILEGROWTH = 10%) 
		TO FILEGROUP [fg]
go

ALTER DATABASE [TestEncryption] 
	ADD FILE
		(NAME = N'TestEncryptionFile2', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\TestEncryptionFile2.NDF' , 
		SIZE = 60, FILEGROWTH = 10%) 
		TO FILEGROUP [fg]
go

ALTER DATABASE [TestEncryption] 
	ADD FILE
		(NAME = N'TestEncryptionFile3', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\TestEncryptionFile3.NDF' , 
		SIZE = 60, FILEGROWTH = 10%) 
		TO FILEGROUP [fg]
go

ALTER DATABASE [TestEncryption] 
MODIFY FILEGROUP [FG] DEFAULT
go

USE TestEncryption
go

CREATE USER Kimberly 
go

CREATE SYMMETRIC KEY DESKey
AUTHORIZATION Kimberly
WITH ALGORITHM = DES, 
KEY_SOURCE = 'K2I#M5B5E^R8L9Y#L6O7V2E3S2R6A&F@A$L!'
ENCRYPTION BY PASSWORD = 'R$A&F3A^L1L9O3V)E%S#K8I3M$B1E6R4L&Y!'
go

CREATE SYMMETRIC KEY [3DESKey]
AUTHORIZATION Kimberly
WITH ALGORITHM = TRIPLE_DES, 
KEY_SOURCE = 'K2I#M5B5E^R8L9Y#L6O7V2E3S2R6A&F@A$L!'
ENCRYPTION BY PASSWORD = 'R$A&F3A^L1L9O3V)E%S#K8I3M$B1E6R4L&Y!'
go

CREATE SYMMETRIC KEY AES128Key
AUTHORIZATION Kimberly
WITH ALGORITHM = AES_128, 
KEY_SOURCE = 'K2I#M5B5E^R8L9Y#L6O7V2E3S2R6A&F@A$L!'
ENCRYPTION BY PASSWORD = 'R$A&F3A^L1L9O3V)E%S#K8I3M$B1E6R4L&Y!'
go

CREATE SYMMETRIC KEY AES192Key
AUTHORIZATION Kimberly
WITH ALGORITHM = AES_192, 
KEY_SOURCE = 'K2I#M5B5E^R8L9Y#L6O7V2E3S2R6A&F@A$L!'
ENCRYPTION BY PASSWORD = 'R$A&F3A^L1L9O3V)E%S#K8I3M$B1E6R4L&Y!'
go

CREATE SYMMETRIC KEY AES256Key
AUTHORIZATION Kimberly
WITH ALGORITHM = AES_256, 
KEY_SOURCE = 'K2I#M5B5E^R8L9Y#L6O7V2E3S2R6A&F@A$L!'
ENCRYPTION BY PASSWORD = 'R$A&F3A^L1L9O3V)E%S#K8I3M$B1E6R4L&Y!'
go

CREATE SYMMETRIC KEY RC2Key
AUTHORIZATION Kimberly
WITH ALGORITHM = RC2, 
KEY_SOURCE = 'K2I#M5B5E^R8L9Y#L6O7V2E3S2R6A&F@A$L!'
ENCRYPTION BY PASSWORD = 'R$A&F3A^L1L9O3V)E%S#K8I3M$B1E6R4L&Y!'
go

CREATE SYMMETRIC KEY RC4Key
AUTHORIZATION Kimberly
WITH ALGORITHM = RC4, 
KEY_SOURCE = 'K2I#M5B5E^R8L9Y#L6O7V2E3S2R6A&F@A$L!'
ENCRYPTION BY PASSWORD = 'R$A&F3A^L1L9O3V)E%S#K8I3M$B1E6R4L&Y!'
go

SELECT * FROM sys.symmetric_keys
go

CREATE ASYMMETRIC KEY RSAKey
AUTHORIZATION Kimberly
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = 'R$A&F3A^L1L9O3V)E%S#K8I3M$B1E6R4L&Y!'
go

SELECT * FROM sys.asymmetric_keys
go

CREATE TABLE TestDES
(
	TestDESID	int	IDENTITY,
	EncryptCol	varbinary(1024)
)
go

GRANT SELECT, INSERT, UPDATE, DELETE
ON TestDES to Kimberly
go

CREATE TABLE Test3DES
(
	TestDESID	int	IDENTITY,
	EncryptCol	varbinary(1024)
)
go

GRANT SELECT, INSERT, UPDATE, DELETE
ON Test3DES to Kimberly
go

CREATE TABLE TestAES128
(
	TestDESID	int	IDENTITY,
	EncryptCol	varbinary(1024)
)
go

GRANT SELECT, INSERT, UPDATE, DELETE
ON TestAES128 to Kimberly
go

CREATE TABLE TestAES256
(
	TestDESID	int	IDENTITY,
	EncryptCol	varbinary(1024)
)
go

GRANT SELECT, INSERT, UPDATE, DELETE
ON TestAES256 to Kimberly
go

CREATE TABLE TestRC2
(
	TestDESID	int	IDENTITY,
	EncryptCol	varbinary(1024)
)
go

GRANT SELECT, INSERT, UPDATE, DELETE
ON TestRC2 to Kimberly
go

CREATE TABLE TestRC4
(
	TestDESID	int	IDENTITY,
	EncryptCol	varbinary(1024)
)
go

GRANT SELECT, INSERT, UPDATE, DELETE
ON TestRC4 to Kimberly
go

CREATE TABLE TestRSA
(
	TestDESID	int	IDENTITY,
	EncryptCol	varbinary(1024)
)
go

GRANT SELECT, INSERT, UPDATE, DELETE
ON TestRSA to Kimberly
go
----- Test DESKey ----- 
EXECUTE AS LOGIN = 'Kimberly'
go
OPEN SYMMETRIC KEY DESKey 
DECRYPTION BY PASSWORD = 'R$A&F3A^L1L9O3V)E%S#K8I3M$B1E6R4L&Y!'
go
DECLARE @StartTime	datetime,
		@Counter	int
SELECT @Counter = 0
SELECT @StartTime = getdate()
WHILE @Counter < 2000
BEGIN
	INSERT dbo.TestDES
		VALUES(encryptByKey(Key_GUID('DESKey'), 'This is sample text to make sure that all of the encryption types encrypt the same amount of data.'))
	SELECT @Counter = @Counter + 1
END
INSERT master.dbo.MasterEncryptionPerformanceTestNumbers
	VALUES ('DES', datediff(ms, @StartTime, getdate()))
--SELECT * FROM master.dbo.MasterEncryptionPerformanceTestNumbers
go
REVERT
go
----- Test DESKey ----- 

----- Test 3DESKey ----- 
EXECUTE AS LOGIN = 'Kimberly'
go
OPEN SYMMETRIC KEY [3DESKey] 
DECRYPTION BY PASSWORD = 'R$A&F3A^L1L9O3V)E%S#K8I3M$B1E6R4L&Y!'
go
DECLARE @StartTime	datetime,
		@Counter	int
SELECT @Counter = 0
SELECT @StartTime = getdate()
WHILE @Counter < 2000
BEGIN
	INSERT dbo.Test3DES
		VALUES(encryptByKey(Key_GUID('3DESKey'), 'This is sample text to make sure that all of the encryption types encrypt the same amount of data.'))
	SELECT @Counter = @Counter + 1
END
INSERT master.dbo.MasterEncryptionPerformanceTestNumbers
	VALUES ('3DES', datediff(ms, @StartTime, getdate()))
--SELECT * FROM master.dbo.MasterEncryptionPerformanceTestNumbers
go
REVERT
go
----- Test 3DESKey ----- 

----- Test AES128Key ----- 
EXECUTE AS LOGIN = 'Kimberly'
go
OPEN SYMMETRIC KEY AES128Key 
DECRYPTION BY PASSWORD = 'R$A&F3A^L1L9O3V)E%S#K8I3M$B1E6R4L&Y!'
go
DECLARE @StartTime	datetime,
		@Counter	int
SELECT @Counter = 0
SELECT @StartTime = getdate()
WHILE @Counter < 2000
BEGIN
	INSERT dbo.TestAES128
		VALUES(encryptByKey(Key_GUID('AES128Key'), 'This is sample text to make sure that all of the encryption types encrypt the same amount of data.'))
	SELECT @Counter = @Counter + 1
END
INSERT master.dbo.MasterEncryptionPerformanceTestNumbers
	VALUES ('AES128', datediff(ms, @StartTime, getdate()))
--SELECT * FROM master.dbo.MasterEncryptionPerformanceTestNumbers
go
REVERT
go
----- Test AES128Key ----- 

------- Test AES256Key ----- 
EXECUTE AS LOGIN = 'Kimberly'
go
OPEN SYMMETRIC KEY AES256Key 
DECRYPTION BY PASSWORD = 'R$A&F3A^L1L9O3V)E%S#K8I3M$B1E6R4L&Y!'
go
DECLARE @StartTime	datetime,
		@Counter	int
SELECT @Counter = 0
SELECT @StartTime = getdate()
WHILE @Counter < 2000
BEGIN
	INSERT dbo.TestAES256
		VALUES(encryptByKey(Key_GUID('AES256Key'), 'This is sample text to make sure that all of the encryption types encrypt the same amount of data.'))
	SELECT @Counter = @Counter + 1
END
INSERT master.dbo.MasterEncryptionPerformanceTestNumbers
	VALUES ('AES256', datediff(ms, @StartTime, getdate()))
--SELECT * FROM master.dbo.MasterEncryptionPerformanceTestNumbers
go
REVERT
go
------- Test AES256Key ----- 

----- Test RC2Key ----- 
EXECUTE AS LOGIN = 'Kimberly'
go
OPEN SYMMETRIC KEY RC2Key 
DECRYPTION BY PASSWORD = 'R$A&F3A^L1L9O3V)E%S#K8I3M$B1E6R4L&Y!'
go
DECLARE @StartTime	datetime,
		@Counter	int
SELECT @Counter = 0
SELECT @StartTime = getdate()
WHILE @Counter < 2000
BEGIN
	INSERT dbo.TestRC2
		VALUES(encryptByKey(Key_GUID('RC2Key'), 'This is sample text to make sure that all of the encryption types encrypt the same amount of data.'))
	SELECT @Counter = @Counter + 1
END
INSERT master.dbo.MasterEncryptionPerformanceTestNumbers
	VALUES ('RC2', datediff(ms, @StartTime, getdate()))
--SELECT * FROM master.dbo.MasterEncryptionPerformanceTestNumbers
go
REVERT
go
----- Test RC2 ----- 

----- Test RC4Key ----- 
EXECUTE AS LOGIN = 'Kimberly'
go
OPEN SYMMETRIC KEY RC4Key 
DECRYPTION BY PASSWORD = 'R$A&F3A^L1L9O3V)E%S#K8I3M$B1E6R4L&Y!'
go
DECLARE @StartTime	datetime,
		@Counter	int
SELECT @Counter = 0
SELECT @StartTime = getdate()
WHILE @Counter < 2000
BEGIN
	INSERT dbo.TestRC4
		VALUES(encryptByKey(Key_GUID('RC4Key'), 'This is sample text to make sure that all of the encryption types encrypt the same amount of data.'))
	SELECT @Counter = @Counter + 1
END
INSERT master.dbo.MasterEncryptionPerformanceTestNumbers
	VALUES ('RC4', datediff(ms, @StartTime, getdate()))
--SELECT * FROM master.dbo.MasterEncryptionPerformanceTestNumbers
go
REVERT
go
----- Test RC4 ----- 

----- Test RSAKey ----- 
EXECUTE AS LOGIN = 'Kimberly'
go

-- No need to open an asymmetric key - we will encrypt with public
-- decrypt with private

-- OPEN ASYMMETRIC KEY RSAKey 
-- DECRYPTION BY PASSWORD = 'R$A&F3A^L1L9O3V)E%S#K8I3M$B1E6R4L&Y!'
-- go

DECLARE @StartTime	datetime,
		@Counter	int
SELECT @Counter = 0
SELECT @StartTime = getdate()
WHILE @Counter < 2000
BEGIN
	INSERT dbo.TestRSA
		VALUES(encryptByKey(Key_GUID('RSAKey'), 'This is sample text to make sure that all of the encryption types encrypt the same amount of data.'))
	SELECT @Counter = @Counter + 1
END
INSERT master.dbo.MasterEncryptionPerformanceTestNumbers
	VALUES ('RSA', datediff(ms, @StartTime, getdate()))
--SELECT * FROM master.dbo.MasterEncryptionPerformanceTestNumbers
go

REVERT
go
----- Test RSA ----- 

-- Now see some of the data:
--SELECT TOP 100 * FROM TestDes
--SELECT TOP 100 * FROM Test3Des
--SELECT TOP 100 * FROM TestAES128
--SELECT TOP 100 * FROM TestAES256
--SELECT TOP 100 * FROM TestRC2
--SELECT TOP 100 * FROM TestRC4
--SELECT TOP 100 * FROM TestRSA
go

SELECT * FROM master.dbo.MasterEncryptionPerformanceTestNumbers
go
