Single-Sign-On(SSO) access from the SAS Viya Application to the Snowflake Database
The SAS Viya platform supports Single-Sign-On(SSO) authentication access to the Snowflake database. The SAS/ACCESS interface to Snowflake enables, and the Snowflake data connector enables the SAS Compute Server and CAS to connect to the Snowflake database. Both LIBNAME and CASLIB statements support the SSO against the Snowflake database. The SAS/ACCESS Interface to Snowflake and Snowflake data connector are distributed with the SAS Viya software deployment.
The SAS Viya platform can be configured with Microsoft Entra ID as an OIDC provider for initial user authentication. The steps to configure the SAS Viya platform for using OIDC with Microsoft Entra ID are listed in SAS documentation.
In this post, I discuss the configuration parts of the Entra ID OIDC Application, the Snowflake security integration object, and user permission at the Snowflake database for SSO-based access.
Azure Application Registration
Entra ID OIDC application configuration:
When creating an Entra ID OIDC application for the Snowflake database access, apart from standard API permissions, you must include the customized Apps (e.g Snowflake OAuth Resource) API permission with the scope defined and delegated role.
The Azure tenant admin must have consented to these roles and the status must be green with text like granted for specific domain/organization.
The following pics describe an Entra ID OIDC application with API permission to another Azure application (Snowflake OAuth Resource) defined for Snowflake database access.
Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.
Snowflake OAuth Resource application configuration:
It’s an Azure application with API permission and exposed API to get access tokens for the Snowflake database environment. The Microsoft Graph – User Read API permission is required for user sing-in event.
The Snowflake Resource Application includes an exposed API with a scope. The scope defined in this section should match with the user role defined at snowflake database. In this case “sesskion:scope:developer” scope is added to the Snowflake Resource Application. You can use scope as “all”, provided you have the same role given on the database side.
Snowflake Database environment Configuration
External security integration object at Snowflake database:
The Azure Snowflake OAuth Resource application requires an external security integration object at the Snowflake database with the matching information. The Database external security integration objects must contain the Azure Snowflake OAuth resource application ID and tenant ID. The Snowflake database administrator must create an external security object similar to the following SQL statements.
SQL:
create security integration external_oauth_azure_gel
type = external_oauth
enabled = true
external_oauth_type = azure
external_oauth_issuer = 'https://sts.windows.net/a7000009-1d96-416a-ad34-72fa00980006d/'
external_oauth_jws_keys_url = 'https://login.microsoftonline.com/a7099890009-1d96-416a-ad34-72fa00980006d/discovery/v2.0/keys'
external_oauth_audience_list = ('https://gel.abc.com/06dd0a8e-07de-43a2-8226-da22d1bf029e')
external_oauth_token_user_mapping_claim = 'upn'
external_oauth_snowflake_user_mapping_attribute = 'login_name';
The external_oauth_issuer= and external_oauth_jws_keys_url= parameters contain the URL with tenant ID of the Snowflake OAuth resource application.
The external_oauth_audience_list= parameter contains the URL with the domain name and Snowflake OAuth resource application ID.
Snowflake database user role:
The Snowflake database user must have the assigned role used in the Snowflake OAuth resource application scope. In this case, a ‘developer’ role was used in the OAuth Resource application, and the following SQL assigns the same role to a user in the Snowflake database environment.
SQL:
CREATE USER MyUser PASSWORD = '' LOGIN_NAME = 'MyUser@abc.com' DISPLAY_NAME = 'MyUser user'
COMMENT = 'Azure AD SSO user for MyUser ' EMAIL = 'MyUser@abc.com'
DEFAULT_ROLE = "DEVELOPER"
DEFAULT_WAREHOUSE = 'USERS_WH'
DEFAULT_NAMESPACE = 'USERS_DB.MyUser'
MUST_CHANGE_PASSWORD = FALSE;
grant role DEVELOPER to user MyUser;
SSO-based access from SAS Compute Server to Snowflake database
When the SAS Viya platform is configured with the Entra ID OIDC application, including the Snowflake OAuth Resource application, to provide initial user authentication. SAS users can use SSO-based access from the SAS Compute Server to the Snowflake database using the snow LIBNAME engine with the authscope= option. The Snowflake database must have the external security integration object configured using the information from the Azure tenant and Azure Snowflake OAuth resource application.
The following code describes the SSO-based access from the SAS Compute Server to the Snowflake database. Notice that there is no user ID and password but have an option authscope= option as part of the LIBNAME statement. The authscope= parameter using the application-id and scope configured in the Azure Snowflake OAuth resource application.
Code:
%let MYSERVER="partner.east-us-2.azure.snowflake.com" ;
%let MYSCHEMA=GELDMUI;
%let MYDB=USERS_DB ;
%let MYWH=USERS_WH ;
/* SSO based access to Snowflake database */
LIBNAME snow101 snow
server=&MYSERVER
database=&MYDB
schema=&MYSCHEMA
warehouse=&MYWH
preserve_names=yes
authscope="https://gel.abc.com/999999999-07de-43a2-8226-da22d1bf9999/session:scope:developer"
;
data snow101.fish_sas ;
set sashelp.fish ;
run;
Proc SQL outobs=10;
select * from snow101.fish_sas ;
run;quit;
Log:
........
...............
87 /* SSO based access to Snowflake database */
88 LIBNAME snow101 snow
89 server=&MYSERVER
90 database=&MYDB
91 schema=&MYSCHEMA
92 warehouse=&MYWH
93 preserve_names=yes
94 authscope="https://gel.abc.com/999999999-07de-43a2-8226-da22d1bf9999/session:scope:developer"
95 ;
NOTE: Libref SNOW101 was successfully assigned as follows:
Engine: SNOW
Physical Name: partner.east-us-2.azure.snowflake.com
96
101 data snow101.fish_sas ;
102 set sashelp.fish ;
103 run;
NOTE: There were 159 observations read from the data set SASHELP.FISH.
NOTE: The data set SNOW101.fish_sas has 159 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 1.86 seconds
cpu time 0.11 seconds
104
105 Proc SQL outobs=10;
106 select * from snow101.fish_sas ;
WARNING: Statement terminated early due to OUTOBS=10 option.
107 run;quit;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: The PROCEDURE SQL printed page 2.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.33 seconds
cpu time 0.05 seconds
108
SSO-based access from CAS to Snowflake database
When the SAS Viya platform is configured with the Entra ID OIDC application, including the Snowflake OAuth Resource application, to provide initial user authentication. SAS users can use SSO-based access from the CAS to the Snowflake database using the Snowflake CASLIB with the authscope= option. The Snowflake database must have the external security integration object configured using the information from the Azure tenant and Azure Snowflake OAuth resource application.
The following code describes the SSO-based access from the CAS to the Snowflake database. Notice that there is no user ID and password but have an option authscope= option as part of the CASLIB statement. The authscope= parameter using the application-id and scope configured in the Azure Snowflake OAuth resource application.
Code:
CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" );
%let MYSERVER="partner.east-us-2.azure.snowflake.com" ;
%let MYSCHEMA=GELDMUI;
%let MYDB=USERS_DB ;
%let MYWH=USERS_WH ;
/* SSO based access to Snowflake database */
caslib snowlib desc='Snowflake Caslib'
dataSource=(srctype='snowflake',
server=&MYSERVER,
database=&MYDB,
schema=&MYSCHEMA,
warehouse=&MYWH,
authscope="https://gel.abc.com/999999999-07de-43a2-8226-da22d1bf9999/session:scope:developer"
) libref=snowlib;
/*save a CAS table to Snowflake */
proc casutil incaslib="snowlib" outcaslib="snowlib";
load data=sashelp.cars casout="cars" replace;
save casdata="cars" casout="cars_cas" replace;
list files;
quit ;
/* load a Snowflake table to CAS*/
proc casutil incaslib="snowlib" outcaslib="snowlib" ;
load casdata="cars_cas" casout="cars_cas" replace;
list tables;
quit ;
cas mysession terminate;
Log:
........
...............
83 /* Initialize the variable with Snowflake Database connection details */
84 %let MYSERVER="partner.east-us-2.azure.snowflake.com" ;
85 %let MYSCHEMA=GELDMUI;
86 %let MYDB=USERS_DB ;
87 %let MYWH=USERS_WH ;
88
89 /* SSO based access to Snowflake database */
90 caslib snowlib desc='Snowflake Caslib'
91 dataSource=(srctype='snowflake',
92 server=&MYSERVER,
93 database=&MYDB,
94 schema=&MYSCHEMA,
95 warehouse=&MYWH,
96 authscope="https://gel.abc.com/999999999-07de-43a2-8226-da22d1bf9999/session:scope:developer"
97 ) libref=snowlib;
NOTE: 'SNOWLIB' is now the active caslib.
NOTE: Cloud Analytic Services added the caslib 'SNOWLIB'.
NOTE: CASLIB SNOWLIB for session MYSESSION will be mapped to SAS Library SNOWLIB.
NOTE: Action to ADD caslib SNOWLIB completed for session MYSESSION.
98
99
100 /*save a CAS table to Snowflake */
101 proc casutil incaslib="snowlib" outcaslib="snowlib";
NOTE: The UUID 'e8f2dccf-ab97-1649-a6b4-ac53dc4c57ab' is connected using session MYSESSION.
102 load data=sashelp.cars casout="cars" replace;
NOTE: The INCASLIB= option is ignored when using the DATA= option in the LOAD statement.
NOTE: SASHELP.CARS was successfully added to the "SNOWLIB" caslib as "CARS".
103 save casdata="cars" casout="cars_cas" replace;
NOTE: Performing serial SaveTable action using SAS Data Connector to Snowflake.
NOTE: Cloud Analytic Services saved the file cars_cas in caslib SNOWLIB.
NOTE: The Cloud Analytic Services server processed the request in 4.735969 seconds.
............
.................
107 /* load a Snowflake table to CAS*/
108 proc casutil incaslib="snowlib" outcaslib="snowlib" ;
NOTE: The UUID 'e8f2dccf-ab97-1649-a6b4-ac53dc4c57ab' is connected using session MYSESSION.
109 load casdata="cars_cas" casout="cars_cas" replace;
NOTE: Performing serial LoadTable action using SAS Data Connector to Snowflake.
NOTE: Cloud Analytic Services made the external data from cars_cas available as table CARS_CAS in caslib snowlib.
NOTE: The Cloud Analytic Services server processed the request in 2.891609 seconds.
......
.............
Important Links:
Authenticate to Microsoft Azure by Using Single Sign-On (Snowflake)
Scenario: OIDC with Microsoft Entra ID (Azure Active Directory)
LIBNAME Statement for Snowflake
Configuring Microsoft Entra ID for External OAuth
Find more articles from SAS Global Enablement and Learning here.
