Creating a Symmetric AES128 Key in SQL Server based on Key and Initial Vector

Question

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

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;

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.

UPDATE 2014-07-07

I want to provide some background of my problem.

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.

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

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

Answer 1

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 (from CREATE SYMMETRIC KEY docs)

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.

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

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.

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.

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