topic Re: Example of DSN-less SQL Server connection from Linux in SAS Data Management

Example of DSN-less SQL Server connection from Linux

Quentin — Tue, 11 Apr 2017 20:01:09 GMT

If you have a successfull example of a DSN-less connection string used to connect to a MS SQL Server database from SAS 9.4 on a linux server, could you please show me what it looks like?

I have a working explicit pass-through connection that uses a DSN, but would like to convert to DSN-less. I've been playing with different permutations of complete= / noprompt= / required= etc, but don't even think I'm coming close. Not even sure what to use for the driver name.

We're running 9.4M3. I suspect the relevant part of the odbc.ini file are:

[MYDATABASE]
Driver=/sas/sasinstall/AccessClients/9.4/SQLServer/lib/S0sqls27.so
Database=MY DATABASE
HostName=[ip address]
PortNumber=[port number]

Thanks,

--Q.

Re: Example of DSN-less SQL Server connection from Linux

SASKiwi — Tue, 11 Apr 2017 20:52:22 GMT

Well, we use this approach exclusively but under Windows only. Here are examples that work for us. The first one uses Windows Authentication so I'd suggest starting with the second example which is pretty generic. Your Linux Administrator should be able to tell you the ODBC driver name.

proc sql;
  connect to odbc (noprompt = "server=SQLServerName;DRIVER=SQL Server Native Client 11.0;Trusted_Connection=yes;DATABASE=MyDatabase;");
  connect to odbc (noprompt = "server=SQLServerName;DRIVER=SQL Server Native Client 11.0;Uid=myUsername;Pwd=myPassword;DATABASE=MyDatabase;");
quit;

This link may also be helpful:

https://www.connectionstrings.com/sql-server-native-client-11-0-odbc-driver/

Re: Example of DSN-less SQL Server connection from Linux

Quentin — Wed, 12 Apr 2017 14:21:19 GMT

Thanks @SASKiwi. Worked like a charm from Windows but I think I'm going to submit to SAS tech support to ask about Linux.

I poked around a bit, and tried specifiying driver in different ways:

  connect to sqlsvr (noprompt = "SERVER=MyServer;DRIVER=/sas/sas94/.../lib/S0sqls27.so;UID=MyUser;PWD=MyPwd;DATABASE=My Database;");
  connect to sqlsvr (noprompt = "SERVER=MyServer;DRIVER=DataDirect 7.1 SQL Server Wire Protocol;UID=MyUser;PWD=MyPwd;DATABASE=My Database;");
  connect to sqlsvr (noprompt = "SERVER=MyServer;DRIVER=SAS Institute, Inc 7.1 SQL Server Wire Protocol;UID=MyUser;PWD=MyPwd;DATABASE=My Database;");

The first two fail with:

ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Data source name not found and no default driver specified

The laset fails with:

ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Specified driver could not be loaded

So maybe that last one is close, and it's a problem with our odbinst.ini file.

The docs state that complete= prompt= and required= are not supported on Unix, so I suppose it's possible that noprompt= isn't supported either.

Re: Example of DSN-less SQL Server connection from Linux

SASKiwi — Wed, 12 Apr 2017 20:11:08 GMT

Yep, SAS Tech Support is a good move. It's unfortunate that the use of connection strings with SAS/ACCESS products is not well documented for all supported operating systems. Hunting around for answers is a frustrating exercise.

Re: Example of DSN-less SQL Server connection from Linux

JBailey — Wed, 12 Apr 2017 20:28:19 GMT

Hi @Quentin

This SGF paper may help you:

An Insider's Guide to SAS/ACCESS Interface to ODBC

The paper includes a trick that may allow you to pull the information for your DSN-less connection from a DSN connection. It is a pretty cool trick, but it doesn't work for ever ODBC driver.

Are you using SAS/ACCESS Interface to ODBC or SAS/ACCESS Interface to Microsoft SQL Server?

Best wishes,

Jeff

Re: Example of DSN-less SQL Server connection from Linux

Quentin — Thu, 13 Apr 2017 17:26:53 GMT

Thanks @JBailey. I couldn't get that trick to work. I'm using SAS/ACCESS Interface to MS SQL Server.

Got a good reponse to my tech support ticket. They said yes, it should be workable from linux, and gave and example:

libname sqwireno sqlsvr noprompt='Driver=SQLServer;Address=machine1.reg.company.com,1433;Database=users;UID=myuser;PWD=mypass;' schema=myschema;

Also referred me to Step #8 of this note: http://support.sas.com/kb/48/515.html Apparently the key point is to make sure those environment variables are set correctly, then make sure the odbcinst.ini file is configured correctly (see the full code tab), and use the driver name from the odbcinst.ini file in the connection string. I haven't got it working yet, as I will need to get with an admin to monkey with the odbcinst.ini file, but looks promissing.

Re: Example of DSN-less SQL Server connection from Linux

SASKiwi — Fri, 14 Apr 2017 00:25:38 GMT

@Quentin many thanks for posting a connection example that works for Linux. That is very handy to know.

Re: Example of DSN-less SQL Server connection from Linux

SamLoflin — Mon, 26 Feb 2018 17:15:18 GMT

Hi, all.

In case you all were not able to get DSN-less connections working and need additional clarification, I have included a section from an email which I recently sent to a customer.

I think what may throw some folks off is that Progress/Data Direct does DSN-less connections a bit differently than when using a unixODBC driver manager. Whereas you can do DSN-less connections when using a unixODBC driver manager and not reference any INI files at all, Progress/Data Direct requires that you at least reference an odbcinst.ini file entry.

When doing DSN-less connections with Progress/Data Direct ODBC client components (we bundle these in with SAS ACCESS TO MS SQL Server), you do not have to have an odbc.ini file with DSN entries for SQL Server data bases but you must create an entry in an odbcinst.ini and you must set the ODBCINST environment to point to this odbcinst.ini file.

I have set up a test environment on a Linux server here and the following shows how I got this to work. I have set things up so that I can use either reference DSNs in an odbc.ini file or do DSN-less connections.

My odbc.ini file looks like this:

[ODBC Data Sources]

sqlserver=SAS Institute, Inc 7.1.5 SQL Server Wire Protocol

[ODBC]

IANAAppCodePage=4

InstallDir=/users/sasswl/acc_mssql_dsnless/sqls715

Trace=0

TraceFile=/tmp/odbctrace.out

TraceDll=/users/sasswl/acc_mssql_dsnless/sqls715/lib/S0trc27.so

[sqlserver]

Driver=/users/sasswl/acc_mssql_dsnless/sqls715/lib/S0sqls27.so

Description=SAS Institute, Inc 7.1.5 SQL Server Wire Protocol

AnsiNPW=1

AuthenticationMethod=1

Database=users

EnableQuotedIdentifiers=0

HostName=blackfin.na.sas.com

Language=

PortNumber=1433

My odbcinst.ini looks like this:

[ODBC_Drivers]

sqlserver_dsnless=SAS Institute, Inc 7.1.5 SQL Server Wire Protocol

[ODBC]

#This section must contain values for DSN-less connections

#if no odbc.ini file exists. If an odbc.ini file exists,

#the values from that [ODBC] section are used.

[sqlserver_dsnless]

Driver=/users/sasswl/acc_mssql_dsnless/sqls715/lib/S0sqls27.so

I set ODBC-related environment variables in the sasenv_local file in the $SASROOT/bin directory on Linux like this:

export ODBCHOME=/users/sasswl/acc_mssql_dsnless/sqls715

export ODBCINI=$ODBCHOME/odbc.ini

export ODBCINST=$ODBCHOME/odbcinst.ini

export LD_LIBRARY_PATH=$ODBCHOME/lib:$LD_LIBRARY_PATH

The following excerpt from my SAS Log shows how I can successfully use SAS ACCESS TO MS SQL SERVER to do either DSN or DSN-less connections in the same SAS session.

1? libname sqltest1 sqlsvr user=xxxx pw=xxxx dsn=sqlserver;

NOTE: Libref SQLTEST1 was successfully assigned as follows:

Engine: SQLSVR

Physical Name: sqlserver

2? libname sqltest2 sqlsvr noprompt="Driver=sqlserver_dsnless;AnsiNPW=1;AuthenticationMethod=1;Database=users;EnableQuotedIdentifiers=0;HostName=blackfin.na.sas.com;PortNumber=1433;UID=xxxx;PWD=xxxx";

NOTE: Libref SQLTEST2 was successfully assigned as follows:

Engine: SQLSVR

Physical Name:

NOTES:

Again, I set things up so that I could do either DSN or DSN-less connections in the same SAS session.

If you are only doing DSN-less connections, you could do away with the odbc.ini file altogether and only reference an odbcinst.ini file by only setting the ODBCINST environment variable...BUT the Progress/Data Direct driver manager relies on an [ODBC] stanza and if you are not referencing an odbc.ini (where you normally include this stanza) you must include this [ODBC] stanza in your odbcinst.ini file.