Hi, I’m trying to create a generic project that it is going to decrypt different pgp files. I’m planning to create a configuration table in the sql server, but the passphrase would be encrypted. But I saw that EM doesn’t support encrypted column. What would be the best approach for my project?
thanks,
Renata
Hi @rkawana,
You can try to use the “Custom SQL” mode of the “Database command” action to insert values to an encrypted column. It supports multiple SQL statements. Similarly, you can use the “Custom SQL” mode of the “Import from database” action to open the key and then decrypt and import encrypted values.
I didn’t test this approach, but it worth a try.
I was able to read the encrypted (always encrypted) column from SQL DB.
I had to install the encryption key on my computer or in the server and change this configuration on the connection:
Thank you for sharing the solution, Renata.
Andrew, can you please clarify your statement? When you say it supports multiple SQL statements, do you mean to be executed at one time or simply it supports more than one type of SQL statement? Thanks!
Jacqueline, I meant that several SQL statements might be executed consecutively by a Single database command action. For example, the following statements will work as expected:
Hey Andrew,
Thanks for the clarification! I ask because we’re running into an error message when trying to execute more than one SQL statement in the same action, specifically when interacting with Snowflake. As a guideline, we’ve told our end users that one action should equal one statement, to make troubleshooting and reviewing easier, so it’s not a huge fire, but it would be good to understand why we’re running into this unexpected behavior.
Hi Jacqueline,
A few database drivers don’t support multiple statements or require additional configuration to make multiple statement queries work.
With Snowflake, you have to specify the MULTI_STATEMENT_COUNT option to make them work:
I tested the solution with setting the MULTI_STATEMENT_COUNT session variable to zero, and it worked for me. But currently, there is no way to do the same with the “Custom SQL” mode of the query builder or with the “Custom command” command.
There may be a way to set the MULTI_STATEMENT_COUNT variable globally or for a single user. But for now, I haven’t been able to find that way.
As a workaround, you can wrap your queries in the BEGIN … END block like this:
CREATE TABLE foo (bar INT);
INSERT INTO foo VALUES (1);
INSERT INTO foo VALUES (2);
END;