BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kgeorge130
Fluorite | Level 6

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

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

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
kgeorge130
Fluorite | Level 6

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.

 

 

View solution in original post

2 REPLIES 2
Anand_V
Ammonite | Level 13

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!

kgeorge130
Fluorite | Level 6

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.

 

 

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

Get Started with SAS Information Catalog in SAS Viya

SAS technical trainer Erin Winters shows you how to explore assets, create new data discovery agents, schedule data discovery agents, and much more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 5051 views
  • 2 likes
  • 2 in conversation