Juan Carlos

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

h)o.style.height=h;" selold="2" sel="2">Code Copy HideScrollFull

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