How to get compatibility between C# and SQL2k8 AES Encryption?
- by Victor Rodrigues
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.
