SAS/ACCESS SNOWFLAKE LIBNAME

[KAZ97]

Hello -

I am attempting to access data stored on SNOWFLAKE via a SNOWFLAKE LIBNAME statement and am having problems.

Outside of SAS, I can access SNOWFLAKE via an OKTA connection with an OKTA username/password.  But obviously when I use that pair in the LIBNAME connection statement, I get an error that the pair does not exist (presumably because it is a OKTA username/password, not a SNOWFLAKE pair).  

Is there a way to connect to SNOWFLAKE directly using a RSA public/private key set up?  Are there ways SAS can connect via OKTA?

[Tom]

Did you check out this question?

https://communities.sas.com/t5/SAS-Programming/SAS-ACCESS-Interface-to-Snowflake-Connection-Successf...

It looks like that users problem was actually a firewall issue on their SAS server side.

[KAZ97]

Thank you, that is helpful.

Unfortunately, I am operating in a locked down SAS Viya environment and do not have the ability to modify DSN entries.  

Is there a method to authenticate with an external browser without having to modify the DSN?  Is there a way to use private/public RSA keys?

[SASKiwi]

Is your SAS Viya environment already configured and working with Snowflake? If it is then tweaking it for a different database and/or authentication method shouldn't be too difficult. If it isn't then authentication is just one of many things that will need to be configured, including Viya.

What authentication method you use will most likely be dictated by what your Snowflake administrators have allowed. I suggest you consult with them if you haven't already. 

[KAZ97]

Here is the closest I have gotten so far, using an encrypted RSA key pair:

libname sf snow
    server="<myaccount>.snowflakecomputing.com"
    user="<username>"
    database="<dbname>"
    schema="<schemaName>"
    conopts='authenticator=SNOWFLAKE_JWT;
            PRIV_KEY_FILE=<path-to-file>;
            PRIV_KEY_FILE_PWD=<pwd>
            ';

 

That throws the following error: 

Error finalize setting: Marshaling private key failed.

Any recommendations?

[SASKiwi]

Does this help? - https://community.snowflake.com/s/article/ODBC-connectivity-to-snowflake-fails-with-error-Marshaling...

[KAZ97 · Accepted solution]

Bingo!

I was using an encrypted key and trying to pass the PRIV_KEY_FILE_PWD parameter.  I switched to an unencrypted key (to avoid the issue in the above article) and got it to work.  For posterity, the final libname statement using an UNENCRYPTED key was:

libname sf snow
    server="<account_name>.snowflakecomputing.com"
    user="<user_name>"
    database="<database_name>"
    schema="<schema_name>"
    conopts="
        authenticator=SNOWFLAKE_JWT;
        priv_key_file=<path_to_key>;
        ";

If you use an encrypted key, you have to follow theses steps:

ODBC connectivity to snowflake fails with error "Marshaling private key failed" when using Key Pair ...

[Kurt_Bremser]

Since SAS/ACCESS modules for relational databases make use of the native clients which have to be installed and configured on the SAS server, the SAS admins need to do this in cooperation with the DB admins. After they have done this, they can tell you which credentials to use.