SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Example of DSN-less SQL Server connection from Linux

Accepted Solution Solved
Reply
PROC Star
Posts: 1,231
Accepted Solution

Example of DSN-less SQL Server connection from Linux

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.


Accepted Solutions
Solution
‎04-13-2017 01:28 PM
PROC Star
Posts: 1,231

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

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.

View solution in original post


All Replies
Super User
Posts: 3,106

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

[ Edited ]

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/

 

 

PROC Star
Posts: 1,231

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

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.

Super User
Posts: 3,106

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

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.

SAS Employee
Posts: 203

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

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

Solution
‎04-13-2017 01:28 PM
PROC Star
Posts: 1,231

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

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.

Super User
Posts: 3,106

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 521 views
  • 3 likes
  • 3 in conversation