我也正在取得两列AES加密：存储在一个SQL Server的 2000 数据库这些列中的一个;另一种是存储在SQL服务器的 2008 数据库。

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.

随着第一列的数据库（2000年）不具有加密/解密机功能，我们决定做在应用程序级的加密逻辑，用.NET类，这两个。

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.

不过，作为第二列的数据库（2008年）允许这种功能，我们希望使用的数据库的功能，​​以更快的速度进行数据迁移，因为在SQL 2K的数据迁移比第二，它要小得多将持续，因为正在在应用程序级50个小时以上。

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;
}

这是SQL code样品：

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

这varbinaryToBase64是我们用来VARBINARY转换成我们用来存储字符串中的.NET应用程序相同格式的测试SQL函数。

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.

结果在C＃是： eg0wgTeR3noWYgvdmpzTKijkdtTsdvnvKzh + uhyN3Lo =

The result in C# is: eg0wgTeR3noWYgvdmpzTKijkdtTsdvnvKzh+uhyN3Lo=

同样的结果在SQL2k8是： AI0zI7D77EmqgTQrdgMBHAEAAACyACXb+P3HvctA0yBduAuwPS4Ah3AB4Dbdj2KBGC1Dk4b8GEbtXs5fINzvusp8FRBknF15Br2xI1CqP0Qb/M4w

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

我只是没有得到然而，这是我做错了。

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

你有什么想法？

编辑：有一点我认为是至关重要的：我有一个初始化向量在我的C＃code，16个字节。这四是没有设置SQL对称密钥，我能做到这一点？

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?

不过，即使没有填在C＃中四，我得到不同的结果，无论是在内容和长度。

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

推荐答案

有一对夫妇的事情，我想看看：

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

请务必确保明文是在内容和编码是相同的。 IIRC，溪流默认为UTF-8，而如果你的 VarBinaryToBase64 函数取nvarchar的参数，这将是统一code。

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.

确认这两个加密算法使用相同的块大小。在SQL中，您确定算法，当你调用 CREATE对称密钥。如果没有指定一个算法，它采用AES256。在.NET中使用 RijndaelManaged的，我相信默认块大小是128，但你可以将其设置为256（你不能，如果你使用爱依斯类）。

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).

的最后一件事我会寻找是SQL Server如何处理初始化向量作为您在修订后提及。我想说的是，它使用了身份验证参数对于这一点，但是这是一个疯狂的猜测。

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.

修改

我的路要走。鉴于我已经发现了，你不能使用.NET类来解密加密的SQL Server的内置加密文本，因为SQL Server添加了一堆粘粘什么被加密，包括随机初始化向量。从迈克尔·科尔的著作专业的T-SQL 2005程序员指南（虽然2008年这是否以同样的方式）：

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):

在SQL Server加密通过对称   键，它增加元数据到加密   结果，以及填充，使得   加密的结果大（有时   显著大于）   未加密的明文。对于格式   加密结果与元数据   遵循以下格式：

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:   的的前16个字节的加密结果再present的GUID   用于对数据进行加密对称密钥   在接下来的4个字节再present的版本号，目前硬codeD   为01000000。   在接下来的8个字节的DES加密（16字节AES加密）   再present随机生成   初始化向量。   再$ P $ 在接下来的8个字节的报头信息psenting选项   用于对数据进行加密。如果   认证器选项的情况下，这   标题信息包括一个20字节   SHA1哈希认证，使得   首部信息中的28字节   长度。   经加密的数据的最后部分是实际的数据和填补本身。   对于DES算法，这个长度   加密的数据将是8的倍数   字节。的AES算法，长度   将是16字节的倍数。    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.