topic Re: SAS/ACCESS Interface to Snowflake Connection Successful Cannot View Tables in SAS Programming

SAS/ACCESS Interface to Snowflake Connection Successful Cannot View Tables

um3rella — Wed, 21 Apr 2021 19:59:07 GMT

I'm using SAS/ACCESS Interface to Snowflake with SSO using a ODBC DSN with AUTHENTICATOR=externalbrowser.

The connection is successful, however I cannot view any table as I get the error: The <table> does not exist or cannot be displayed.

Also none of the tables have a size.

Anyone else experience this same issue?

Re: SAS/ACCESS Interface to Snowflake Connection Successful Cannot View Tables

pchegoor1 — Thu, 22 Apr 2021 03:35:16 GMT

@um3rella

I am actually trying to test SAS/ACCESS Interface to SNOWFLAKE using Native SSO ( OKTA as IDP) per the link given here :

https://docs.snowflake.com/en/user-guide/admin-security-fed-auth-use.html#native-sso-okta-only.

But it does not work.

I have added the authenticator=https://<okta_account_name>.okta.com , where i have used my company okta account name as per details given here : https://docs.snowflake.com/en/user-guide/odbc-parameters.html ( search under authenticator conection paramater) . As per the documentation i have added this to the Snowflake DSN entry in the ODBC.ini file. I am then trying to make connection with the DSN option on the Libname statement as follows but it throws an Error .

LIBNAME SF snow DSN=SF_Test user='test01' password="xxxxxxx" schema=INFORMATION_SCHEMA  ;

ERROR: CLI error trying to establish connection: [Snowflake][Snowflake] (6) Assertion failure: error_in_response_json
ERROR: Error in the LIBNAME statement.

However my connection to the snowflake works if use a service account defined in snowflake with default authentication (snowflake) and then run the Libname with DSN option and the Service account and password.. But i have to use OKTA since it is my company standard for authentication

SAS support mentioned this ( OKTA) should work. In their response they told me

"The SASACCESS to Snowflake engine should support the various authentication options currently supported by the driver via the 'Authenticator' driver option added through the CONOPTS syntax or in a DSN within the odbc.ini file. The only real restriction is that the authenticator must support programmatic authentication vs something that requires third party interaction such as 'externalbrowser'. "

So i am surprised your connection using SSO with externalbrowser atleast works with no Errors.

I guess i have to reach out to SAS Tech support and do some more research. Have you reached out to them?

Re: SAS/ACCESS Interface to Snowflake Connection Successful Cannot View Tables

um3rella — Thu, 22 Apr 2021 16:32:48 GMT

Instead of using snow, have you tried sasiosnf?

Re: SAS/ACCESS Interface to Snowflake Connection Successful Cannot View Tables

pchegoor1 — Thu, 29 Apr 2021 23:24:56 GMT

Yes I did try that but still did not work.

It turns out that my Networking Team have now fixed the issue . There was a firewall blocking access to the OKTA URL from SAS server and once that was opened my issue got resolved.

@um3rella BTW Since you are of the SNOWFLAKE & SAS Users i found in this community, i wanted to check if you too faced this problem i reported here

https://communities.sas.com/t5/SAS-Programming/Temporary-Table-support-in-Snowflake-from-SAS/m-p/735319#M229069 in your Testing of Snowflake from SAS .Basically if i want to join a SAS dataset with a SnowFlake Table I am unable to make use of the TEMPOARARY Table concept in SNOWFLAKE ( similar to VOLATILE TABLE in Teradata) to do the join in a more efficient manner. Looks like R&D is still looking into this.