I need to decrypt a value in SQL that was encrypted in Ax using CryptoApi

(0) Share

Report

Posted on by BrandonSA

1,673

Hi everyone

I'm dealing with data in SQL that was encrypted in Ax 2012 using CryptoAPI. Now I'd like to know how to decrypt it inside of SQL, without the aid of Ax. my guess is that the way that I do this is by calling a Windows dll that uses the same algorithm that Ax uses.

My questions are:

- Is what I'm trying to do possible?

- What would the dll be that I would use and where would I find it within the operating system?

- What does the code look like in SQL? (having checked a website or 2, I've come to this. I need the ??? filled in. 'scuse the rough SQL)

CREATE ASSEMBLY ???
AUTHORIZATION dbo
FROM ???
WITH PERMISSION_SET = UNSAFE
GO

CREATE PROCEDURE decryptValue
@key BIGINT,
@pass BIGINT,
@ret NUMERIC
AS EXTERNAL NAME ???.decrypt
GO

DECLARE @key BIGINT,
DECLARE @pass BIGINT
EXEC DECRYPTvALUE 1111, 1111
PRINT Ret

Thanks

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (1)

Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report

The encryption key is stored on the machine when the encryption occurred, which likely isn't the DB server, so you simply don't have the key.

Also, accessing AX data directly though SQL is strongly discouraged; you should use AX integration features instead.

Was this reply helpful? Yes No
Suggested answer

Pravasti AK 2,985 on at

Like (0)

Report

Hi Bradon,

Create a Class, make it has property set to RunOn=Server

or

Create server static method and use the below code:

static void hide(Args _args)

{

CryptoApi cryptoApi;

Container cont,cont1;

ContainerClass cc;

;

/* Key is like a password, while encrypting and decrypting the phrase, the CryptoAPI class

has to instantiated with same key(99999999999). The phrases/words are encrypted & decrypted

based on the key*/

cc = new ContainerClass(["testXXX"]);

cont = CryptoApi.encrypt(cc.Blob());

cont1 = ContainerClass::blob2Container(CryptoApi.decrypt(cont));

info(Strfmt("Encrypted: %1",BinData::dataToString(cont)));

info(con2str(cont1));

}

Was this reply helpful? Yes No
Verified answer

Vilmos Kintera 46,149 on at

Like (1)

Report

We have faced a similar issue, where we have an e-mail and password combination for our eCommerce portal stored in the AX database. If we use the CryptoAPI inside AX due to the key being stored on the instance where it was encrypted, we would be in trouble for the web application trying to decrypt it, or even from the other load-balanced AOS serving the AIF service calls. So we have decided to implement our own Rijndael (AES) algorythm with a key and salt that is accessible for the web fronted as well, so we could authenticate our external users from the other end too, but store their credentials in a safe manner. Then it would be easy to implement an AES decryptor on SQL side or in .Net.

Also you could run PowerShell scripts easily from SQL, for which you can already find examples of encryption/decryption. However, like we said earlier, you will not be able to decrypt standard AX content, unless you implement your own encryption logic for the table and field.

Was this reply helpful? Yes No
BrandonSA 1,673 on at

Like (0)

Report

Thank you Vilmos

Was this reply helpful? Yes No