I have a routine that properly encrypts and then decrypts data, using CF, into a SQL table. My encrypt function is as simple as: encrypt('secure text', [seed key], 'AES')
I can use this to insert data into a MS SQL varchar(50) field, and then later retrieve those results using CF, use the CF decrypt function, and get my 'secure text' back in plain text. This has all worked perfectly well for years.
I now have a need to decrypt the 'secure text' using SQL directly, not ColdFusion. I have found the SQL EncryptByKey and DecryptByKey functions, but even though I'm using the same [seed key] string, and using the AES_128 SQL algorithm, it's not properly decrypting things. Is this just general incompatibility, and I should stop trying, or is there a way to align the CF encryption and the SQL decryption to make them compatible?
The SQL function I'm using looks like this:
CREATE SYMMETRIC KEY AES128SecureSymmetricKey
WITH ALGORITHM = AES_128
ENCRYPTION BY PASSWORD = N'[seed text]';
OPEN SYMMETRIC KEY AES128SecureSymmetricKey
DECRYPTION BY PASSWORD = N'[seed text]';
DECLARE @decrypted_str varchar(50)
SET @decrypted_str = DecryptByKey([Coldfusion-generated encrypted string]);
SELECT CONVERT(NVARCHAR(100), @decrypted_str) AS PlainText;
Anyone have any thoughts on how I might get this done? Much obliged!