Decrypt the encrypted data in qlikview application

[Anonymous]

hi

my client has encryted column in sql dB table. but need to decryt it in QV application. any idea how to do it.

thanks

[big_dreams]

What logic means function did he use to encrypt the column????

Regards

[Miguel_Angel_Baeyens]

Best way is to use the same SQL DB functions to decrypt, if they exist.

[Anonymous]

Hi Max

Its just the option selected in sql db says 'always encrypt on' no other function used

Hi Miguel,

dont want to decrypt it in sql

thanks

[Miguel_Angel_Baeyens]

There must be a function or stored procedure (or equivalent capability depending on the DBMS) to be used in the SQL statement that allows you to decrypt a value stored as encrypted on the database.

QlikView does not have any decryption function available for the LOAD script, you would have to build one on your own (likely using VBScript or similar) to decrypt, if possible, the value stored depending on the algorithm used. So instead of overcomplicating an issue on the LOAD side, use the native SQL function in the SELECT part.

Anyway, without an example on how the values are stored, which mechanism was used and the DBMS underlying, it's impossible to say.

[Colin-Albert]

Not something I have worked with, but this post may help.

SQL Server Encryption: Always Encrypted - Simple Talk

[Peter_Cammaert]

Good post, Colin. So this particular encryption/decryption has nothing to do with controlled encryption by using special functions. It has to do with data governance, and the requirement that data shouldn't be available unencrypted anywhere at all (not even when transferred from/to the RDBMS = "data in motion") except at the very endpoint = the client. The article says that even SQL Server itself has no idea about how to decrypt this data, because the necessary keys aren't necessarily stored in SQL Server.

I guess this type of encryption can only be reversed by using a encrypting/decrypting driver (as mentioned in the article) that is properly configured with certificates and keys.

The article uses SQL Server Management Studio which uses the .NET Framework Data Provider for SQL Server. For instructions on how to obtain and properly configure a Windows ODBC driver (either v13 or v17), see here: Using Always Encrypted with the ODBC Driver for SQL Server | Microsoft Docs. There is a link on this page to download the ODBC driver.

I think this will not work with the standard v11 ODBC/OLEDB driver as that one is already pretty old and was created before SQL Server 2016 was released.