Decrypting AES in SQL with key and IV

Question

I have a project requirement where I need to encrypt/decrypt a string in my application (c#.net) using AES and a key/IV (initialization vector) that I provide.

Easy enough.

Where it gets a little more confusing is I also have a requirement that I have to be able to decrypt the string using SQL also. Here is where I start to struggle.

Here is the code I am using (c#.net) to encrypt my string:

public string Encrypt(string str)
{
    byte[] IV = { 57, 87, 122, 110, 97, 109, 111, 53, 65, 95, 114, 101, 120, 73, 72, 84 };
    string key = "MKE7612Y4ADF8JD1";

    byte[] clearBytes = Encoding.UTF8.GetBytes(str);
    byte[] keyBytes = Encoding.UTF8.GetBytes(key);

    using (Aes encryptor = Aes.Create())
    {
        encryptor.Key = keyBytes;
        encryptor.IV = IV;

        using (MemoryStream ms = new MemoryStream())
        {
            using (CryptoStream cs = new CryptoStream(ms, encryptor.CreateEncryptor(), CryptoStreamMode.Write))
            {
                cs.Write(clearBytes, 0, clearBytes.Length);
                cs.Close();
            }
            str = Convert.ToBase64String(ms.ToArray());
        }
    }
    return str;
}

That all works great:

string encrypted = Encrypt("UAT-6127592");
// returns: 9ZzHICvqQsm/ZI/Uuh8QLQ==

That is exactly what I would expect. Now to the part I am struggling with - being able to decrypt this from SQL.

I am trying to do this using a symmetric key as follows:

USE tempdb;

CREATE SYMMETRIC KEY AES128SecureSymmetricKey 
WITH 
    ALGORITHM = AES_128, -- based on the length of my key
    IDENTITY_VALUE = N'9Wznamo5A_rexIHT',  -- the UTF8 equivalent of the decimal IV entered from my c# code above
    KEY_SOURCE = N'MKE7612Y4ADF8JD1' -- the same key I used in my c# code above
ENCRYPTION BY PASSWORD = N'MKE7612Y4ADF8JD1';

OPEN SYMMETRIC KEY AES128SecureSymmetricKey 
DECRYPTION BY PASSWORD = N'MKE7612Y4ADF8JD1';

DECLARE @strBase64 NVARCHAR(max);
SET @strBase64 = N'9ZzHICvqQsm/ZI/Uuh8QLQ=='; -- for testing, just hard coding the same string I encoded above

-- Converting the base64 string to HEX
DECLARE @strHex VARBINARY(MAX);
SET @strHex = CAST(N'' as xml).value('xs:base64Binary(sql:variable("@strBase64"))', 'varbinary(MAX)');

-- Try to decrypt the value
DECLARE @decrypted_hex_with_key NVARCHAR(MAX);
SET @decrypted_hex_with_key =  DecryptByKey(@strHex);
SELECT CONVERT(NVARCHAR(256), @decrypted_hex_with_key) AS DecryptedValue;

-- close and drop the key
CLOSE SYMMETRIC KEY AES128SecureSymmetricKey;
DROP SYMMETRIC KEY AES128SecureSymmetricKey;

The result of the query above returns NULL for "DecryptedValue".

I am quite certain that I am not using the key and IV values correctly in the query above. I have tried (what seems like) a thousand combinations though and haven't been able to get this to work quite right.

Can anybody provide some insights into what I am doing wrong?

I am a bit new to the cryptography world so it is quite possible I am way off base here! :)

Thanks for the help!!!

score 0 · Answer 1 · answered Mar 03 '20 at 16:30

The problem with your code is treating IDENTITY_VALUE in SQL Server and Initialization Vector (IV) in .NET as if they were the same things. They are not. IV is a standard parameter used to randomize the ciphertext and IDENTITY_VALUE is a SQL Server specific key identifier. See my answer here for more details.

SQL Server's methods expect ciphertexts which contain specific metadata and don't allow you to pass IV explicitly. Your best bet is to leave decryption to the app layer. If you really have to, you could try using SQLCLR with .NET code in SQL Server. This sounds like a bad idea though because you would have to somehow share the master key with SQL Server.

Decrypting AES in SQL with key and IV

1 Answers1