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:
- Encryption is done using the Rijndael algorithm with a 256 bits Key and 128 bits IV derived from the pass phrase provided by the user.
- The encrypted data is salted with random data to avoid obtaining the same encrypted data with the same input plain data. This contributes to make encryption stronger.
- Prior to encryption, input plain data is compressed. This is not necessary at all, but I like compression and it is so easy to compress…
Actually, the compression only takes place if the length of the original plain text is greater than 128 bytes as an attempt to avoid getting compressed data larger than plain data. - There is no need to use the SQL Server built-in functions to encrypt/decrypt the data. The UDT implementation does it for you (no more open symmetric key or EncryptByKey instructions for example). So, applications code is simpler.
- Of course, you can create your own database master key and take care of it (backup it and store the backup in a secure site), but you can get ride of that work and let this implementation to create and maintain the database master key for you. In this way, when you restore the database in a different server, you only need to provide your secret pass phrase (which should be stored in the most secure site I know: your brain :-) )
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
A demo for the compressed and encrypted UDT
• 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:
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
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
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.
--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
Friday, November 03, 2006
C# code to implement a compressed and encrypted User Defined Type (UDT) in SQL Server 2005
//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
//