Highlighted

Encrypting with CF; decrypting with SQL?

New Here ,
Feb 27, 2018

Copy link to clipboard

Copied

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!

jtf

TOPICS
Advanced techniques

Views

317

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Encrypting with CF; decrypting with SQL?

New Here ,
Feb 27, 2018

Copy link to clipboard

Copied

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!

jtf

TOPICS
Advanced techniques

Views

318

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Feb 27, 2018 0

Have something to add?

Join the conversation