topic Re: using freetds drivers in ODBC connections w/ SAS on linux in SAS Data Management

using freetds drivers in ODBC connections w/ SAS on linux

daniel12345 — Fri, 28 Aug 2020 17:36:38 GMT

I am trying to use freetds drivers to connect to sqlserver using ODBC in SAS (on linux). Outside of SAS, the connections work, but inside of SAS, the connections do not work.

In SAS, I cannot get past a simple libname statement at the command-line:

libname mylib odbc dsn=SQLEDT_FREETDS user=myuser password=mypass;

My output:

[me@machine 9.4]$ ./sas -nodms NOTE: Copyright (c) 2016 by SAS Institute Inc., Cary, NC, USA. NOTE: SAS (r) Proprietary Software 9.4 (TS1M6) NOTE: This session is executing on the Linux 4.18.0-193.6.3.el8_2.x86_64 (LIN X64) platform. NOTE: Analytical products: SAS/STAT 15.1 SAS/ETS 15.1 SAS/IML 15.1 SAS/QC 15.1 NOTE: Additional host information: Linux LIN X64 4.18.0-193.6.3.el8_2.x86_64 #1 SMP Mon Jun 1 20:24:55 UTC 2020 x86_64 Red Hat Enterprise Linux release 8.2 (Ootpa) 1? libname mylib odbc dsn=SQLEDT_FREETDS user=myuser password=mypass; ERROR: CLI error trying to establish connection: [unixODBC][FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist : [unixODBC][FreeTDS][SQL Server]Unable to connect to data source : [unixODBC][FreeTDS][SQL Server]Unknown host machine name. ERROR: Error in the LIBNAME statement.

With that being said, freetds appears to work outside of SAS just fine:

[me@machine 9.4]$ isql -v SQLEDT_FREETDS "myuser" "mypass" +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> quit [me@machine 9.4]$ tsql -S SQLEDT_FREETDS -U myuser -P mypass locale is "en_US.UTF-8" locale charset is "UTF-8" using default charset "UTF-8" 1> quit

My ODBC DSN is very simple:

My DSN is very simple: [SQLEDT_FREETDS] Driver=FreeTDS Server=10.10.10.128 Port=1433

Is this DSN connection definition too simple? Am I missing something that SAS needs that other tools don't need?

In this particular case, the username/pass corresponds to a local sqlserver-auth account (I have an interest in getting an AD-based account working too, but I can't seem to get a simple account to work)

Any advice is appreciated; I am not sure what my next steps would be.

Re: using freetds drivers in ODBC connections w/ SAS on linux

SASKiwi — Sat, 29 Aug 2020 23:24:38 GMT

Have SAS ODBC connections to SQL Server ever worked on this server, perhaps with other ODBC drivers, or is this a first-time setup?

Re: using freetds drivers in ODBC connections w/ SAS on linux

daniel12345 — Sun, 30 Aug 2020 17:23:05 GMT

This is a new SAS server so it's the first time set-up we're going through. With that being said, SAS ODBC connections to the same SQLserver appear to work for people that use SAS on Windows, so I think it's localized to me and my linux server.

I verified my DSN works by using it in other places (isql/tsql and R), so I think it might be something related to my SAS syntax or something SAS needs in my odbc.ini that I am missing if connecting from linux.

I tried entering more information in my odbc.ini:

[SQLEDT_FREETDS]
Driver=FreeTDS
Description=FreeTDS testing
Trace=No
Database=mydatabase
Server=10.10.10.128
Port=1433
TDS Version = 8.0

but this didn't seem to make an impact.

Thanks for the reply - any thoughts on next steps would be appreciated!

Re: using freetds drivers in ODBC connections w/ SAS on linux

daniel12345 — Sun, 30 Aug 2020 18:54:51 GMT

A small update: I can get a DSN-less connection working to our SQL server with this syntax:

LIBNAME mylibref ODBC NOPROMPT="driver={FreeTDS};Server=10.10.10.128;port=1433;database=mydatabase;uid=myuser;pwd=mypassword"

So I think there's something weird with SAS + my DSN or my syntax for trying to use the DSN:

libname mylib odbc dsn=SQLEDT_FREETDS user="myuser" password="mypassword";

Does SAS need something outside /etc/odbc.ini for this to work?

Re: using freetds drivers in ODBC connections w/ SAS on linux

SASKiwi — Mon, 31 Aug 2020 00:28:53 GMT

@daniel12345 - I think you are on the money if DSN-less connection strings work, and you are thinking something needs to be added to odbc.ini.Not an area I've had experience with unfortunately. Someone with linux experience needs to chip in.

Edit: I've moved your post to Data Management to be more on topic. Experts like @DBailey may be able to help.