I am trying to get to the point where we can get data from a MSSQL server and bring that into our Viya Environment for use with CAS and Visual Analytics. I went through the steps in the deployment guide to configure and verify my ODBC Connection. Results from that were mixed and are listed in the following txt. At this point I am not sure if I have somehing configured wrong in SAS or what the problem is, any help would be greatly appreciated! thank you in advance Kevin ----------------- SAS Viya running on CENTOS7 The server is set up with MS-ODBC Drivers and unixODBC Can make a connection to the SQL server from isql and can run SQL commands to get data back from SQL server. Here are some steps that I have taken and the configuration files that I think will help in diagnosing the issue. ------------------------------------------------------------------------ odbci.ini [Test] Driver=ODBC Driver 13 for SQL Server Description=Test FGC SQL Server=132.2.2.2 Database=FGC_CAS [SQLServer] Driver=ODBC Driver 13 for SQL Server Description=My MS SQL Server Trace=No Server=132.2.2.2 odbcinst.ini [ODBC Driver 13 for SQL Server] Description=Microsoft ODBC Driver 13 for SQL Server Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2 UsageCount=1 [ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.1.1 UsageCount=1 SAS Config Files workspaceserver_usermods.sh #!/bin/bash -p # # Copyright (c) 2016, SAS Institute Inc., Cary, NC, USA, All Rights Reserved # # workspaceserver_usermods.sh # # This script extends workspaceserver.sh. Add local environment variables # to this file so they will be preserved. # USERMODS_OPTIONS= export ODBCSYSINI=/etc/ export ODBCINI=/etc/odbc.ini export ODBCINST=/etc/odbcinst.ini export LD_LIBRARY_PATH=:/opt/microsoft/msodbcsql/lib64/:$LD_LIBRARY_PATH ---------------------------------------------------------------------------------------- /opt/sas/viya/config/etc/sysconfig/compsrv/default/sas-compsrv # Config for compute server #COMPUTESERVER_LOG_PATH="/opt/sas/viya/config/var/log/compsrv/default" #Uncomment the following line to enable lockdown for the compute server #export COMPUTESERVER_LOCKDOWN_ENABLE=1 #ARM_SUBSYS=[ ARM_PROC | ARM_DSIO | ARM_ALL | ARM_NONE ] #export ARM_SUBSYS=ARM_PROC export ODBCSYSINI=/etc/ export ODBCINI=/etc/odbc.ini export ODBCINST=/etc/odbcinst.ini export LD_LIBRARY_PATH=:/opt/microsoft/msodbcsql/lib64/:$LD_LIBRARY_PATH --------------------------------------------------------------------- /opt/sas/viya/config/etc/cas/default/cas_usermods.settings export ODBCSYSINI=/etc export ODBCINI=/etc/odbc.ini export ODBCINSTINI=/etc/odbcinst.ini export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/microsoft/msodbcsql17/lib64/ When following the validation from procedures post install here are the results libname olib odbc dsn="SQLServer" user="sa" password=XXXXXXXXXXXXXXXXXX; NOTE: Libref OLIB was successfully assigned as follows: Engine: ODBC Physical Name: SQLServer OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 75 76 caslib newLib datasource=(srctype="odbc", username="sa", 77 password=XXXXXXXXXXXXXXXXXX, odbc_dsn="SQLServer"); NOTE: 'NEWLIB' is now the active caslib. NOTE: Cloud Analytic Services added the caslib 'NEWLIB'. NOTE: Action to ADD caslib NEWLIB completed for session MYSESSION. 78 79 proc casutil; NOTE: The UUID 'd21238d1-4b06-b740-885e-320a760686a0' is connected using session MYSESSION. 80 list files incaslib="newLib"; Caslib Information Library NEWLIB Source Type odbc Uid sa Session local Yes Active Yes Personal No Hidden No Transient No Odbc_dsn SQLServer ERROR: The connection to the data source driver failed. ERROR: ERROR: 523 80 ERROR: Function failed. ERROR: The action stopped due to errors. NOTE: Cloud Analytic Services processed the combined requests in 0.00303 seconds. 81 run; 82 83 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 96 Also have tried running this code 1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 75 76 LIBNAME SQLUP ODBC DATAsrc=test SCHEMA=dbo user=sa password=XXXXXXXXXXXXXXXXXX ; NOTE: Libref SQLUP was successfully assigned as follows: Engine: ODBC Physical Name: test 77 78 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 91 with this lib sqlup is added a table is diplayed however only the first char of the table name is shown and if you try and open the table a message reads The data set "SQLUP.t" is password protected and cannot be opened. Have also tried to make a connection to the SQL from Data Explorer on inside Enviroment Manager with the following results when trying to list tables: The connection to the data source driver failed. ERROR: 523 80 Function failed. The action stopped due to errors. code: 2-0-310002 uri: /casManagement/dataSources/cas~fs~cas-shared-default~fs~TESTSQL/tables path: /SASDataExplorer/ui/datasources/children traceId: 37866a73d0f05006 ---------------------------------------------------------------------- running this code from SASStudio 4 LIBNAME SQLUP ODBC DATAsrc=test SCHEMA=dbo user=sa password=XXXXXXXXXXXXXXXXXX ; NOTE: Libref SQLUP was successfully assigned as follows: Engine: ODBC Physical Name: test 74 75 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 88 Lib SQLUP is created and the table is visiable however when you try to open the table the following error is displayed CLI describe error: [ -------------------------------------------------------------------------------------
... View more
