Monday, January 22, 2007

 

Backup and Restore tool for SQL Server with embedded compression and encryption

Backup & Restore tool for SQL Server with embedded compression and encryption

When I wrote Four cheap ways to get your SQL Server backup files smaller , I did it with SQL Server 7 and SQL Server 2000 in mind. Now, with SQL Server 2005 not supporting “Named Pipes” as target devices for backup/restore operations, only “three cheap ways to get SQL Server backup files smaller” would be available.

While I was revising the content of the referred post, I found myself developing a simple tool to perform compression/decompression while backing up or restoring and also adding the capability to encrypt/decrypt the result.

Finally, I got a tool I called ZipVDI with a short “readme.txt” file you can read bellow. ZipVDI is a very simple tool, not comparable to all those commercial tools making a good job in this area. All of them have a lot of interesting features, but there is an interesting one I am not able to find in those commercial tools: ZipVDI compression and encryption format is publicly available. Compression is done with gzip library and sample code showing how ZipVDI derives keys, IV, encrypts and decrypts is attached.
This means that any other tool (commercial or not) would be able to use or generate files in the “ZipVDI” format.

Why is this feature an important one? Let’s see: haven’t you see cases where different sites of the same company are not able to share SQL Server backups generated by these tools because they are using different tools in each site? Of course, this situation is much more common when the involved sites belong to different companies.
Of course, “ZipVDI” is not relevant enough to make others tools be aware of its file format, but if these commercial tools were able to interact each other, your company, your site or even your department would buy their favorite tool to use as widely as possible in its internal scope while still being able to directly interchange backup files with “others”.
Isn’t this the situation with commercial/free file compressors? When you buy a copy of WinZip, you expect to decompress almost any compressed file no matter the compression format.

If after reading the previous comment and the content of the “Readme.txt” file for ZipVDI tool, you still want to play with it, download a binary copy from
download ZipVDI


Readme.txt
License Agreement For ZipVDI
------------------------------

This program can be used to backup/restore SQL Server databases
to/from compressed and encrypted files.
If you encrypt a file with this program, and then forget your
password, there is no way you can get the file back
- and it is NOT MY FAULT!

If the program crashes whilst encrypting/decrypting and you
lose valuable data, then I apologise, but I am not to be
held responsible.


Limited Warranty:
-----------------
THIS SOFTWARE IS PROVIDED "AS IS" AND ANY EXPRESS OR IMPLIED
WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE
GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR
BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY
OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY
WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF
THE POSSIBILITY OF SUCH DAMAGE.


Frequently Asked Questions
=============================

- What is ZipVDI?
- What is the compression algorithm used by ZipVDI?
- What are the encryption algorithms used by ZipVDI?
- What command line arguments can be used with ZipVDI?
- Is there deeper explanation for command line arguments?
- How do I convert a backup file generated with ZipVDI
to a regular SQL Server backup file?
- How do I convert a regular SQL Server backup file to
a file format readable by ZipVDI?


- What is ZipVDI?
===================

ZipVDI is a small console application that allows you to
execute backup/restore operations directly TO/FROM
compressed and encrypted files.
Compression and encryption is done while the
backup/restore operation takes place, that is, they are
executed in memory without needing intermediate files.


- What is the compression algorithm used by ZipVDI?
===================================================

ZipVDI uses the zlib library to compress/decompress
the backup information.
Compression level ranges from 0 (no compression) to 9
(max compression), obviously inherited from the zlib
library

- What are the encryption algorithms used by ZipVDI?
====================================================

ZipVDI can encrypt/decrypt the backup information using:

- AES (rijndael) with a 256 bits Key and 128 bits IV
- Triple DES with 168 bits key and 128 bits IV

Key and IV are derived from a user pass phrase provided as
a command line argument.

However, due to Windows Crypto API provider limitations,
AES encryption can only be used on Windows 2003 and later
(Triple DES can be used in any of the currently supported
Windows versions)

- What command line arguments can be used with ZipVDI?
======================================================

ZipVDI uses getopts Command line argument parser from
Steve Mertz Its copyright notice
should be present among all the documentation of this tool.

By typing ZipVDI -h at the command prompt, a list of
available options and a short explanation is showed.

Usage: zipvdi [options]

--help, -h Displays this information
--instance, -i SQL Server instance name
default instance if omitted)
--sentence, -s SQL backup/restore sentence
use " to enclose the sentence)
--compress, -c Compression level (if omitted set
to 6)
--password, -p Pass phrase to encrypt/decrypt
use " to enclose the pass phrase).
If -p is present and pass phrase
is empty (you typed -p ""),
the program will prompt for it.
If -p is no present, no encryption
takes place.
--algorithm,-a Algorithm to encrypt: a(aes) d(3des)
(default algorithm is AES)
--logfile, -l File to store log information

Some samples:

* Backup pubs database to c:\pubs.bkp with the default
compression level and encrypting using AES with a
pass phrase

(next three lines are a single command)
zipvdi -s "backup database pubs to disk='c:\pubs.bkp'
with stats=1" -p "unbreakable pass phrase" -a a
-l "c:\pubs.bkp.log"

* Restore pubs_new database using the previous backup
and decrypting using AES with a pass phrase

(next five lines are a single command)
zipvdi -s "restore database pubs_new from disk = 'c:\pubs.bkp'
with move N'pubs' to N'c:\mssql\data\pubs_new.mdf',
move N'pubs_log' to N'c:\mssql\data\pubs_new_log.ldf',
replace, stats = 10" -p "unbreakable pass phrase"
-a a -l "c:\pubs_new.res.log"


- Is there deeper explanation for command line arguments?
=========================================================

Here, you can find deeper explanation for some command
line arguments

--instance, -i
--------------------------

When several SQL Server instances are running on the same
machine, use this argument to select which one is the
target instance for the backup/restore operation.

For example, if in addition to your default SQL Server
instance, you have a second instance named MySecondSQL,
you should execute the following command to backup the
pubs database hosted in the second instance

(next three lines are a single command)
zipvdi -i ServerName\MySecondSQL -s "backup database pubs
to disk='c:\pubs2.bkp'" -p "unbreakable pass phrase"
-a a -l "c:\pubs2.bkp.log"



--sentence, -s
-------------------------

This is the SQL sentence that performs the backup/restore
operation. Use double quotation marks to enclose the
sentence.

The same syntax supported by SQL Server should be used
with ZipVDI (you can use options such as CHECKSUM,
VERIFYONLY,...)with some restrictions:

- Only one backup/restore device can be specified.
Backing up/Restoring TO/FROM multiple devices
are not currently supported.

- Only disk can be specified as backup device
(TO DISK='xxx'/FROM DISK='xxx').
However, you can specify a named pipe or the
stdin/stdout as the file name used for the
backup/restore operation.

Example 1: backup pubs database to the named pipe
'\\.\pipe\PUBS_PIPE'
This possibility is very useful if you
want to send the output of this
program to the input of other tool
(like a different encryptor).
Note that in the example bellow,
no encryption takes place

(next three lines are a single command)
zipvdi -s "backup database pubs to
disk='\\.\pipe\PUBS_PIPE'"
-l "c:\pubs.bkp.log"

Example 2: backup pubs database to the stdout.
stdout is selected by choosing '-'
as the file name.
stdin can also be selected for restore operations

(next three lines are a single command)
zipvdi -s "backup database pubs to disk='-'"
-p "unbreakable pass phrase" -a a
-l "c:\pubs.bkp.log"

--compress, -c
-------------------------

This parameter defines the compression level that will be
used by zlib when compressing the backup content.
Valid values are from 0 (no compression) to 9 (max
compression). If not values is provided,
the default compression level of 6 will be used.



--algorithm, -a
--------------------------

This parameter defines which encryption/decryption will
be used. Backups can be encrypted with AES (a) or triple
DES (d). However, due to restrictions in the Windows Crypto
API system, AES encryption is only supported in
Windows 2003 or later.
Thus, if you want to share encrypted backups between
different Operating system, you should check all of them
support the selected encryption algorithm.

If this parameter is not present, then no
encryption/decryption is executed and the backup content
is only compressed/decompressed during the backup/restore
operation

--password, -p
-------------------------

Use this parameter to provide the pass phrase used to
derive the Key and the IV to be used with the encryption
algorithm.
Use double quotation marks to enclose pass phrase made of
more than one word.
If -p option is present but no pass phrase is entered
(you specify -p ""), then the program will ask for the pass
phrase before starting the operation.
This option is useful if you don't want to have the pass
phrase in your batch files, but requires you enter manually
the pass phrase at execution time

If the selected algorithm is AES, then the derived key will
have a length of 256 bits.
If the selected algorithm is 3 DES, then the derived key
will have a length of 168 bits.

--logfile, -l
-------------------------

Use this optional parameter to define a log file where
ZipVDI will store information about the operation: which
task is executed, target file, compression ratio, and any
message returned by the SQL Server during the
backup/restore operation (for example, if you specify the
clause "WITH STATS=10", the progress information will be
logged to this file).


- How do I convert a backup file generated with ZipVDI to
a regular SQL Server backup file?
==========================================================

ZipVDI backup operation is quite simple: the information
returned by the SQL Server as backup content is first
compressed using the zlib library and then it is encrypted
using either AES or Triple DES (if any of these algorithms
was selected).


+------------+ VDI +----------+ +---------+ +-------+
SQL Server -> Compress -> Encrypt -> Write
+------------+ +----------+ +---------+ +-------+


Thus, to convert the file generated by ZipVDI as the result
of a backup operation, you only need to revert both
operations:

- First you have to decrypt the file.
- then, you have to decompress the decrypted file

and you will get a backup file in the native SQL Server
backup file format.

To decrypt the ZipVDI generated file, you can use the
accompanying tool called DecryptBackup.
Also, the source code for this tool is provided.

To decompress the file obtained after decrypting,
you can use the gzip tool available at
www.gzip.org site.

These two steps could be done with one single tool (even
this functionality could be part of the ZipVDI tool).
The reason to create the tool DecryptBackup and opening
its code is to transparently show how ZipVDI encrypts.
ZipVDI is performing standard compression using a
"well know" compression algorithm and also is performing
standard encryption (using also "well know" encryption
algorithms) offering you the possibility to revert the
encryption by yourself since you can see the code to
encrypt.


- How do I convert a regular SQL Server backup file to a
file format readable by ZipVDI?
=========================================================

ZipVDI backup operation is quite simple: the information
returned by the SQL Server as backup content is first
compressed using the zlib library and then it is encrypted
using either AES or Triple DES (if any of these algorithms
was selected).
Thus, to convert a backup file generated by SQL Server to
a file that can be read by ZipVDI (and used to perform
restore operations) you only need to execute both
operations:

- First you have to compress the original backup
file using gzip tool.
- Then, you may encrypt the compressed file using
the accompanying tool called EncryptBackup.

And as explained in the previous question, these two steps
could be done with one single tool.
The reason to create the tool DecryptBackup and opening
its code is to transparently show how ZipVDI encrypts.
ZipVDI is performing standard compression using a
"well know" compression algorithm and also is performing
standard encryption (using also "well know" encryption
algorithms) offering you the possibility to revert the
encryption by yourself since you can see the code to
encrypt.


# posted by Juan Carlos @ 6:34 PM 7 comments

Monday, November 06, 2006

 

A compressed and encrypted User Defined Type (UDT) for SQL Server 2005

Compression and encryption: you may like or hate them, but they have been (and will be) there for a long time.

In fact, there are so many resources devoted to compression and encryption that is difficult to believe anyone could have a requirement and nobody has already developed a solution for that requirement.

Well, this is not totally true. For example, related to compression, time ago I had to develop a tool to perform on-the-fly compression/decompression for the output/input of windows programs (something similar to Unix tool mknod, but as a native Windows application, not emulated with Cygwin or similar).

Now, although the built-in support for Encryption in SQL Server 2005 seems to be enough to solve almost any encryption requirement at database level, I miss one feature: a native encrypted text data type that could be used as any other native data type in table definition.

I won’t discuss here whether it is necessary or not to have this data type or if it is better to satisfy this requirement using other techniques. This is better done by Sybase in its product features sheet (http://www.sybase.com/detail?id=1036205 or search for the words column and encryption in sybase.com site if the previous link becomes broken).
I know at least Sybase and Oracle 10g R2 are at this moment offering transparent data encryption at column level.

So, I started thinking how it could be done in SQL Server 2005. Many fancy features came to my mind during this phase, however, I tried to simplify as much as possible while keeping an interesting level of functionality for the this UDT. These are some of the features implemented in the Compressed and Encrypted UDT:

If you want to see a demo about how to create and use this compressed and encrypted UDT read this article: http://jcarlossaez.blogspot.com/2006/11/demo-for-compressed-and-encrypted-udt.html


# posted by Juan Carlos @ 5:09 PM 0 comments
 

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:

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
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;
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;
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')
drop database [TestCompEncrypt]
go
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
. . .

# posted by Juan Carlos @ 3:43 PM 0 comments

Friday, November 03, 2006

 

C# code to implement a compressed and encrypted User Defined Type (UDT) in SQL Server 2005

h)o.style.height=h;" selold="2" sel="2">Code Copy HideScrollFull
//
//Begining of sample code
//
// This software is provided 'as-is', without any express
// or implied warranty. In no event will the author be
// held liable for any damages arising from the use of this
// software.
//
// The purpose of this code is to implement a compressed
// and encrypted User Defined Type for SQL Server 2005
//
// To compile the code and generate the corresponding assembly
// copy the content between the literal
// "//Begining of sample code" and "//ending of sample code",
// save it in a file (for example in
// C:\CompressEncrypt\CompEncString.cs) and at the command
// prompt type:
//
// csc /out:C:\CompressEncrypt\CompEncString.dll
// /target:library C:\CompressEncrypt\CompEncString.cs
//
// assuming the c# compiler is in your path variable
// or use the c# IDE
//
// For a detailed sample about haw this UDT can be used,
// read my post
// http://jcarlossaez.blogspot.com/2006/11/compressed-and-encrypted-user-defined.html
//

using System;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.IO;
using System.IO.Compression;
using System.Text;
using System.Security.Cryptography;

namespace CEString
{
[Serializable]
[SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true,
MaxByteSize = 8000, Name = "CompEncString", IsFixedLength = false)]
public class CompEncString : INullable, IBinarySerialize
{
#region private state and constructors

//Original plain text
private string m_String;

//Plain text ompressed and encrypted
private byte[] m_Bytes;

//Salt min and max len
private int minSaltLen = 4;
private int maxSaltLen = 32;

//Min len of plain text to fire compression
private int MinLenToCompress = 128;


public CompEncString(string value)
{
this.m_String = value;
this.m_Bytes = GetCompressedEncryptedBytes(Encoding.Unicode.GetBytes(value));
}

public CompEncString(byte[] bytes)
{
this.m_Bytes = bytes;
}
public bool IsNull
{
get
{
return this.m_String == null && this.m_Bytes == null;
}
}

public static CompEncString Null
{
get
{
CompEncString str = new CompEncString((string)null);

return str;
}
}

public CompEncString()
{
}

#endregion

#region Parse/ToString Methods

[Microsoft.SqlServer.Server.SqlMethod(IsDeterministic = true,
IsPrecise = true, DataAccess = DataAccessKind.Read,
SystemDataAccess = SystemDataAccessKind.None)]
public override string ToString()
{
//Returns de original text by decrypting and decompressing
if (this.m_Bytes != null)
{
this.m_String = GetDecryptedDecompressedString(this.m_Bytes);
}
else
this.m_String = null;

return this.m_String;
}

[Microsoft.SqlServer.Server.SqlMethod(DataAccess = DataAccessKind.Read,
SystemDataAccess = SystemDataAccessKind.None)]
public static CompEncString Parse(SqlString sqlString)
{
//Performs compression and encryption of original plain text
if (sqlString.IsNull)
return CompEncString.Null;
return new CompEncString(sqlString.Value);
}
#endregion

#region IBinarySerialize Members

public void Write(System.IO.BinaryWriter w)
{
//Writes the binary data (compressed and encrypted) adding
//a small header which represents the len of binary data
w.Write(this.m_Bytes.Length);
w.Write(this.m_Bytes);
}

public void Read(System.IO.BinaryReader r)
{
//Reads the binary data. The len of the data
//to be read is in the data header
int length = r.ReadInt32();
this.m_Bytes = r.ReadBytes(length);

}
#endregion

#region conversion to/from compressed and encrypted strings

private byte[] GetCompressedEncryptedBytes(byte[] input)
{
//Compress and encrypt the array of bytes received as parameter
//Compression is done with GZipStream. After original plain text has
//been compressed, it is encrypted using Rijndael algorithm and the Key
//and IV stored in the database.
//Previous to encrypting, Random data is pre-appended to byte array (salt)
//to generate different encrypted array for the same plain text

byte[] output;
byte[] compressedsalted;
byte[] rKey = new byte[32];
byte[] rIV = new byte[16];

//Get KEY and IV
if (GetKEYIV(ref rKey, ref rIV) == false)
{
//We can not read key and IV
throw new Exception("It has not been possible to get KEY and IV to encrypt data.\n" +
"Try running the procedure SetCEStringSecurity to correctly set up KEY and IV");
}
if (input.Length >= MinLenToCompress)
{
using (MemoryStream ms = new MemoryStream())
{
//Compress the original byte array
using (GZipStream gz = new GZipStream(ms, CompressionMode.Compress, true))
gz.Write(input, 0, input.Length);

//Add random data to the compressed result
compressedsalted = this.AddSalt(ms.ToArray());
}
}
else
{
//Add random data to plain text
compressedsalted = this.AddSalt(input);
}

//Encrypt salted data
output = Encrypt(compressedsalted,rKey,rIV);

return output;
}

private string GetDecryptedDecompressedString(byte[] input)
{
//Decrypt and decompress the array of bytes received as parameter.
//Decryption is done using Rijndael algorithm and the Key
//and IV stored in the database.Then, the random data added (salt)
//is removed and finally, the resulting array is decompressed using
//GZipStream.

const int MaxDeccompressedSize = 8000;
byte[] output = new byte[MaxDeccompressedSize];
int length = 0;
byte[] compressedsalted;
byte[] rKey = new byte[32];
byte[] rIV = new byte[16];

//Get the KEY and IV
if (GetKEYIV(ref rKey, ref rIV) == false)
{
//We can not read key and IV
throw new Exception("It has not been possible to get KEY and IV to encrypt data.\n" +
"Try running the procedure SetCEStringSecurity to correctly set up KEY and IV");
}
//Decrypt the byte array
compressedsalted = Decrypt(input, rKey,rIV);

//Remove random data from the decrypted array
//Firts, get the salt len
int saltLen = (compressedsalted[0] & 0x03)
(compressedsalted[1] & 0x0c)
(compressedsalted[2] & 0x30)
(compressedsalted[3] & 0xc0);

// Copy original plain text discarding the salt value
Array.Copy(compressedsalted, saltLen, output,
0, compressedsalted.Length - saltLen);

//Check gzip header magic number. Perhaps, the original
//plain text was not compressed because it was too short
if ((output[0] == 0x1f) & (output[1] == 0x8b))
{
try
{
//Now decompress the array to obtain the original plain text
//As the plain text can not be larger than 8000 bytes, lets
//decompress in one single step, allocating enough memory
using (MemoryStream ms = new MemoryStream(output))
{
using (GZipStream gz = new GZipStream(ms, CompressionMode.Decompress, true))
{
int numBytesRead = 0;
int offset = 0;
numBytesRead = gz.Read(output, offset, MaxDeccompressedSize);
if (numBytesRead == 0)
Array.Resize<byte>(ref output, length);
else if (numBytesRead < MaxDeccompressedSize)
Array.Resize<byte>(ref output, numBytesRead);
}
}
}
catch
{
//incorrect gzip format! little to do. Just return as plain text
return Encoding.Unicode.GetString(output);
}
}
return Encoding.Unicode.GetString(output);
}
#endregion

#region Helper functions for encryption

//Encrypt a byte array into a byte array using Rijndael,a key and an IV
private byte[] Encrypt(byte[] clearData, byte[] Key, byte[] IV)
{
//Encryption artifacts
MemoryStream ms = new MemoryStream();
Rijndael enc = Rijndael.Create();

//Set the KEY and IV
enc.Key = Key;
enc.IV = IV;

//explicitly set properties, even if they have default values
enc.Mode = CipherMode.CBC;
enc.Padding = PaddingMode.PKCS7;

//Perform encryption
CryptoStream cs = new CryptoStream(ms, enc.CreateEncryptor(),
CryptoStreamMode.Write);
cs.Write(clearData, 0, clearData.Length);
cs.Close();

//Return encrypted array
return ms.ToArray();
}

// Decrypt a byte array into a byte array using Rijndael,a key and an IV
private byte[] Decrypt(byte[] cipherData, byte[] Key, byte[] IV)
{
//Decryption artifacts
MemoryStream ms = new MemoryStream();
Rijndael dec = Rijndael.Create();

//Set the KEY and IV
dec.Key = Key;
dec.IV = IV;

//explicitly set properties, even if they have default values
dec.Mode = CipherMode.CBC;
dec.Padding = PaddingMode.PKCS7;

//Perform decryption
CryptoStream cs = new CryptoStream(ms, dec.CreateDecryptor(),
CryptoStreamMode.Write);
cs.Write(cipherData, 0, cipherData.Length);
cs.Close();

//Return recrypted array
return ms.ToArray();
}


private bool GetKEYIV(ref byte[] Key, ref byte[] IV)
{
//Reads the KEY and IV stored in the database, in the table SecurityGuard
//and encrypted using an asymmetric key named k_CEStringProtector
string sqlsentence;

//Prepare the sentence to decrypt and read the encryption key and IV
sqlsentence = "select DecryptByAsymKey(AsymKey_ID('k_CEStringProtector')," +
"SecurityGuardKey) frKEY,DecryptByAsymKey(AsymKey_ID('k_CEStringProtector')," +
"SecurityGuardIV) frIV from SecurityGuard where " +
"SecurityGuardName='default'";

using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(sqlsentence, connection);
try
//Read the KEY and IV. If nothing is present, returns false
{
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
if (reader.GetSqlBytes(0).IsNull == true reader.GetSqlBytes(1).IsNull == true)
return false;

//Load the key
reader.GetBytes(0, 0, Key, 0, 32);

//Load the IV
reader.GetBytes(1, 0, IV, 0, 16);

return true;
}
else
return false;
}
catch
{
return false;
}
}
}
/// Adds an array of randomly generated bytes at the beginning of the
/// array holding original plain text value.
/// This code has been borrowed from
/// http://www.obviex.com/samples/EncryptionWithSalt.aspx
private byte[] AddSalt(byte[] plainTextBytes)
{
// Generate the salt.
byte[] saltBytes = GenerateSalt();

// Allocate array which will hold salt and plain text bytes.
byte[] plainTextBytesWithSalt = new byte[plainTextBytes.Length +
saltBytes.Length];
// First, copy salt bytes.
Array.Copy(saltBytes, plainTextBytesWithSalt, saltBytes.Length);

// Append plain text bytes to the salt value.
Array.Copy(plainTextBytes, 0,
plainTextBytesWithSalt, saltBytes.Length,
plainTextBytes.Length);

return plainTextBytesWithSalt;
}

/// Generates an array holding cryptographically strong bytes.
/// This code has been borrowed from
/// http://www.obviex.com/samples/EncryptionWithSalt.aspx
private byte[] GenerateSalt()
{
int saltLen = 0;

// Use random number generator to calculate salt length.
saltLen = GenerateRandomNumber(minSaltLen, maxSaltLen);

// Allocate byte array to hold our salt.
byte[] salt = new byte[saltLen];

// Populate salt with cryptographically strong bytes.
RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();

rng.GetNonZeroBytes(salt);

// Split salt length (always one byte) into four two-bit pieces and
// store these pieces in the first four bytes of the salt array.
salt[0] = (byte)((salt[0] & 0xfc) (saltLen & 0x03));
salt[1] = (byte)((salt[1] & 0xf3) (saltLen & 0x0c));
salt[2] = (byte)((salt[2] & 0xcf) (saltLen & 0x30));
salt[3] = (byte)((salt[3] & 0x3f) (saltLen & 0xc0));

return salt;
}

/// Generates random integer
/// This code has been borrowed from
/// http://www.obviex.com/samples/EncryptionWithSalt.aspx
private int GenerateRandomNumber(int minValue, int maxValue)
{
// We will make up an integer seed from 4 bytes of this array.
byte[] randomBytes = new byte[4];

// Generate 4 random bytes.
RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
rng.GetBytes(randomBytes);

// Convert four random bytes into a positive integer value.
int seed = ((randomBytes[0] & 0x7f) << 24)
(randomBytes[1] << 16)
(randomBytes[2] << 8)
(randomBytes[3]);

// Now, this looks more like real randomization.
Random random = new Random(seed);

// Calculate a random number.
return random.Next(minValue, maxValue + 1);
}
#endregion

}

}

public class EncryptionSetUp
{
private static readonly string CRLF = System.Environment.NewLine;
[Microsoft.SqlServer.Server.SqlProcedure]
public static void SetCEStringSecurity(string PassPhrase, int Force)
{
//This procedure executes the necessary work to set up all the necessary
//infrastructure for the Compressed and Encrypted user defined type:
// - Checks for the existence of database master key. If not present, a
// master key is created and encrypted using the user's pass phrase
// -Checks for the existence of the asymmetric key k_CEStringProtector
// used to encrypt the symmetric key and IV. If it doesn't exist, it
// is created.
// - Additionally, not only the presence of these keys is checked, but also
// also their usability, trying to hide to the user all the necessary work
// to set up security: the user only needs to rememeber the pass phrase
// - Once the master key and the asymmetric key are up, the table SecurityGuard
// is created, a symmetric key and a IV are derived from the user's pass phrase
// and all of this information (pass phrase, KEY and IV) is stored in the table
// encrypted by the asymmetric key k_CEStringProtector.

string sqlsentence;
string MessageInCaseOfError = "";
string sqlPassPhrase;
byte[] rKEY = new byte[32];
byte[] rIV = new byte[16];
int result = 0;

//escape single quotation mark from pass phrase
sqlPassPhrase = PassPhrase.Replace("'", "''");
GenerateKEYIV(PassPhrase, ref rKEY, ref rIV);

//Check DB master key exists
sqlsentence = "select count(*) from sys.symmetric_keys " +
"where symmetric_key_id=101";
MessageInCaseOfError = "Unexpected error trying to guess if your database " +
"has DB master key created: ";
if (ExecuteSQLWithResult(sqlsentence, ref result, ref MessageInCaseOfError) == false)
//We can not continue without db master key
return;

if (result == 0) //No Db master Key present, lets create it
{
sqlsentence = "create master key encryption by password='" + sqlPassPhrase + "'";
MessageInCaseOfError = "Since the database has not a mster key created,the " +
"system has tried to create it for you using the pass phrase you " +
"provided, but the following error ocurred: ";
if (ExecuteSQLWithResult(sqlsentence, ref result, ref MessageInCaseOfError) == false)
//We can not continue without db master key
return;
}

//Lets check the asymmetric key
sqlsentence = "select count(*) from sys.asymmetric_keys where name='k_CEStringProtector'";
MessageInCaseOfError = "Unexpected error trying to guess if your database has " +
"the asymetric key created: ";
if (ExecuteSQLWithResult(sqlsentence, ref result, ref MessageInCaseOfError) == false)
//We can not continue if we are not able to check
return;

if (result == 0) //No assymetric Key present, lets create it
{
sqlsentence = "create asymmetric key k_CEStringProtector WITH ALGORITHM = RSA_2048";
MessageInCaseOfError = "";
if (ExecuteSQLWithNoResult(sqlsentence, ref MessageInCaseOfError) == false)
{
//We can still try to open master key using the provided pass phrase
//as last chance (perhaps the database has been restored in a different
//server and the master key needs to be reencrypted
if (ReOpenDatabaseMasterKey(sqlPassPhrase) == false)
//We can not continue with no usable master key
return;

else //Master key restored, re-try asymmetric key creation
{
sqlsentence = "create asymmetric key k_CEStringProtector WITH ALGORITHM = RSA_2048";
MessageInCaseOfError = "It has been not possible to create the " +
"asymmetric master key: ";
if (ExecuteSQLWithNoResult(sqlsentence, ref MessageInCaseOfError) == false)
return;
}
}
}

//Lets check if the asymmetric key is ready to encrypt
sqlsentence = "select convert(int,isnull(convert(varchar(50)," +
"decryptbyasymkey(asymkey_id('k_CEStringProtector')," +
"encryptbyasymkey(asymkey_id('k_CEStringProtector'),'1'))),0))";
MessageInCaseOfError = "Unexpected error trying to check asymmetric key availability: ";
if (ExecuteSQLWithResult(sqlsentence, ref result, ref MessageInCaseOfError) == false)
//We can not continue since asymmetric encryption does not work
return;

if (result != 1)
//Asymmetric key not correctly working, lets try to re-open master key
{
//We can still try to open master key using the provided pass phrase
//as last chance (perhaps the database has been restored in a different
//server and the master key needs to be reencrypted
if (ReOpenDatabaseMasterKey(sqlPassPhrase) == false)
return;

else //Master key restored, re-try asymmetric key encryption
{
sqlsentence = "select convert(int,isnull(convert(varchar(50)," +
"decryptbyasymkey(asymkey_id('k_CEStringProtector')," +
"encryptbyasymkey(asymkey_id('k_CEStringProtector'),'1'))),0))";
MessageInCaseOfError = "It has been not possible to create the asymmetric " +
"master key: ";
if (ExecuteSQLWithResult(sqlsentence, ref result, ref MessageInCaseOfError) == false)
return;

if (result != 1)
//Asymmetric key still not correctly working, we can't do anything more
{
MessageInCaseOfError = "It has been not possible to create the " +
"asymmetric master key: " + CRLF + MessageInCaseOfError;
SendFeedBackToUser(MessageInCaseOfError);
return;
}
}
}
//Here we are with a master key and an asymmetric key ready for use.

//Create the table to store the pass phrase and dervied keys
sqlsentence = "if not exists (select * from sys.sysobjects where id = " +
"object_id(N'[SecurityGuard]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)" + CRLF +
"CREATE TABLE [SecurityGuard](" + CRLF +
"[SecurityGuardName] [nvarchar](50) NOT NULL," + CRLF +
"[SecurityGuardPassPhrase] [varbinary](500) NULL," + CRLF +
"[SecurityGuardKey] [varbinary](500) NULL," + CRLF +
"[SecurityGuardIV] [varbinary](500) NULL," + CRLF +
"CONSTRAINT [PK_SecurityGuard] PRIMARY KEY CLUSTERED " + CRLF +
"([SecurityGuardName] ASC))" + CRLF;
MessageInCaseOfError = "Unexpected error creating the table to store your pass phrase: ";
if (ExecuteSQLWithNoResult(sqlsentence, ref MessageInCaseOfError) == false)
return;

//If there is not pass phrase with keys, create them
sqlsentence = "select count(*) from SecurityGuard where SecurityGuardName='default'";
MessageInCaseOfError = "Unexpected error reading the table to store your pass phrase: ";
if (ExecuteSQLWithResult(sqlsentence, ref result, ref MessageInCaseOfError) == false)
//We can not continue
return;

if (result != 1) //Thre is no pass phrase, lets create it
{
sqlsentence = "insert into SecurityGuard (SecurityGuardName," +
"SecurityGuardPassPhrase,SecurityGuardKey,SecurityGuardIV)" +
"values ('default'," +
"EncryptByPassPhrase (N'" + sqlPassPhrase + "',N'" + sqlPassPhrase + "')," +
"EncryptByAsymKey(AsymKey_ID('k_CEStringProtector'),0x" + GetHexStringFromBytes(rKEY) + ")," +
"EncryptByAsymKey(AsymKey_ID('k_CEStringProtector'),0x" + GetHexStringFromBytes(rIV) + "))";
MessageInCaseOfError = "Unexpected error storing your pass phrase: ";
if (ExecuteSQLWithNoResult(sqlsentence, ref MessageInCaseOfError) == false)
return;
}
else
{
//There is a pass phrase. Lets check it is the same the user is providing now
// In such case, we will update the keys. If they are different, only update
//if the parameter Force is 1
sqlsentence = "select count(*) from SecurityGuard where SecurityGuardName='default' " +
"and convert(nvarchar(500),DecryptByPassPhrase(N'" + sqlPassPhrase +
"',SecurityGuardPassPhrase))=N'" + sqlPassPhrase + "'";
MessageInCaseOfError = "Unexpected error reading the table to store your " +
"pass phrase: ";
if (ExecuteSQLWithResult(sqlsentence, ref result, ref MessageInCaseOfError) == false)
return;

if ((result == 1) (Force == 1))
{
sqlsentence = "update SecurityGuard set " +
"SecurityGuardPassPhrase = EncryptByPassPhrase (N'" + sqlPassPhrase + "',N'" + sqlPassPhrase + "'), " +
"SecurityGuardKey = EncryptByAsymKey(AsymKey_ID('k_CEStringProtector'),0x" + GetHexStringFromBytes(rKEY) + "), " +
"SecurityGuardIV=EncryptByAsymKey(AsymKey_ID('k_CEStringProtector'),0x" + GetHexStringFromBytes(rIV) + ") " +
"where SecurityGuardName='default'";
MessageInCaseOfError = "Unexpected error updating your pass phrase: ";
if (ExecuteSQLWithNoResult(sqlsentence, ref MessageInCaseOfError) == false)
return;
}
else //The stored pass phrase and the current one do not match
{
//inform about the possibility of use the parameter Force
MessageInCaseOfError = "The pass phrase you are providing does not match the existing one." +
"If you want the new pass phrase to become the active pass phrase, use the " +
"the parameter Force = 1 when executing this procedure, but be aware that " +
"previous encrypted data will become unusable";
SendFeedBackToUser(MessageInCaseOfError);
return;
}

}
}


private static void SendFeedBackToUser(string errmessage)
{
//Console.WriteLine(errmessage);
SqlContext.Pipe.Send(errmessage);
}

private static bool ReOpenDatabaseMasterKey(string sqlPassPhrase)
{
string sqlsentence;
string MessageInCaseOfError;

sqlsentence = "open master key decryption by password='" + sqlPassPhrase + "'" + CRLF +
"ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY" + CRLF +
"close master key";
MessageInCaseOfError = "The database has a master key that either has not been " + CRLF +
"created by this tool or it has been created with this tool but using " +
"a different pass phrase. " + CRLF +
"If you situation is the first case: " + CRLF +
" - you should recover it from you master key backup. " + CRLF +
"If you situation is the second case: " + CRLF +
" - Try again providing the correct pass phrase." + CRLF +
" OR " + CRLF +
" - If you want to change the pass phrase (assuming all the content " + CRLF +
" encrypted with the previous pass phrase will become unavailable)" + CRLF +
" you have to: " + CRLF +
" - Drop asymmetric key k_CEStringProtector executing " + CRLF +
" drop asymmetric key k_CEStringProtector " + CRLF +
" - Drop database master key executing " + CRLF +
" drop master key " + CRLF;

if (ExecuteSQLWithNoResult(sqlsentence, ref MessageInCaseOfError) == false)
//We can not open the master key
return false;
else //Master key restored
return true;
}

//executes the sentence received as parameter in the sqlcontext and return the
//value of first column/first Row in value parameter.
//If ErrorMessage parameter is not empty and an error occures, ErrorMessage
//and the exception error message are sent to the user
private static bool ExecuteSQLWithResult(string SQLSentence, ref int value, ref string ErrorMessage)
{
string MessageToUser;
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand();
SqlDataReader reader;
command.Connection = connection;

command.CommandText = SQLSentence;
try
{
reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
if (reader.GetSqlInt32(0).IsNull ==false)
value = (int) reader.GetSqlInt32(0);
}
reader.Close();
return true;
}
catch (Exception e)
{
if (ErrorMessage != "")
{
MessageToUser = ErrorMessage + CRLF + e.Message;
SendFeedBackToUser(MessageToUser);
}
return false;
}
}
}

//executes the sentence received as parameter in the sqlcontext
//If ErrorMessage parameter is not empty and an error occures, ErrorMessage
//and the exception error message are sent to the user
private static bool ExecuteSQLWithNoResult(string SQLSentence, ref string ErrorMessage)
{
int value;
string MessageToUser;
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand();
SqlDataReader reader;
command.Connection = connection;

command.CommandText = SQLSentence;
try
{
reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
value = (int)reader.GetSqlInt32(0);
}
reader.Close();
return true;
}
catch (Exception e)
{
if (ErrorMessage != "")
{
MessageToUser = ErrorMessage + CRLF + e.Message;
SendFeedBackToUser(MessageToUser);
}
return false;
}
}
}

// Generate a Key and IV from a passphrase
private static void GenerateKEYIV(string PassPhrase, ref byte[] Key, ref byte[] IV)
{
MD5CryptoServiceProvider hash = new MD5CryptoServiceProvider();
PasswordDeriveBytes pdb = new PasswordDeriveBytes(PassPhrase,
hash.ComputeHash(Encoding.Unicode.GetBytes(PassPhrase)));
Array.Copy(pdb.GetBytes(32), Key, 32);
Array.Copy(pdb.GetBytes(16), IV, 16);
}
public static string GetHexStringFromBytes(byte[] bytes)
{
StringBuilder sb = new StringBuilder(bytes.Length * 2);
if ((bytes == null) (bytes.Length == 0))
return null;

for (int i = 0; i < bytes.Length; i++)
{
sb.Append(bytes[i].ToString("X2"));
}
return sb.ToString();
}

}
//
//ending of sample code
//
. . .

# posted by Juan Carlos @ 4:26 PM 0 comments

This page is powered by Blogger. Isn't yours?