Configuring MS-SQL Server Data Source at Remote Data Agent (Cloud Data Exchange)
With the SAS Viya Cloud Data Exchange (CDE) environment, the MS-SQL Server data source is one of the widely used data sources. The CDE Data Agent supports the MS-SQL Server database access. The Remote Data Agent can access the MS-SQL Server database using an ODBC driver connection. The MS-SQL Server ODBC driver is provided with the SAS Viya and Remote Data Agent deployment. The CDE administrator must prepare an odbc.ini with MS-SQL Server connection details and place it at a location mounted to the Data Agent container. An administrator can configure the MS-SQL Server data source at both (Remote and Co-located) Data Agents.
In this post, I discuss the configuration of the MS-SQL Server data source at the Remote Data agent.
The Remote Data Agent is a containerized software and runs with docker runtime. The CDE administrator can mount an NFS location with an odbc.ini file while starting the Remote Data Agent container.
The following pics describe the local/NFS folder with an odbc.ini file mounted to the Remote Data Agent container and access to the MS-SQL Server database.
Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.
The following steps describe the MS-SQL Server data source configuration at the Remote Data Agent Server.
Prepare an odbc.ini file with MS-SQL Server connection details
The CDE connection to the MS-SQL Server is an ODBC connection. The MS-SQL ODBC driver is provided with the SAS Viya and Remote Data Agent deployment. There is no need for an additional Unix ODBC driver or MS-SQL Server driver. An administrator must prepare an odbc.ini file with the SQL Server database information and place it in a location (mounted) reachable from the Remote Data Agent container. The folder and files containing the odbc.ini file should have read permission for user “sas” (uid 1001:1001). The following is an odbc.ini example, you can customize it before using it in your environment.
Code:
RDA_HOST=`hostname ` ;
echo $RDA_HOST ;
tee /viya-share/gelenv/config/access-clients/odbc/odbc.ini > /dev/null << EOF
[rmtsql]
Driver=/opt/sas/viya/home/lib64/accessclients/lib/S0sqls28.so
Description=SAS ACCESS to MS SQL Server
Address=$RDA_HOST,1433
AlternateServers=
AnsiNPW=Yes
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=
FetchTSWTZasTimestamp=0
FetchTWFSasTime=0
LoadBalancing=0
LogonID=
Password=
QuotedId=Yes
ReportCodepageConversionErrors=0
ReportDateTimeType=1
SnapshotSerializable=0
QEWSD=2457822
EOF
sudo chown 1001:1001 -R /viya-share/gelenv/config/access-clients/odbc;
Update the sas-access.properties with the MS-SQL Server environment variable
The sas-access.properties file is meant for SAS/ACCESS engine-related environment variables. Users can include and update the respective access engine environment variables. The following variable is related to the MS-0SQL ODBC client.
Code:
ODBCINI=/gelenv/config/access-clients/odbc/odbc.iniStart the Remote Data Agent service with the odbc.ini location mounted
Start the RDA service with the local data folder (e.g. /viya-share/gelenv) mounted to the RDA container. The mounted folder contains the odbc folder odbc.ini file.
Code:
./container-manager start \
--license-path SASViyaV4_9CV11D_license.jwt \
--sasdata sasdata \
--vars da-vars.env \
--data sasdata/data \
--mount /viya-share/gelenv:/gelenv \
--access-vars sas-access.properties \
sas-data-agent-server-remote
Log in and authenticate a user for sas-viya CLI
The sas-viya cli is the user interface to manage and maintain the configurations at the SAS Cloud Data Exchange. An administrator must log in and authenticate a user for sas-viya CLI before creating data source connections.
Code:
export GELLOW_NAMESPACE=gelenv
export SAS_CLI_PROFILE=${GELLOW_NAMESPACE:-Default} ;
export SSL_CERT_FILE=~/.certs/${GELLOW_NAMESPACE}_trustedcerts.pem ;
sas-viya -k auth login --user geldmui@gelenable.sas.com --password '!wQ0eXXXXXXX$T@c' ;
sas-viya profile set-output text ;
Create an MS-SQL Security Domain at the RDA server
Create an MS-SQL domain on the RDA server to keep the SAS Viya user identity and database credentials in pairs.
Code:
sas-viya dagentsrv servers set-default --data-agent sas-data-agent-server-remote
sas-viya dagentsrv security domains create --domain MSSQLDOM1
sas-viya dagentsrv security domains list
Log:
jumpuser@utkuma-p04215-jump-vm:~$ sas-viya dagentsrv security domains create --domain MSSQLDOM1
The domain was successfully created.
jumpuser@utkuma-p04215-jump-vm:~/CDERemoteDA_Deploy$
jumpuser@utkuma-p04215-jump-vm:~/CDERemoteDA_Deploy$ sas-viya dagentsrv security domains list
ID Type Label Description
kerberos GSSCredential Kerberos credentials captured by logon and cached for delegation
DefaultAuth password The default authentication domain for SAS Viya
EsriAuth password EsriAuth Esri authentication credential domain
EsriPortalAuth password EsriPortalAuth Local Esri portal authentication credential domain
EsriPortalRefreshToken token EsriPortalRefreshToken Esri portal OAuth2 refresh credential domain
azure_oidc oauth2.0 azure_oidc
MSSQLDOM1 shadow
jumpuser@utkuma-p04215-jump-vm:~$Add Viya user identity to MS-SQL Security Domain at RDA server
Add the SAS Viya user identity with the MS-SQL Server database user ID and password to access the database objects from the SAS Viya applications. The MS-SQL Server database user password used in the following statement is a one-time user credentials setup. The Password of the database user never leaves the on-premises Remote Data Agent Server. It resides in the SAS Secrets Manager (Vault) at the Remote Data Agent Server.
Code:
### sastest1@gelenable.sas.com - SAS Viya user id. ###
### SA – MS-SQL Server database user ###
### xxxxxxxxxx – MS-SQL Server database password for user SA ###
sas-viya dagentsrv security credentials create --domain MSSQLDOM1 --identity sastest1@gelenable.sas.com --username SA --password 'xxxxxxxxxx'
sas-viya dagentsrv security credentials create --domain MSSQLDOM1 --identity sasadm@gelenable.sas.com --username SA --password 'xxxxxxxxxx'
sas-viya dagentsrv security credentials create --domain MSSQLDOM1 --identity geldmui@gelenable.sas.com --username SA --password 'xxxxxxxxxx'
sas-viya dagentsrv security credentials list --domain MSSQLDOM1
Log:
jumpuser@utkuma-p04215-jump-vm:~$ sas-viya dagentsrv security credentials list --domain MSSQLDOM1
Identity Username Identity Type
sastest1@gelenable.sas.com SA user
geldmui@gelenable.sas.com SA user
sasadm@gelenable.sas.com SA user
jumpuser@utkuma-p04215-jump-vm:~$Create an MS-SQL Server data source
The following statement creates an MS-SQL Server data source service with the ODBC DSN name defined in the odbc.ini. The statement uses the MS-SQL security domain name and has the database user-id password saved with the SAS Viya user identity.
Code:
sas-viya dagentsrv data-services create sqlsvr --name mssqlsrv1 --driver odbc --dsn rmtsql --domain MSSQLDOM1 --register-all
sas-viya dagentsrv services list
Log:
jumpuser@utkuma-p04215-jump-vm:~$ sas-viya dagentsrv services list
Name Type Domain Version Options
BASE base --- 2.6 ---
__SERVER__ server --- 2.6 PURGE_CACHE=30;CASE_SENSITIVITY=(OBJECT=F;COLUMN=F);CACHE=(NAME=AS;TIMEOUT=300)
mssqlsrv1 sqlserver MSSQLDOM1 2.6 CONOPTS=(DRIVER=odbc;ODBC_DSN=rmtsql);CASE_SENSITIVITY=(OBJECT=F;COLUMN=F)
jumpuser@utkuma-p04215-jump-vm:~$
Test the MS-SQL Server data source
When the following statement is executed, log in to the MS-SQL Server database using the Viya identity user to fetch the database password from the MS-SQL security domain and SAS security vault. The Viya identity user is authenticated against the Viya platform using the sas-viya CLI.
Code:
sas-viya dagentsrv data-sources test --name mssqlsrv1
Log:
jumpuser@utkuma-p04215-jump-vm:~$ sas-viya dagentsrv data-sources test --name mssqlsrv1
Successfully connected to data source mssqlsrv1.
jumpuser@utkuma-p04215-jump-vm:~$List MS-SQL Server database table and data from MS-SQL data source
With a successful test connection to an MS-SQL data source, the user can list the data table and data from the MS-SQL Server database using the sas-viya CLI.
Code:
sas-viya dagentsrv data-sources tables list --data-source mssqlsrv1 --catalog geldm --schema dbo
sas-viya dagentsrv sql --sql "select * from geldm.dbo.test_table " --dsn mssqlsrv1
Log:
jumpuser@utkuma-p04215-jump-vm:~$ sas-viya dagentsrv data-sources tables list --data-source mssqlsrv1 --catalog geldm --schema dbo
Name Type Native Catalog
test_table TABLE geldm
jumpuser@utkuma-p04215-jump-vm:~$
jumpuser@utkuma-p04215-jump-vm:~$ sas-viya dagentsrv sql --sql "select * from geldm.dbo.test_table " --dsn mssqlsrv1
id name value
== ==== =====
1 Pen 100
2 Pencil 200
3 Book 300
4 Notebook 400
jumpuser@utkuma-p04215-jump-vm:~$
SAS Access to MS-SQL Data Source via Remote Data Agent
With the MS-SQL data source configured at the Remote Data Agent, users can access MS-SQL Server database tables from the SAS Compute Server using the CDE LIBNAME statement. The following code describes the access to an MS-SQL Server database table via Remote Data Agent data source.
Code:
LIBNAME cdems1 cde dataagentname="sas-data-agent-server-remote"
dsn=mssqlsrv1 catalog=geldm schema=dbo preserve_tab_names=yes;
data cdems1.FISH_SAS ;
set sashelp.fish ;
run;
Proc SQL outobs=20;
select * from cdems1.FISH_SAS ;
run;quit;
Log:
81 LIBNAME cdems1 cde dataagentname="sas-data-agent-server-remote"
82 dsn=mssqlsrv1 catalog=geldm schema=dbo preserve_tab_names=yes;
NOTE: Libref CDEMS1 was successfully assigned as follows:
Engine: CDE
Physical Name: mssqlsrv1
84 data cdems1.FISH_SAS ;
85 set sashelp.fish ;
86 run;
NOTE: There were 159 observations read from the data set SASHELP.FISH.
NOTE: The data set CDEMS1.FISH_SAS has 159 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 1.65 seconds
cpu time 0.12 seconds
87CAS Access to MS-SQL Server Data Source via Remote Data Agent
With the MS-SQL data source configured at the Remote Data Agent, users can access MS-SQL Server database tables from the CAS using the CDE data connector. The following code describes the access to an MS-SQL Server database table via Remote Data Agent data source using CDE CASLIB.
Code:
CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true);
caslib cdems datasource=(
srctype="clouddex",
dataAgentName="sas-data-agent-server-remote",
catalog="geldm",
schema="dbo",
conopts="dsn=mssqlsrv1"
) libref=cdems;
proc casutil incaslib="cdems";
list files ;
run;
quit;
/* CAS load from CDE RDA MS-SQL table */
proc casutil incaslib="cdems" outcaslib="cdems";
load casdata="TEST_TABLE" casout="TEST_TABLE" replace ;
list tables ;
run;
quit;
/* Save CAS table to RDA MS-SQL database */
proc casutil incaslib="cdems" outcaslib="cdems" ;
droptable casdata="SAS_CARS" quiet ;
load data=sashelp.cars casout="SAS_CARS" ;
save casdata="SAS_CARS" casout="SAS_CARS" replace ;
list files ;
quit ;
cas mysession terminate;
Log:
81
82 caslib cdems datasource=(
83 srctype="clouddex",
84 dataAgentName="sas-data-agent-server-remote",
85 catalog="geldm",
86 schema="dbo",
87 conopts="dsn=mssqlsrv1"
88 ) libref=cdems;
NOTE: Executing action 'table.addCaslib'.
NOTE: 'CDEMS' is now the active caslib.
NOTE: Cloud Analytic Services added the caslib 'CDEMS'.
NOTE: Action 'table.addCaslib' used (Total process time):
NOTE: real time 0.011011 seconds
94
95 /* CAS load from CDE RDA MS-SQL table */
96 proc casutil incaslib="cdems" outcaslib="cdems";
NOTE: The UUID '0249bc7c-a935-124e-98f3-a8a8f3c3f622' is connected using session MYSESSION.
97 load casdata="TEST_TABLE" casout="TEST_TABLE" replace ;
NOTE: Executing action 'table.loadTable'.
NOTE: Connecting using the OAuth token for CAS user 'geldmui@gelenable.sas.com'.
WARNING: Using server default session timeout of 3600
NOTE: Cloud Analytic Services made the external data from TEST_TABLE available as table TEST_TABLE in caslib cdems.
NOTE: Action 'table.loadTable' used (Total process time):
NOTE: real time 0.831338 seconds
NOTE: SASHELP.CARS was successfully added to the "CDEMS" caslib as "SAS_CARS".
106 save casdata="SAS_CARS" casout="SAS_CARS" replace ;
NOTE: Executing action 'table.save'.
NOTE: Performing serial SaveTable action using SAS Data Connector to Cloud Data Exchange.
NOTE: Connecting using the OAuth token for CAS user 'geldmui@gelenable.sas.com'.
WARNING: Using server default session timeout of 3600
NOTE: Cloud Analytic Services saved the file SAS_CARS in caslib CDEMS.
NOTE: Action 'table.save' used (Total process time):
NOTE: real time 7.707428 seconds
Many thanks to Brian Hess, @bhess, for sharing the expert knowledge and help on this post.
Important Links:
Remote SAS Data Agent: Deployment and Administration Guide
Posts:
SAS Cloud Data Exchange for the SAS Viya Platform
SAS Viya Cloud Data Exchange Deployment and Configuration (Part -1)
SAS Viya Cloud Data Exchange Deployment and Configuration (Part -2)
Configuring BASE SAS Data Source at Remote Data Agent (Cloud Data Exchange)
Configuring Oracle Data Source at Remote Data Agent (Cloud Data Exchange)
Configuring SAS Federated Data Source at Remote Data Agent (Cloud Data Exchange)
Configuring DB2 Data Source at Remote Data Agent (Cloud Data Exchange)
Find more articles from SAS Global Enablement and Learning here.
