Thanks! I am looking for a solution for data at rest and transit.

I Know, SQL Server 2016 Always Encrypted is one of the solution for this problem, however moving our customer from SQL Server 2012/2014 to 2016 will take couple of years. Hence looking for some other solution.

Regards,

Jey

Hi Jeyakumar,

PB supports calling SQL Server’s Symmetric key for encryption and decryption. However, when you do the Open Symmetric Key, you need to use PB Dynamic SQL. Here is an example:

String ls_decryption_begin_sql,  ls_decryption_end_sql

ls_decryption_begin_sql = "OPEN SYMMETRIC KEY sym_Demo DECRYPTION BY PASSWORD = 'AppeonPassword123'"

ls_decryption_end_sql = "CLOSE SYMMETRIC KEY sym_Demo"

// open symmetric decryption

EXECUTE IMMEDIATE :ls_decryption_begin_sql ;

//retrieve data by DW or SQL using EncryptByKey or DecryptByKey function

….

// close symmetric decryption

EXECUTE IMMEDIATE :ls_decryption_end_sql ;

Regards,
Ken

Hi Jeyakumar;

  AFAIK  - the SS function that creates the key in SS cannot be retrieved. It can only be used internally within the SS realm ( I could be wrong on that interpretation). However if I am correct without the encryption key, you could not decrypt the data in PB (or any other language for that matter).

Regards ... Chris

If you are using encryption in the database, it should decrypt and encrypt the data automatically.