Oauth on SAS Grid 9.4M7

[freshstarter]

Hello,

We are in SAS 9.4M7 Grid suite running on Linux servers and recently we have got a requirement that we need to connect to snowflake database on Cloud. We have purchased the SAS Access to snowflake product and also we have installed Snowflake odbc driver on our servers. Our organisation said we have to connect to snowflake using oauth 2.0 authentication. All our Business users are not web based users and they have SAS EG installed locally.

Whether it is possible to use oauth in SAS Grid architecture ? I dont see any SAS documentation relates to this.  please confirm.

[JuanS_OCS · Accepted solution]

Hello @freshstarter ,

SAS 9 (including Grid) does not have OAuth support in the SASLogon application which is the one, together with the Metadata server, managing the identities in SAS. SAS Viya has this integration.

This does not mean it is not possible to use OAuth, but it will have to be a code-based solution.

The basics are described here, regardless if it is SAS or not: https://community.snowflake.com/s/article/HOW-TO-OAUTH-TOKEN-GENERATION-USING-SNOWFLAKE-CUSTOM-OAUTH

In SAS you can just use proc http, proc soap, proc json etc to simulate the curl command.

The main aspect is that you would need to configure the external OAuth: https://docs.snowflake.com/en/user-guide/oauth-azure 

After that, you can capture your OAuth token within the code and make the connection.

Pragmatically:

Step 1:

%let client_id = <YOUR_CLIENT_ID>;
%let client_secret = <YOUR_CLIENT_SECRET>;
%let token_url = <YOUR_OAUTH_TOKEN_URL>;

filename resp temp;

proc http
method = "POST"
url = "&token_url."
out = resp
headerout = headr
headerout_overwrite;

/* Set the request headers */
headers
'Content-Type' = 'application/x-www-form-urlencoded';

/* Set the request body (OAuth parameters) */
body "grant_type=client_credentials"
"&client_id=&client_id."
"&client_secret=&client_secret.";

run;

/* Read and parse the token from the response */
libname myjson json fileref=resp;

data _null_;
set myjson.root;
call symput('oauth_token', access_token); /* Save token to macro variable */
run;

%put &=oauth_token;

Step 2:

LIBNAME mySnowflakeLibname SASEQUITY
server = "<SNOWFLAKE_ACCOUNT>.snowflakecomputing.com"
port = 443
schema = "<SCHEMA>"
user = "<USERNAME>"
pwd = "&oauth_token."
db = "<DATABASE>"
authDomain = "OAuth";

[freshstarter]

Thank you so much @JuanS_OCS . Took time to disgest this new concept for me.

To my knowledge, it seems like a single oauth token will be utilised by all the SAS EG users irrespective of different SAS Metadata id's they do have. As we are not using  grant type as "authorisation code", for me it seems all SAS EG users will utilise one snowflake service account token. Am I understanding this correctly ? Please let me know. 

[JuanS_OCS]

Hi, no worries,

anything related to security requires digestion time when taken seriously.

To answer to your question: it depends.
What you say it’s possible when the user is the same and/or the policies for the OAuth token are set to allow that.

However normally that’s not the case and that’s the best practice. Different users would need different tokens. The tokens also expire in time and need to be refreshed into new tokens.

Therefore, you and your users need to adapt the code to the policies set for the token generation and lifetime.

[freshstarter]

Thank you so much @JuanS_OCS  for all the details.

[JuanS_OCS]

@freshstarter , you are very welcome!

[ArnabSAS]

Is not possible without snowflakes server ? 

We have already SAS application in Azure Active Directory. 

Also, are you using 'Client secret Value' or 'Client secret ID' ?