using freetds drivers in ODBC connections w/ SAS on linux

[daniel12345]

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.

[SASKiwi]

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

[daniel12345]

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!

[daniel12345]

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?

[SASKiwi]

@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.