Connecting to Snowflake using JDBC and key pair authentication (ssh)

jcamp — Thu, 14 Oct 2021 18:20:46 GMT

I am working on the SAS Grid AIX system using SAS Enterprise Guide. I'm trying to connect to a Snowflake sandbox environment as a POC to prove that AIX's JDBC connector will connect to Snowflake. On top of that, I'm required to use key pairs (SSH RSA keys) to authenticate. I'm having trouble with the syntax.

I tested the key pairs using Snowsql. I was able to connect, so I know that the keys are working.

Here is what I tried:

proc sql;
connect to jdbc as C (driverclass="net.snowflake.client.jdbc.SnowflakeDriver"
URL="jdbc:snowflake://poc.east-us-2.azure.snowflakecomputing.com" authenticator=SNOWFLAKE_JWT private_key_file=/home/user/rsa_key.p8 private_key_file_pwd=mypwd classpath="/sas/scripts/snowflake/snowflake-jdbc-3.13.5.jar");
select * from connection to C (select count(*) from DB_NAME.SCHEMA_NAME.TABLE_NAME);
quit;

ERROR: Invalid option name authenticator.
ERROR: The C engine cannot be found.
ERROR: A Connection to the C DBMS is not currently supported, or is not installed at your site.

libname snow1 jdbc driver='net.snowflake.client.jdbc.SnowflakeDriver'
classpath='/sas/scripts/snowflake/snowflake-jdbc-3.13.5.jar'
URL='jdbc:snowflake://poc.east-us-2.azure.snowflakecomputing.com/?authenticator=SNOWFLAKE_JWT&private_key_file=/home/user/rsa_key.p8&private_key_file_pwd=mypwd
&user=user@mycompany.com&db=DB_NAME&schema=SCHEMA_NAME'
schema='SCHEMA_NAME'
;

ERROR: Error trying to establish connection: net.snowflake.client.jdbc.SnowflakeSQLLoggedException: Private key provided is invalid
or not supported: /home/user/rsa_key.p8: /home/user/rsa_key.p8
ERROR: Error in the LIBNAME statement.

libname snow1 jdbc driver='net.snowflake.client.jdbc.SnowflakeDriver'
classpath='/sas/scripts/snowflake/snowflake-jdbc-3.13.5.jar'
URL='jdbc:snowflake://poc.east-us-2.azure.snowflakecomputing.com'
user="user"
authenticator=SNOWFLAKE_JWT 
private_key_file='/home/user/rsa_key.p8'
private_key_file_pwd='mypwd' 
classpath="/sas/scripts/snowflake/snowflake-jdbc-3.13.5.jar"
schema='SCHEMA_NAME';

ERROR 22-7: Invalid option name AUTHENTICATOR.
ERROR 22-7: Invalid option name PRIVATE_KEY_FILE.
ERROR 22-7: Invalid option name PRIVATE_KEY_FILE_PWD.

Re: Connecting to Snowflake using JDBC and key pair authentication (ssh)

ChrisNZ — Fri, 15 Oct 2021 00:59:39 GMT

The second one is the closest, the others use unsupported syntax.

You can use the libname in proc sql by coding:

libname SNOW1 jdbc ...;
proc sql;
 connect using SNOW1;

I suppose the break before &user= is not in the program?

The syntax seems correct according to this page.

Now the only issue remaining seems to be the private key: snowflake rejects it.

What do the snowflake admins see?

I suggest you open a SAS Tech support incident so you can get guidance directly from the snowflake developers.

Re: Connecting to Snowflake using JDBC and key pair authentication (ssh)

jcamp — Fri, 15 Oct 2021 14:09:30 GMT

Thank you, yes the break before &user does not exist in the actual program. Based on your suggestion, I opened a SAS Tech support incident.

topic Connecting to Snowflake using JDBC and key pair authentication (ssh) in SAS Programming

Connecting to Snowflake using JDBC and key pair authentication (ssh)

Re: Connecting to Snowflake using JDBC and key pair authentication (ssh)

Re: Connecting to Snowflake using JDBC and key pair authentication (ssh)