Wednesday, March 26, 2008

Encrypt App.config

// Run "Encryption.EncryptConnectionStrings();" in the beginning of your winservice or winforms app.
// You need to add .NET reference to System.configuration.dll
using System.Configuration;
public static class Encryption
{
public static void EncryptConnectionStrings()
{
Configuration configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
ConfigurationSection section = configuration.GetSection("connectionStrings");
if (!section.SectionInformation.IsProtected)
{
section.SectionInformation.ProtectSection("DataProtectionConfigurationProvider");
section.SectionInformation.ForceSave = true;
configuration.Save(ConfigurationSaveMode.Modified);
}
}

public static void DecryptConnectionStrings()
{
Configuration configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
ConfigurationSection section = configuration.GetSection("connectionStrings");
if (section.SectionInformation.IsProtected)
{
section.SectionInformation.UnprotectSection();
section.SectionInformation.ForceSave = true;
configuration.Save(ConfigurationSaveMode.Modified);
}
}
}

How to encrypt and decrypt string in .NET application

using System;
using System.IO;
using System.Security.Cryptography;
using System.Text;

namespace MyCompany.Library
{
public class Encryption
{
public static string EncryptString(string valueToEncrypt)
{
return EncryptString(valueToEncrypt, "My password key");
}

public static string DecryptString(string valueToDecrypt)
{
return DecryptString(valueToDecrypt, "My password key");
}

public static string EncryptString(string valueToEncrypt, string secretPhrase)
{
CryptoStream encryptStream = null; // Stream used to encrypt
RijndaelManaged rijndael = null; // Rijndael provider
ICryptoTransform rijndaelEncrypt = null; // Encrypting object
MemoryStream memStream = new MemoryStream(); // Stream to contain data
byte[] key;
byte[] IV;
GenerateKey(secretPhrase, out key, out IV);
try
{
if( valueToEncrypt.Length > 0 )
{
// Create the crypto objects
rijndael = new RijndaelManaged();
rijndael.Key = key;
rijndael.IV = IV;
rijndaelEncrypt = rijndael.CreateEncryptor();
encryptStream = new CryptoStream(
memStream, rijndaelEncrypt, CryptoStreamMode.Write);

// Write the encrypted value into memory
byte[] input = Encoding.UTF8.GetBytes(valueToEncrypt);
encryptStream.Write(input, 0, input.Length);
encryptStream.FlushFinalBlock();

// Retrieve the encrypted value and return it
return Convert.ToBase64String(memStream.ToArray());
}
else
{
return "";
}
}
finally
{
if (rijndael != null) rijndael.Clear();
if (rijndaelEncrypt != null) rijndaelEncrypt.Dispose();
if (memStream != null) memStream.Close();
}
}

public static string DecryptString(string valueToDecrypt, string secretPhrase)
{
CryptoStream decryptStream = null; // Stream used to decrypt
RijndaelManaged rijndael = null; // Rijndael provider
ICryptoTransform rijndaelDecrypt = null; // decrypting object
MemoryStream memStream = null; // Stream to contain data
byte[] key;
byte[] IV;
GenerateKey(secretPhrase, out key, out IV);
try
{
if( valueToDecrypt.Length > 0 )
{
// Create the crypto objects
rijndael = new RijndaelManaged();
rijndael.Key = key;
rijndael.IV = IV;

//Now decrypt the previously encrypted message using the decryptor
// obtained in the above step.

// Write the encrypted value into memory
byte[] encrypted = Convert.FromBase64String(valueToDecrypt);
memStream = new MemoryStream(encrypted);


rijndaelDecrypt = rijndael.CreateDecryptor();
decryptStream = new CryptoStream(memStream, rijndaelDecrypt, CryptoStreamMode.Read);

byte[] fromEncrypt = new byte[encrypted.Length];

//Read the data out of the crypto stream.
decryptStream.Read(fromEncrypt, 0, fromEncrypt.Length);

// Retrieve the encrypted value and return it
string decryptedString = new string(Encoding.UTF8.GetChars(fromEncrypt));
return decryptedString.TrimEnd(new char[] {'\0'});
}
else
{
return "";
}
}
finally
{
if (rijndael != null) rijndael.Clear();
if (rijndaelDecrypt != null) rijndaelDecrypt.Dispose();
if (memStream != null) memStream.Close();
}
}

/// Generates an encryption key based on the given phrase. The
/// phrase is hashed to create a unique 32 character (256-bit)
/// value, of which 24 characters (192 bit) are used for the
/// key and the remaining 8 are used for the initialization vector (IV).
private static void GenerateKey(string secretPhrase, out byte[] key, out byte[] IV)
{
// Initialize internal values
key = new byte[24];
IV = new byte[16];

// Perform a hash operation using the phrase. This will
// generate a unique 32 character value to be used as the key.
byte[] bytePhrase = Encoding.ASCII.GetBytes(secretPhrase);
SHA384Managed sha384 = new SHA384Managed();
sha384.ComputeHash(bytePhrase);
byte[] result = sha384.Hash;

// Transfer the first 24 characters of the hashed value to the key
// and the remaining 8 characters to the intialization vector.
for (int index=0; index<24; index++) key[index] = result[index];
for (int index=24; index<40; index++) IV[index-24] = result[index];
}
}
}

Tuesday, March 11, 2008

Generate Sequential GUIDs for SQL Server 2005 in C#

Why generate sequential GUID in C#?

Originally, uniqueidentifier (GUID) column in SQL Server was not supposed to be sequential. But in my case, having sequential GUID is quite useful.
My application needs to know, what record was inserted first.

Fortunately, SQL Server 2005 supports "default newsequentialid()" constraint, that makes uniqueidentifier column grow sequentially [with every inserted record].

That worked quite well for me, until I decided to generate sequential GUID in C#.
(I needed it, because I use SqlBulkCopy and try to save two tables that share the same generated GUID key).

That turned out to be a tricky task. The reason -- .NET and SQL Server treat GUIDs quite different. In particular, they sort them quite differently.

Searching for solution

Alberto Ferrari's post How are GUIDs sorted by SQL Server? gave me a good idea about how to handle the problem.

I used modified Alberto's SQL code to find out what C#.NET GUID bytes are more [or less] significant from SQL Server 2005 ORDER BY clause perspective.

With UIDs As (
Select ID = 3, UID = cast ('01000000-0000-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 2, UID = cast ('00010000-0000-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 1, UID = cast ('00000100-0000-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 0, UID = cast ('00000001-0000-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 5, UID = cast ('00000000-0100-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 4, UID = cast ('00000000-0001-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 7, UID = cast ('00000000-0000-0100-0000-000000000000' as uniqueidentifier)
Union Select ID = 6, UID = cast ('00000000-0000-0001-0000-000000000000' as uniqueidentifier)
Union Select ID = 8, UID = cast ('00000000-0000-0000-0100-000000000000' as uniqueidentifier)
Union Select ID = 9, UID = cast ('00000000-0000-0000-0001-000000000000' as uniqueidentifier)
Union Select ID = 10, UID = cast ('00000000-0000-0000-0000-010000000000' as uniqueidentifier)
Union Select ID = 11, UID = cast ('00000000-0000-0000-0000-000100000000' as uniqueidentifier)
Union Select ID = 12, UID = cast ('00000000-0000-0000-0000-000001000000' as uniqueidentifier)
Union Select ID = 13, UID = cast ('00000000-0000-0000-0000-000000010000' as uniqueidentifier)
Union Select ID = 14, UID = cast ('00000000-0000-0000-0000-000000000100' as uniqueidentifier)
Union Select ID = 15, UID = cast ('00000000-0000-0000-0000-000000000001' as uniqueidentifier)
)
Select * From UIDs Order By UID


Note, that first line with ID=3 corresponds to:
new Guid(new bytes[16]{ 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 }
That means:
(new Guid("01000000-0000-0000-0000-000000000000").ToByteArray()[3] == 1)


Now, when I run modified Alberto's query, I'm getting the following sequence:
3, 2, 1, 0, 5, 4, 7, 6, 9, 8, 15, 14, 13, 12, 11, 10

That means, that GUID's byte #3 is the least significant and GUID's byte #10 is the most significant [from SQL Server ORDER BY clause perspective].

Final solution

Now we're ready to write C# code, that would sequentially increment any given GUID.
I also made it more convenient to increment GUID, by using "++" operator.
Here's how it's used:

private void Test()
{
SequentialGuid = new SequentialGuid(Guid.Empty);
SequentialGuid++;
}


and C# code that increments GUIDs sequentially:

public class SequentialGuid
{
Guid _CurrentGuid;
public Guid CurrentGuid
{
get { return _CurrentGuid; }
}

public SequentialGuid()
{
_CurrentGuid = Guid.NewGuid();
}

public SequentialGuid(Guid previousGuid)
{
_CurrentGuid = previousGuid;
}

public static SequentialGuid operator ++(SequentialGuid sequentialGuid)
{
byte[] bytes = sequentialGuid._CurrentGuid.ToByteArray();
for (int mapIndex = 0; mapIndex < 16; mapIndex++)
{
int bytesIndex = SqlOrderMap[mapIndex];
bytes[bytesIndex]++;
if (bytes[bytesIndex] != 0)
{
break; // No need to increment more significant bytes
}
}
sequentialGuid._CurrentGuid = new Guid(bytes);
return sequentialGuid;
}

private static int[] _SqlOrderMap = null;
private static int[] SqlOrderMap
{
get
{
if (_SqlOrderMap == null)
{
_SqlOrderMap = new int[16] { 3, 2, 1, 0, 5, 4, 7, 6, 9, 8, 15, 14, 13, 12, 11, 10 };
// 3 - the least significant byte in Guid ByteArray [for SQL Server ORDER BY clause]
// 10 - the most significant byte in Guid ByteArray [for SQL Server ORDERY BY clause]
}
return _SqlOrderMap;
}
}
}

Followers

About Me

My photo
Email me: blog@postjobfree.com