BookmarkSubscribeRSS Feed

SAS Viya accessing Azure Synapse with Single Sign-On

Started ‎12-14-2022 by
Modified ‎12-14-2022 by
Views 1,299

SAS Viya has supported Single Sign-On (SSO) access to Azure resources(services) since the Stable 2020.1.3 release. This functionality enables SAS Viya users to access Azure resources (services) seamlessly without using the cumbersome device code authentication method. The Viya(CAS) user can access the Azure Synapse Pool database table using MS- SQL Server SAS Data Connector.

 

Our colleague @StuartRogers posted SAS Viya Azure AD Single Sign-On to Other Azure Services  discussing the required steps at Azure Active Directory to configure SAS Viya with OpenID Connect login. The post also features a list of supported Azure resources/services with Viya releases. Please go through the post for more detail.

 

This post discusses the access of Azure Synapse Pool databases from SPRE and CAS after Single Sign-On(SSO) is configured in the Viya environment.

 

SSO login to SAS Viya environment

 

When you open the SAS Viya application, the login manager page will have another sign-in option e.g. “Log-in with Open ID”. Once opted to log in with “Log-in with OpenID” it opens the Microsoft login page to sign in with a valid domain user-id and password. The domain user id is synced from the Azure Active directory to the SAS Viya user identity. The SAS user ID identity under SAS Environment Manager shall have the domain name and not just a plain user id name from local LDAP.

 

uk_1_SASViya_Accessing_AzureSynapse_With_SSO_1.png

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

 

Access to Azure Synapse Workspace - adding new user

 

The access to Azure Synapse Pool database is available through the Synapse Workspace. The Synapse Pool ( Serverless pool, Dedicated pool, SPARK pool), and databases are created under a named Synapse Workspace. Now that the SAS Viya environment is configured to use SSO (OIDC), the admin user can access the databases using the SSO authentication method. However, the non-admin user needs additional permission to access the Synapse workspace and SQL-Pool database. The AD Admin user used for Synapse Workspace has full access to SQL-Pools and databases. The Admin user can add new users and grant additional privileges to users.

 

uk_2_SASViya_Accessing_AzureSynapse_With_SSO_2.png

 

 

The following SQL statement can be used to add a user to different pool databases. The method to add a user to each pool database is little different, use the statement as needed.

 

Adding a user and role at Serverless Pool and database

 

--at the master database
CREATE LOGIN "gatedemo001@gelenable.sas.com" FROM EXTERNAL PROVIDER;

-- at User database 

CREATE USER gatedemo001 FROM LOGIN "gatedemo001@gelenable.sas.com";
ALTER ROLE db_owner ADD member gatedemo001;

 

Adding a user at SPARK Pool and database

 

--at the master database (built-in SQL-Pool )
grant connect any database to "gatedemo001@gelenable.sas.com";
grant select all user securables to "gatedemo001@gelenable.sas.com";

 

Adding user at Dedicated Pool and database

 

--at the dedicated pool database (dedicated SQL-Pool )
CREATE USER 'gatedemo001@gelenable.sas.com' FROM EXTERNAL PROVIDER;
EXEC sp_addrolemember 'db_owner', 'gatedemo001@gelenable.sas.com';

 

Azure Application (OIDC Apps) API Permissions

 

The Azure application (OIDC Apps) used in SAS Viya logon-manage to authenticate and generate tokens for Azure resource access shall have the “Azure SQL Database” and “Azure Storage” API permission. The API permission must have been granted by the Azure Tenant admin for the respective domain.

 

uk_3_SASViya_Accessing_AzureSynapse_With_SSO_3.png

 

Customized DSN

 

A customized DSN entry for each Azure SQL Pool in the odbc.ini file to access the database with hostname, database name, and SSL lib path, etc.

 

…….
………………
[sqls_gelws]
Driver=/opt/sas/viya/home/lib64/accessclients/lib/S0sqls28.so
Description=SAS ACCESS to MS SQL Server
AEKeyCacheTTL=-1
AEKeystorePrincipalId=
AEKeystoreClientSecret=
AlternateServers=
AlwaysReportTriggerResults=0
AnsiNPW=1
ApplicationIntent=0
ApplicationName=
ApplicationUsingThreads=1
AuthenticationMethod=1
BulkBinaryThreshold=32
BulkCharacterThreshold=-1
BulkLoadBatchSize=1024
BulkLoadFieldDelimiter=
BulkLoadOptions=2
BulkLoadRecordDelimiter=
BulkLoadThreshold=2
ColumnEncryption=Disabled
ConnectionReset=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
CryptoProtocolVersion=TLSV1,TLSV1.1,TLSV1.2,SSLv3, SSLv2
Database=geldwpool
Domain=
EnableBulkLoad=0
EnableQuotedIdentifiers=0
EnableServerSideCursors=1
EnableScrollableCursors=4
EncryptionMethod=1
FailoverGranularity=0
FailoverMode=0
FailoverPreconnect=0
FetchTSWTZasTimestamp=0
FetchTWFSasTime=1
GSSClient=native
HostName=utkumaviya4sqlsrv.database.windows.net
HostNameInCertificate=
#IANAAppCodePage=
InitializationString=
KeepAlive=0
Language=
LoadBalanceTimeout=0
LoadBalancing=0
LoginTimeout=15
LogonID=viyadep
MaxPoolSize=100
MinPoolSize=0
MultiSubnetFailover=0
PacketSize=-1
Pooling=0
PortNumber=1433
PRNGSeedFile=/dev/random
PRNGSeedSource=0
ProxyHost=
ProxyMode=0
ProxyPassword=
ProxyPort=
ProxyUser=
QueryTimeout=0
ReportCodepageConversionErrors=0
SnapshotSerializable=0
TrustStore=
TrustStorePassword=
ValidateServerCertificate=0
WorkStationID=
XMLDescribeType=-10
SSLLibName=/usr/lib64/libssl.so.10
CryptoLibName=/usr/lib64/libcrypto.so.10
…….
………………

 

SAS Compute server (SPRE) access to Azure Synapse with SSO

 

With user configuration at SAS Viya identity manager, customized ODBC.ini, and access to Azure Synapse Pool databases, the following LIBNAME statement can be used to access the database table.

 

Notice the code, there is no User-Id/Pwd in the LIBNAME statement instead “authtype=2” parameter is used to follow SSO-based authentication. The SAS Logon manager service requests the Azure Access Key and maintains it.

 

Code:

 

/* Access to Built-in SQL-Pool database*/ 
%let MYDSN=sqls_geldws;
libname azsqlws sqlsvr authtype=OAUTH2 noprompt="dsn=&MYDSN;" stringdates=yes schema=parquet;
Proc SQL outobs=20;
select * from azsqlws.baseball2;
run;quit;


/* Access to SPARK POOL Database */ 
%let MYDSN=sqls_gelspark;
libname azsqlws sqlsvr authtype=OAUTH2 noprompt="dsn=&MYDSN;" stringdates=yes;
Proc SQL outobs=20;
select * from azsqlws.baseball_prqt;
run;quit;


/* Access to Dedicated POOL Database */ 
%let MYDSN=sqls_gelpool;
libname gelpool sqlsvr authtype=OAUTH2 noprompt="dsn=&MYDSN;"  schema="dbo" stringdates=yes;
Proc SQL outobs=20;
select * from gelpool.baseball;
run;quit;

 

CAS access to Azure Synapse with SSO

 

With user configuration at SAS Viya identity manager, customized ODBC.ini, and access to Azure Synapse Pool databases, the following statement can be used to load the CAS table from synapse pool database tables.

 

Notice the code, there is no User-Id/Pwd in the CASLIB statement. When there is no User-id/Pwd provided in the CASLIB statement falls back to SSO-based authentication. The SAS Logon manager service requests the Azure Access Key and maintains it.

 

Code:

 

CAS mySession  SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true);


/* ## CAS load from Synapse Serverless database table. */
%let MYDSN='sqls_geldws' ;
%let MYSCHEMA='parquet';

caslib azsqldb desc='Microsoft SQL Server Caslib'
dataSource=(srctype='sqlserver',
sqlserver_dsn=&MYDSN,
schema=&MYSCHEMA);

proc casutil incaslib="azsqldb" outcaslib="azsqldb";
load casdata="baseball2" casout="baseball2" replace;
list tables;
quit;


/* ## CAS load from Synapse Spark pool  database table. */
%let MYDSN='sqls_gelspark' ;

caslib azsqldb desc='Microsoft SQL Server Caslib'
dataSource=(srctype='sqlserver',
sqlserver_dsn=&MYDSN,
schema=dbo );

proc casutil incaslib="azsqldb" outcaslib="azsqldb";
load casdata="baseball_prqt" casout="baseball_prqt" replace;
list tables;
quit;


/* ## CAS load from Synapse Dedicated pool database external table. */
%let MYDSN='sqls_gelpool' ;

caslib gelpool desc='Microsoft SQL Server Caslib'
dataSource=(srctype='sqlserver',
sqlserver_dsn=&MYDSN,
schema=dbo );

proc casutil incaslib="gelpool" outcaslib="gelpool";
load casdata="baseball" casout="baseball" replace;
list tables;
quit;

CAS mySession  TERMINATE;

 

Important Links:

 

Authentication: OIDC with Azure AD Scenario

 

Microsoft SQL Server Data Connector

 

SAS Viya Azure AD Single Sign-On to Other Azure Services    

 

Find more articles from SAS Global Enablement and Learning here.

Version history
Last update:
‎12-14-2022 02:07 PM
Updated by:
Contributors

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Labels
Article Tags