SAS Viya accessing Azure Synapse with Single Sign-On
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.
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.
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.
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.
