Monday, November 06, 2006
A demo for the compressed and encrypted UDT
During the demo, I will use the folder “C:\CompressEncrypt\” to run the demo. So if you want to run the demo by yourself in your computer, I will recommend you to create this folder. Doing this, you will be able to directly run the demo sample without changing any reference.
• First of all, you will need the C# code that implements this UDT. Download it from http://jcarlossaez.blogspot.com/2006/11/c-code-to-implement-compressed-and.html and save it in a file , for example in C:\CompressEncrypt\CompEncString.cs .
• Now, compile the code to generate the assembly. You can use the C# IDE or simply type at your command prompt this command:
csc /out:C:\CompressEncrypt\CompEncString.dll /target:library C:\CompressEncrypt\CompEncString.cs
• At his moment, you should have the assembly ready in the demo folder (C:\CompressEncrypt). Open a connection to your SQL Server 2005 to continue with the demo running the following T-SQL commands:
•Copy the backup file in a second SQL Server. I will assume you will copy it in the second server in the folder C:\CompressEncrypt.
• First of all, you will need the C# code that implements this UDT. Download it from http://jcarlossaez.blogspot.com/2006/11/c-code-to-implement-compressed-and.html and save it in a file , for example in C:\CompressEncrypt\CompEncString.cs .
• Now, compile the code to generate the assembly. You can use the C# IDE or simply type at your command prompt this command:
csc /out:C:\CompressEncrypt\CompEncString.dll /target:library C:\CompressEncrypt\CompEncString.cs
• At his moment, you should have the assembly ready in the demo folder (C:\CompressEncrypt). Open a connection to your SQL Server 2005 to continue with the demo running the following T-SQL commands:
h)o.style.height=h;" selold="2" sel="2">Code Copy HideScrollFull
set nocount on
go
--
-- Create a demo database
-- better than destroy any existing one ;-)
--
create database TestCompEncrypt
on (name=TestCompEncrypt_dat,
filename='C:\CompressEncrypt\TestCompEncrypt_dat.mdf' )
go
use TestCompEncrypt
go
--
-- Enable CLR
--
sp_configure 'clr enabled',1
go
reconfigure
go
--
-- Load the assembly (Important: change the path to the current location
-- of the assembly)
--
create assembly CompEncString
AUTHORIZATION [dbo]
from 'C:\CompressEncrypt\CompEncString.dll'
WITH PERMISSION_SET = SAFE;
go
--
-- Create the CompEncString user defined type
--
CREATE TYPE dbo.CompEncString
EXTERNAL NAME CompEncString.[CEString.CompEncString];
go
--
-- Create the procedure to set up the keys derived
-- from user's passphrase
--
CREATE PROCEDURE SetCEStringSecurity (@passphrase nvarchar(500) ,@force int)
AS EXTERNAL NAME CompEncString.EncryptionSetUp.SetCEStringSecurity
go
--
-- Do all necessary work to correctly set up the KEY and IV derived from
-- user's pass phrase
--
exec SetCEStringSecurity 'Put your favorite pass phrase here: Upper and lower case,and numbers or others especial chars ;-)',0
--
-- Create three tables: the first one store text using the native
-- SQL Server nvarchar type.
-- The second one uses the new CompEncString data type created.
-- The third one only stores varbinary information
--
create table NoEncryptedTable (MyText nvarchar(4000))
create table EncryptedTable (MyText CompEncString)
Create table RegularTable (myBinaryData varbinary(8000))
go
--
-- Add the same dummy content to the first two tables
--
declare @cont int
set @cont=0
while @cont<10
begin
go
--
-- Compare the space required by both tables to store the same content
--
exec sp_spaceused 'NoEncryptedTable'
exec sp_spaceused 'EncryptedTable';
--
-- Verify we can read encrypted content
--
select MyText [Text From Non Encrypted] from NoEncryptedTable
select MyText.ToString() [Text From Encrypted] from EncryptedTable
--
-- Verify that encrypting the same text does not
-- generate the same ciphered raw data
--
truncate table EncryptedTable
declare @cont int
set @cont=0
while @cont<10
begin
select MyText Cipheredtext,MyText.ToString() PlainText from EncryptedTable
go
--
-- Change a text field to new compressed and encrypted type
--
alter table NoEncryptedTable alter column MyText CompEncString
go
--
-- Verify the content has been compressed and encrypted
--
select MyText [Encrypted Content],Mytext.ToString() [Decrypted Content]
from NoEncryptedTable
go
--
-- Directly insert encrypted data into the table
-- Note: The binary data used is an instance of the
-- literal "1" compressed and encrypted
--
truncate table EncryptedTable
go
insert into EncryptedTable (mytext)
values (0x900000008D225B43FA0C19A9702837A0D4EDEE06CA79F3273862819C142D9BA2FF5E868718E8E1F45BEFCE6B9FA758C54B52AB3D201D09C64A7235E12C91E56FB1AFAC83EEBF90AE0A94DE1185181500D95CA41C67008A5EFF48DB65D8D35A4011C3C6C91FA5A667619D0AD198A0620D7F80FB03A523B5BF90F9AE4B5FBBA175E6995BFBCA8716551AEA6A91E84946D8A852BA3C)
go
select mytext.ToString() [Direct insert of encrypted content] from EncryptedTable
go
--
--Some samples to use with T-SQL.
--
--Although the samples may seen meaningless, its purpose is only
--to show the capabilities. After reading it, you could use this
--data type to easily send encrypted mail messages between SQL Servers
--or use your existing data replication infrastructure to encrypt data
--while it is sent from source server to target server without any
--change in the replication infrastructure. (obviously, source and
--target server must have the same secret pass phrase)
--
-- In memory compresion and encryption
declare @a CompEncString
select @a=cast('Dummy test compressed and encrypted in memory.' as CompEncString)
-- What does encrypted information look like?
select @a [In memory encrypted data]
-- And decrypted?
select @a.ToString() [In memory decrypted data]
-- A way to manipulate encrypted data
select @a=cast('Ooops! I forgot this...' + @a.ToString() as CompEncString)
select @a.ToString() [More In memory decrypted data]
-- Save the encrypted data in a table with encrypted data type
truncate table EncryptedTable
insert into EncryptedTable (mytext)
values (@a)
select mytext.ToString() [Data inserted from T-SQL routine]from EncryptedTable
-- save the encrypted data in a table that is not using
-- ComEmcString data type (raw binary).
-- NOTE: This table could be
-- included in the replicated tables set
-- as it does not contain "nothing strange"
insert into RegularTable (myBinaryData) values (cast(@a as varbinary(8000)))
--Recover the inserted data and decrypt it
--NOTE:this acction could be done in the SQL Server
--that is the reader of the subscription..
declare @b CompEncString
select @b=myBinaryData from RegularTable
select @b.ToString() [Decrypted data from binary information]
--
-- Let's backup the database
--
Backup database TestCompEncrypt
to disk='C:\CompressEncrypt\TestCompEncrypt.bak'
with init
go
--
-- Lets simulate a crash. Our database disappears.
-- At this moment we haven't master key back up.
-- Normally, this means that although we have a database
-- backup, we have lost encrypted data
--
use master
go
drop database TestCompEncrypt
go
go
--
-- Create a demo database
-- better than destroy any existing one ;-)
--
create database TestCompEncrypt
on (name=TestCompEncrypt_dat,
filename='C:\CompressEncrypt\TestCompEncrypt_dat.mdf' )
go
use TestCompEncrypt
go
--
-- Enable CLR
--
sp_configure 'clr enabled',1
go
reconfigure
go
--
-- Load the assembly (Important: change the path to the current location
-- of the assembly)
--
create assembly CompEncString
AUTHORIZATION [dbo]
from 'C:\CompressEncrypt\CompEncString.dll'
WITH PERMISSION_SET = SAFE;
go
--
-- Create the CompEncString user defined type
--
CREATE TYPE dbo.CompEncString
EXTERNAL NAME CompEncString.[CEString.CompEncString];
go
--
-- Create the procedure to set up the keys derived
-- from user's passphrase
--
CREATE PROCEDURE SetCEStringSecurity (@passphrase nvarchar(500) ,@force int)
AS EXTERNAL NAME CompEncString.EncryptionSetUp.SetCEStringSecurity
go
--
-- Do all necessary work to correctly set up the KEY and IV derived from
-- user's pass phrase
--
exec SetCEStringSecurity 'Put your favorite pass phrase here: Upper and lower case,and numbers or others especial chars ;-)',0
--
-- Create three tables: the first one store text using the native
-- SQL Server nvarchar type.
-- The second one uses the new CompEncString data type created.
-- The third one only stores varbinary information
--
create table NoEncryptedTable (MyText nvarchar(4000))
create table EncryptedTable (MyText CompEncString)
Create table RegularTable (myBinaryData varbinary(8000))
go
--
-- Add the same dummy content to the first two tables
--
declare @cont int
set @cont=0
while @cont<10
begin
insert into NoEncryptedTable (MyText)
values (replicate(cast(@cont as varchar),4000))
insert into EncryptedTable (MyText)
values (replicate(cast(@cont as varchar),4000))
set @cont=@cont +1
end;values (replicate(cast(@cont as varchar),4000))
insert into EncryptedTable (MyText)
values (replicate(cast(@cont as varchar),4000))
set @cont=@cont +1
go
--
-- Compare the space required by both tables to store the same content
--
exec sp_spaceused 'NoEncryptedTable'
exec sp_spaceused 'EncryptedTable';
--
-- Verify we can read encrypted content
--
select MyText [Text From Non Encrypted] from NoEncryptedTable
select MyText.ToString() [Text From Encrypted] from EncryptedTable
--
-- Verify that encrypting the same text does not
-- generate the same ciphered raw data
--
truncate table EncryptedTable
declare @cont int
set @cont=0
while @cont<10
begin
insert into EncryptedTable (MyText)
values (replicate('A',4000))
set @cont=@cont +1
end;values (replicate('A',4000))
set @cont=@cont +1
select MyText Cipheredtext,MyText.ToString() PlainText from EncryptedTable
go
--
-- Change a text field to new compressed and encrypted type
--
alter table NoEncryptedTable alter column MyText CompEncString
go
--
-- Verify the content has been compressed and encrypted
--
select MyText [Encrypted Content],Mytext.ToString() [Decrypted Content]
from NoEncryptedTable
go
--
-- Directly insert encrypted data into the table
-- Note: The binary data used is an instance of the
-- literal "1" compressed and encrypted
--
truncate table EncryptedTable
go
insert into EncryptedTable (mytext)
values (0x900000008D225B43FA0C19A9702837A0D4EDEE06CA79F3273862819C142D9BA2FF5E868718E8E1F45BEFCE6B9FA758C54B52AB3D201D09C64A7235E12C91E56FB1AFAC83EEBF90AE0A94DE1185181500D95CA41C67008A5EFF48DB65D8D35A4011C3C6C91FA5A667619D0AD198A0620D7F80FB03A523B5BF90F9AE4B5FBBA175E6995BFBCA8716551AEA6A91E84946D8A852BA3C)
go
select mytext.ToString() [Direct insert of encrypted content] from EncryptedTable
go
--
--Some samples to use with T-SQL.
--
--Although the samples may seen meaningless, its purpose is only
--to show the capabilities. After reading it, you could use this
--data type to easily send encrypted mail messages between SQL Servers
--or use your existing data replication infrastructure to encrypt data
--while it is sent from source server to target server without any
--change in the replication infrastructure. (obviously, source and
--target server must have the same secret pass phrase)
--
-- In memory compresion and encryption
declare @a CompEncString
select @a=cast('Dummy test compressed and encrypted in memory.' as CompEncString)
-- What does encrypted information look like?
select @a [In memory encrypted data]
-- And decrypted?
select @a.ToString() [In memory decrypted data]
-- A way to manipulate encrypted data
select @a=cast('Ooops! I forgot this...' + @a.ToString() as CompEncString)
select @a.ToString() [More In memory decrypted data]
-- Save the encrypted data in a table with encrypted data type
truncate table EncryptedTable
insert into EncryptedTable (mytext)
values (@a)
select mytext.ToString() [Data inserted from T-SQL routine]from EncryptedTable
-- save the encrypted data in a table that is not using
-- ComEmcString data type (raw binary).
-- NOTE: This table could be
-- included in the replicated tables set
-- as it does not contain "nothing strange"
insert into RegularTable (myBinaryData) values (cast(@a as varbinary(8000)))
--Recover the inserted data and decrypt it
--NOTE:this acction could be done in the SQL Server
--that is the reader of the subscription..
declare @b CompEncString
select @b=myBinaryData from RegularTable
select @b.ToString() [Decrypted data from binary information]
--
-- Let's backup the database
--
Backup database TestCompEncrypt
to disk='C:\CompressEncrypt\TestCompEncrypt.bak'
with init
go
--
-- Lets simulate a crash. Our database disappears.
-- At this moment we haven't master key back up.
-- Normally, this means that although we have a database
-- backup, we have lost encrypted data
--
use master
go
drop database TestCompEncrypt
go
. . .
•Copy the backup file in a second SQL Server. I will assume you will copy it in the second server in the folder C:\CompressEncrypt.
h)o.style.height=h;" selold="2" sel="2">Code Copy HideScrollFull
--
--Connect to the second server
--
--
-- Restore the database
--
use master
go
if exists (select name from master.dbo.sysdatabases where name = N'TestCompEncrypt')
restore database TestCompEncrypt from disk='C:\CompressEncrypt\TestCompEncrypt.bak'
go
--
-- Check what we can see
--
use TestCompEncrypt
go
select mytext.ToString() [Decrypted data after restoring DB] from encryptedtable
go
--
-- Recreate the security environment by calling SetCEStringSecurity
-- with our secret pass phrase
--
exec SetCEStringSecurity 'Put your favorite pass phrase here: Upper and lower case,and numbers or others especial chars ;-)',0
go
--
-- Now, we can see the encrypted content we had
-- at the backup time
--
select mytext.ToString() [Decrypted data after restoring DB and setting up security]from encryptedtable
go
--
-- And that's all, drop the database in the second server
--
use master
go
drop database TestCompEncrypt
--Connect to the second server
--
--
-- Restore the database
--
use master
go
if exists (select name from master.dbo.sysdatabases where name = N'TestCompEncrypt')
drop database [TestCompEncrypt]
gorestore database TestCompEncrypt from disk='C:\CompressEncrypt\TestCompEncrypt.bak'
go
--
-- Check what we can see
--
use TestCompEncrypt
go
select mytext.ToString() [Decrypted data after restoring DB] from encryptedtable
go
--
-- Recreate the security environment by calling SetCEStringSecurity
-- with our secret pass phrase
--
exec SetCEStringSecurity 'Put your favorite pass phrase here: Upper and lower case,and numbers or others especial chars ;-)',0
go
--
-- Now, we can see the encrypted content we had
-- at the backup time
--
select mytext.ToString() [Decrypted data after restoring DB and setting up security]from encryptedtable
go
--
-- And that's all, drop the database in the second server
--
use master
go
drop database TestCompEncrypt
. . .
# posted by Juan Carlos @ 3:43 PM 
