Run command accessing environment variables

[cmcclellan]

I have Designer and Desktop Scheduler.

I have a Run command that looks like this

(real connection name redacted obviously).

The configuration file specifies the Snowflake login credentials (username and password) and has worked perfectly for years.

Today we are switching to key-pair authentication - pretty simple. I can generate the public and private keys, update the username in Snowflake with the public key, set the environment variable in Windows, etc....

a) When I run the workflow in Alteryx (just a normal Ctrl-R), when the Run command executes it prompts me for the private key passphrase ( so it doesn't "see" the environment variable )

b) When I try from the command line and run 

snowsql -c XXXXX -f bulk_load.sql

it works fine and doesn't prompt for the environment variable

c) When I schedule the workflow via Desktop Scheduler it also works fine and doesn't prompt for the environment variable

Why does a) not see/read the environment variable and the others do?

Has anyone else done this with Snowflake and key-pair authentication ?

 

[OTrieger]

@cmcclellan 
Are the keys provided via User Interface?

[cmcclellan]

No ... the public key is defined in Snowflake and the private key is referenced in the snowsql configuration file.  The passphrase is set as an environment variable.

That's how it should work, and it does work - except if I simply run the workflow (only then it prompts for the passphrase)

[OTrieger]

I see.

My first thoughts were due to user differences, the Scheduler have a different User, Alteryx User and Designer is using you user id. But then you say that when you run it directly from the CMD it working fine, and CMD will use your user id. 

[cmcclellan]

Yes, it's weird .... it's ONLY the manual execution (ie Ctrl-R) doesn't see the environment variable.  If I manually run the output that the Run Command tool creates OR schedule it, there's no error at all.  If I Ctrl-R then it prompts for passphrase :( 

[OTrieger]

You can try one more thing, running it with the Run as Analytical App just next to the run button and see if it is working well.

[cmcclellan]

Same problem, it prompts for the passphrase :( 

[apathetichell]

my recommendation would be to set the private key path/private key passphrase as registry keys. I posted the link to Snowflake's details on this earlier in the week on another thread.

my hypothesis -> alteryx is running as admin or not as you. you have the variables set -> admin/whatever desktop automation is running on does not. Can you clarify if you are running Alteryx admin or user version of Designer.

https://community.snowflake.com/s/article/How-to-set-up-ODBC-Driver-with-key-pair-authentication-to-...

so assuming you are following this guide -> https://knowledge.alteryx.com/index/s/article/Install-or-Upgrade-Designer-Scheduler-1583460913064 you are running the admin version of Alteryx. You can also try setting your passphrase as a global variable in an admin command prompt window and then rerunning. I'd recommend the entire registry key thing. I like it.

[cmcclellan]

I'm not using ODBC at all, I'm using snowsql to bulk load because that's the best way to use Snowflake.

I'm not sure how the rest explains why the scheduled execution does work, but the interactive execution does not work.

[apathetichell]

re: snowsql vs odbc -> make sense.

my core assumption is still the same - you are running one process as admin and your environmental variables are set as loca/userl. Can you check if desktop automation/scheduler is set to run as you (vs to run as admin)? that would explain why it can reach the variables and you can use the variables when running in cli - but when run as a workflow - Alteryx tries to run as admin and doesn't have access to the user environmental variables.