Decryption of AES 256 Encrypted Data

Question & Answer
,
1

I have a column in my Quicksight Dataset which is AES 256 encrypted. I need to be able to decrypt this field in Quicksight. I have the key saved but that is something I am not willing to hardcode.

  1. Is there a way I use the key as a dataset parameter?
  2. Is there a function I can use to perform the decryption?

We are using Snowflake as our DWH

2

@Abhra ,

for 1 : if it’s custom sql, then should be possible with dataset parameter .
for 2 : Encrpyt and decrpyt data at column level - #2 by Koushik_Muthanna

Kind regards,
Koushik

3

@Koushik_Muthanna
Thanks for #1
for #2, I am getting the following error
SQL compilation error: Unknown function PGP_SYM_DECRYPT|
|sourceErrorState:|42601|
|sourceException:|net.snowflake.client.jdbc.SnowflakeSQLException|
|sourceType:|SNOWFLAKE|
Maybe that function is not available in Snowflake

4

Hi @Abhra - Possibly you need to test the custom sql in snowflake first to ensure you are able to decrypt it. Then you need to put the details in QuickSight.

Regards - Sanjeeb

5

Hi @Abhra - Can you please check below documentation - DECRYPT | Snowflake Documentation

Regards - Sanjeeb

6

@Sanjeeb2022 I am getting the same error in Snowflake as well.
Unknown function PGP_SYM_DECRYPT

I have already tried DECRYPT before posting here:

select 
    to_varchar(
    decrypt(
        to_binary(
            'encrypted_value', 'HEX'
        ),
        'AES Key'
    ),
    'utf-8'
) as decrt

I get this error -
The following string is not a legal hex-encoded value:

7

Hi @Abhra - Ok. this problem is more related to snowflake rather a QuickSight issue. Can you please try using DECRYPT_RAW, see the below post here.

Tagging some of the experts for their opinion as well @sagmukhe @David_Wong @ErikG

Regards - Sanjeeb

8

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!