How to get compatibility between C# and SQL2k8 AES Encryption?

Question

I have an AES encryption being made on two columns: one of these columns is stored at a SQL Server 2000 database; the other is stored at a SQL Server 2008 database.

As the first column's database (2000) doesn't have native functionality for encryption / decryption, we've decided to do the cryptography logic at application level, with .NET classes, for both.

But as the second column's database (2008) allow this kind of functionality, we'd like to make the data migration using the database functions to be faster, since the data migration in SQL 2k is much smaller than this second and it will last more than 50 hours because of being made at application level.

My problem started at this point: using the same key, I didn't achieve the same result when encrypting a value, neither the same result size.

Below we have the full logic in both sides.. Of course I'm not showing the key, but everything else is the same:

private byte[] RijndaelEncrypt(byte[] clearData, byte[] Key)
{
    var memoryStream = new MemoryStream();

    Rijndael algorithm = Rijndael.Create();

    algorithm.Key = Key;
    algorithm.IV = InitializationVector;

    var criptoStream = new CryptoStream(memoryStream, algorithm.CreateEncryptor(), CryptoStreamMode.Write);
    criptoStream.Write(clearData, 0, clearData.Length);
    criptoStream.Close();

    byte[] encryptedData = memoryStream.ToArray();
    return encryptedData;
}

private byte[] RijndaelDecrypt(byte[] cipherData, byte[] Key)
{
    var memoryStream = new MemoryStream();

    Rijndael algorithm = Rijndael.Create();

    algorithm.Key = Key;
    algorithm.IV = InitializationVector;

    var criptoStream = new CryptoStream(memoryStream, algorithm.CreateDecryptor(), CryptoStreamMode.Write);

    criptoStream.Write(cipherData, 0, cipherData.Length);

    criptoStream.Close();

    byte[] decryptedData = memoryStream.ToArray();

    return decryptedData;
}

This is the SQL Code sample:

open symmetric key columnKey decryption by password = N'{pwd!!i_ll_not_show_it_here}'

declare @enc varchar(max)

set @enc = dbo.VarBinarytoBase64(EncryptByKey(Key_GUID('columnKey'), 'blablabla'))

select LEN(@enc), @enc

This varbinaryToBase64 is a tested sql function we use to convert varbinary to the same format we use to store strings in the .net application.

The result in C# is: eg0wgTeR3noWYgvdmpzTKijkdtTsdvnvKzh+uhyN3Lo=

The same result in SQL2k8 is: AI0zI7D77EmqgTQrdgMBHAEAAACyACXb+P3HvctA0yBduAuwPS4Ah3AB4Dbdj2KBGC1Dk4b8GEbtXs5fINzvusp8FRBknF15Br2xI1CqP0Qb/M4w

I just didn't get yet what I'm doing wrong.

Do you have any ideas?

EDIT: One point I think is crucial: I have one Initialization Vector at my C# code, 16 bytes. This IV is not set at SQL symmetric key, could I do this?

But even not filling the IV in C#, I get very different results, both in content and length.

Answer 1

Not really an answer, but too large for a comment. Perhaps it'll help someone else figure it out :)
SQL Server seems to do a great deal of padding. When I tried your example, I kept getting varying results, although the first 18 characters seem to be the same every time. It's not making sense to me yet however...

You're not showing how RijndaelEncrypt gets called, but with the different data lengths I was thinking about unicode vs ANSI codepage differences. You don't seem to be using unicode in SQL Server however, so the length difference would be the other way around, if anything.

Answer 2

There are a couple of things I'd look at:

1. Make absolutely sure that the plaintext is identical in content and encoding. IIRC, streams default to UTF-8 whereas if your VarBinaryToBase64 function take a nvarchar parameter, it will be Unicode.

2. Make sure both encryption algorithms use the same block size. In SQL, you determine the algorithm when you call CREATE SYMMETRIC KEY. If you do not specify an algorithm, it uses AES256. In .NET using RijndaelManaged, I believe the default block size is 128 but you can set it to 256 (you cannot if you use the Aes class).

3. The last thing I'd look for is how SQL Server deals with Initialization Vectors as you mentioned in your amended post. I want to say that it uses the authenticator parameter for this, but that's a wild guess.

EDIT

I was way off. Given what I have discovered, you cannot use a .NET class to decrypt text encrypted by SQL Server's built-in encryption because SQL Server adds a bunch of goo to what gets encrypted, including a random initialization vector. From Michael Cole's book "Pro T-SQL 2005 Programmer's Guide" (although 2008 does this the same way):

When SQL Server encrypts by symmetric key, it adds metadata to the encrypted result, as well as padding, making the encrypted result larger (sometimes significantly larger) than the unencrypted plain text. The format for the encrypted result with metadata follows this format:

• The first 16 bytes of the encrypted result represent the GUID of the symmetric key used to encrypt the data
• The next 4 bytes represent the version number, currently hard-coded as "01000000".
• The next 8 bytes for DES encryption (16 bytes for AES encryption) represent the randomly generated initialization vector.
• The next 8 bytes are header information representing the options used to encrypt the data. If the authenticator option is used, this header information includes a 20-byte SHA1 hash of the authenticator, making the header information 28 bytes in length.
• The last part of the encrypted data is the actual data and padding itself. For DES algorithms, the length of this encrypted data will be a multiple of 8 bytes. For AES algorithms, the length will be a multiple of 16 bytes.