SAS Viya ODBC Connection Not Working (new install)

kgeorge130 — Fri, 20 Sep 2019 02:27:19 GMT

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: [

-------------------------------------------------------------------------------------

Re: SAS Viya ODBC Connection Not Working (new install)

AnandVyas — Fri, 20 Sep 2019 10:29:50 GMT

Hi @kgeorge130 ,

Since you are able to successfully test the connection outside of SAS, I believe the set-up is working fine. In the code, that you have shared I can see the libname statement was also successfully executed. My guess for these errors would be something related to data, like encoding etc.

I would recommend you to open a SAS Tech support track by emailing the details to support@sas.com to help you troubleshoot further.

Thanks!

Re: SAS Viya ODBC Connection Not Working (new install)

kgeorge130 — Tue, 01 Oct 2019 18:51:44 GMT

So just to let everyone know if you find this post, our issue was with Kerberos Delegation not working as a workaround I had to setup our access to the SQL server with username and password. Also had to change the encoding settings.

topic Re: SAS Viya ODBC Connection Not Working (new install) in Administration and Deployment

SAS Viya ODBC Connection Not Working (new install)

Re: SAS Viya ODBC Connection Not Working (new install)

Re: SAS Viya ODBC Connection Not Working (new install)