根据密钥和初始向量在SQL Server中创建对称的AES128密钥 [英] Creating a Symmetric AES128 Key in SQL Server based on Key and Initial Vector

问题描述

我有一些密钥和初始向量，我在.NET应用中将其用于加密目的。假设它们是：

I have some Key and Initial Vector, which I'm using for encryption purposes in my .NET app. Let's say they are:

Key = 0x0102030405060708090A0B0C0D0E
IV  = 0xA1A2A3A4A5A6A7A8A9AAABACADAE

在某些情况下，我想在SQL Server上（不在应用程序中）执行加密，但是对应用程序中的数据解密。我以为我可以在数据库中创建一个临时对称密钥并执行加密：

In some cases I would like to perform encryption on the SQL Server (not in the app), but decrypt the data in the app. I thought that I'll might be able to create a temporary symmetric key in the DB and perform encryption:

CREATE SYMMETRIC KEY #TempKey
    WITH ALGORITHM   = AES_128
    , IDENTITY_VALUE = ???
    , KEY_SOURCE     = ???
    ENCRYPTION BY PASSWORD = 'Pa$$w0rd';

OPEN SYMMETRIC KEY #TempKey
    DECRYPTION BY PASSWORD = 'Pa$$w0rd';

SELECT EncryptByKey(Key_Guid('#TempKey'), 'Secret Data');

CLOSE SYMMETRIC KEY #TempKey;
DROP SYMMETRIC KEY #TempKey;

但是我不知道应该提供什么IDIDITY_VALUE和KEY_SOURCE之间的共享密钥数据库和我的应用程序。

But I have no idea what should I provide as IDENTITY_VALUE and KEY_SOURCE to have a "shared" key between DB and my app.

更新2014-07-07

我想提供一些问题的背景信息。

I want to provide some background of my problem.

1. 首先，我使用的是EF Code First方法，当我需要执行一些数据库更新时，我使用代码优先迁移，我想进一步使用这种基于纯粹迁移的方法。不幸的是，在问题在EF Code First迁移中使用自定义逻辑，则无法在上或关闭方法。我唯一的方法-使用 Sql 方法。

1. First of all, I'm using EF Code First approach and when I need to perform some DB update I use Code First Migrations and I want to use further this pure migrations based approach. Unfortunately, as found during the question Use custom logic in EF Code First migration, there is no way to get current SqlConnection and SqlTransaction within Up or Down methods. The only way I have - execute custom SQL queries using the Sql method.

在下一个数据库更新中，我希望将一个数据加密柱。加密应满足两个条件：（1）数据应在客户端应用程序中可解密（而不是在SQL Server端），（2）对称密钥应以加密形式存储在客户端应用程序中，并且描述应为使用密钥容器中的非对称密钥来完成。不幸的是，这使CRL UDF在这里无用-当我尝试在UDF中获取基于密钥容器的密钥时，出现了权限异常： System.Security.SecurityException：请求类型为System.Security的权限。 Permissions.KeyContainerPermission

In the next DB update I want to encrypt a data in one column. The encryption should satisfy two conditions: (1) the data should be decryptable in the client app (not on the SQL Server side), (2) the symmetrical key should be stored in the client app in an encrypted form and the description should be done using asymmetrical key from a key container. Unfortunately, this makes CRL UDF useless here - when I try to get key container based key in the UDF, I get an permission exception: System.Security.SecurityException: Request for the permission of type System.Security.Permissions.KeyContainerPermission

在1和2期间进行了所有尝试之后，我最终了解到可以尝试使用 CREATE SYMMETRIC KEY 查询在数据库中创建临时对称密钥，但是我所做的所有尝试都以失败告终。

After all tries that I made during 1. and 2. I ended up with understanding that I can try to create the temporary symmetric key in the DB using CREATE SYMMETRIC KEY query, but all my tries of doing it are ended up without any success.

希望所有这些都将有助于理解问题并找到正确的解决方案。

Hope all this will help to understand the problem and find the right solution.

推荐答案

您的代码和nelucon的问题在于将SQL Server中的IDENTITY_VALUE和.NET中的初始化矢量（IV）视为相同。

The problem with your code and nelucon's answer is treating IDENTITY_VALUE in SQL Server and Initialization Vector (IV) in .NET as if they were the same things. They are not.

初始化向量是一个附加的随机值，被抽取到加密函数中以使密文难以预测。每次加密任何值时都应生成该密钥，因此它不是加密密钥的一部分。

Initialization Vector is an additional, random value which is 'pumped' into the encryption function to make the ciphertext less predictable. It should be generated each time you encrypt any value and so it is not a part of the encryption key.

IDENTITY_VALUE （来自创建对称密钥文档）

生成一个GUID，用于标记用新的对称密钥加密的数据。此标记可用于将密钥与加密数据进行匹配。

generates a GUID with which to tag data that is encrypted with the new symmetric key. This tagging can be used to match keys to encrypted data.

因此，基本上，IV是用于随机化密文和IDENTITY_VALUE是SQL Server特定的密钥标识符。

So, basically, IV is a standard parameter used to randomize the ciphertext and IDENTITY_VALUE is a SQL Server specific key identifier.

如果使用SQL Server加密某些纯文本，则所得密文将包含一些元数据，而不仅仅是加密的文本。它的确切结构取决于SQL Server的版本和所使用的加密算法，但是它可能包含一个版本块，所使用的加密密钥的GUID（这是从IDENTITY_VALUE派生的），Initialization Vector（初始化向量）（由SQL Server随机生成）和加密的文本。您的.NET应用程序希望加密的文本，加密密钥和IV均作为单独的值提供。

If you use SQL Server to encrypt some plain text the resulting ciphertext will contain some metadata, not just the encrypted text. The exact structure of it depends on SQL Server version and encryption algorithm used, but it will likely contain a version block, guid of encryption key used (this is derived from the IDENTITY_VALUE), the Initialization Vector (randomly generated by SQL Server) and the encrypted text. Your .NET app expects the encrypted text, encryption key and IV, all provided as separate values.

我的建议是坚持使用应用程序或SQL Server来管理加密。
如果确实需要在应用程序中解密在SQL Server中解密的内容，我认为您需要找出密文的确切结构并从中检索IV和加密的文本。

My suggestion is to stick to either application or SQL Server to manage encryption. If you really, really need to decrypt in your app something that was decrypted in SQL Server, I think you'll need to find out the exact structure of your ciphertext and retrieve the IV and encrypted text from it.

有关SQL Server中密文结构的更多信息此处和此处。

More info about cyphertext structure in SQL Server here and here.

这篇关于根据密钥和初始向量在SQL Server中创建对称的AES128密钥的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！