Connecting to two databases on same SQL server via UNIX SAS

[TBarker]

Is it possible to define one DSN in the odbc.ini file on SAS UNIX and use it for multiple databases on that host?

We have two databases that are on the same host. One of the databases was previously defined in the odbc.ini file on our SAS UNIX server and was working fine, but now there is a second database on the same server and someone wants to connect to it via SAS. I was hoping that we could simply change the DSN definition to change the DATABASE=abc to DATABASE=   and then use the same DSN in LIBNAME statements for both databases by adding DATABASE=whatever to the libname statement, rather than having to define two different DSNs for the same host.

Works:

The DSN in the odbc.ini file is IP and the following LIBNAME statement works if we have DATABASE=IP in the DSN definition in the odbc.ini file:  LIBNAME IP SQLSVR  Datasrc=IP USER=IPME  PASSWORD=XXXX ;

Fails:

If we change to DATABASE=  in the DSN definition and try to connect to the two databases by adding DATABASE=[db] in the LIBNAME statement, both LIBNAME statements fail. These are the errors we get - different error for each LIBNAME statement:

LIBNAME IP SQLSVR  Datasrc=IP Database=IP USER=IPME PASSWORD=XXXX ;

ERROR: CLI error trying to establish connection: [SAS][ODBC 20101 driver][20101]Login failed for user 'IPME'.

LIBNAME OCM SQLSVR  Datasrc=IP Database=OCM SCHEMA=dbo USER=ocmme  PASSWORD=XXXX;

ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Driver Manager Message file not found. Please check for the value of InstallDir in your odbc.ini.

It seems like it should be possible to not have to create a new DSN entry for every database if they are on the same host, but perhaps not, since I haven't been able to find much helpful online about this and our testing thus far has failed. I'm hoping someone has some sparkling insight into making this work with only one DSN entry. If not, or it's just not possible on UNIX, then we will simply create a second DSN entry for the second database.

[JackHamilton]

If I understand your question correctly, you want to create an ODBC entry for the server that doesn't contain a DSN.  Then, in the libname statement, you use the QUALIFIER= option to specify the database name.

This is not well documented.

In explicit passthrough, you might need to specify a three level name.

[TBarker]

Thank you. I end up with the same error. I suspect what I want to do isn't possible on UNIX. SAS on UNIX seems to have a lot of limitations not experienced by SAS on Windows or Linux. I will submit a ticket to SAS tech support for their input. I'll post their response - and solution if there is one.

[SASKiwi]

One approach that should work would be to connect to one of the databases using your working LIBNAME statement then use SQL Passthru to read the actual data you want (replace my example LIBNAME with yours):

libname sqlsrvr odbc noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;";

proc sql;
 connect using sqlsrvr;
  create table Want  as 
  select * from connection to sqlsrvr
  (SELECT *
   FROM [MyDatabase].[MySchema1].[MyTable] A
   LEFT JOIN [MyDatabase2].[MySchema2].[MyTable2] B
   ON A.Key1 = B.Key1
   )
  ;
quit;

[TBarker]

Thank you. Unfortunately, the noprompt option is not supported on UNIX.

[JackHamilton]

There's some confusion here.  SAS/Access to Microsoft SQL Server uses a Unix ODBC driver in the background, but in my (limited) experience, you cannot specify exactly the same parameters using the sqlsvr engine as with the odbc engine.

Did you try my suggestion, which uses the sqlsvr engine with the QUALIFIER= option and does not require the noprompt option?

[TBarker]

Sorry, I just saw this message. I replied to your previous message. Yes, I tried it, but it still didn't work. I'm submitting a ticket to SAS tech support and will follow up with their response/solution. Thanks!

[JackHamilton]

The interface gives very unhelpful error messages.  For example, an invalid userid/password yields

ERROR: CLI error trying to establish connection: [SAS][ODBC SQL Server Wire Protocol driver]A value was not specified for the Domain connection option. A domain name or workgroup must be specified when using Domain NTLM.

[TomKari]

Yes, I've noticed MANY unhelpful CLI messages over the years. I think once you reach that point in the stack, you're out of the SAS ecosystem; makes you appreciate good SAS diagnostics all the more!

Tom

[TBarker]

Indeed!

[TBarker]

I agree. It makes troubleshooting so much harder.

[Quentin]

I never got it working, but I asked a similar question about DSN-less connections from Linux, and apparently it is supported. But since I don't actually manage the ODBC connections on our server, it looked like a little bit too much for me to try to set up with our admins.  

If you try it and get it working, please do leave a note as to how you were successful. Not knowing how ODBC connections work, as a user, it seems crazy that I need to request a new ODBC connection be defined on our SAS server, for each new SQL server database we create on our database server.  It's definitely one of those "there must be a better way" feelings.

https://communities.sas.com/t5/SAS-Data-Management/Example-of-DSN-less-SQL-Server-connection-from-Li...

[JackHamilton]

Our setup allows us to specify different databases on the same server with only one odbc.ini entry, but there's still a DSN.  I agree, it would be useful to be able to specify a connection completely without creating a new odbc.ini entry for each server, but I couldn't find a way to do it.  One problem is that connection strings can't be combined with AUTHDOMAIN= for authentication, and we don't want to hard-code passwords in a connection string.

What we do, leaving out many many lines from this condensed version:

odbc.ini

[dor_sqlserver_prod]
Driver=/opt/sas94_install/sashome/compute/AccessClients/9.4/SQLServer/lib/S0sqls27.so
Database=
HostName=SQLAGL.KP-DOR.KAISER.ORG

In my SAS code, I could libref to database "a" with 

 libname sqlliba sqlsrv dsn=dor_sqlserver_prod qualifier=A;

and to database "b" as 

libname sqllibb sqlsrv dsn=dor_sqlserver_prod qualifier=B;

The alternative would be to have two odbc.ini entries:

[A]
Driver=/opt/sas94_install/sashome/compute/AccessClients/9.4/SQLServer/lib/S0sqls27.so
Database=A
HostName=SQLAGL.KP-DOR.KAISER.ORG

[B]
Driver=/opt/sas94_install/sashome/compute/AccessClients/9.4/SQLServer/lib/S0sqls27.so
Database=B
HostName=SQLAGL.KP-DOR.KAISER.ORG

There are additional entries in odbc.ini and odbcinst.ini to define the drivers.  I hope I didn't abstract anything important out of this code when I edited it.

Either SAS or DataDirect (or both) didn't do a careful job when setting this up.  Sometimes case matters for the DSN and QUALIFIER, and sometimes it doesn't.  If you get the case wrong, sometimes the code partially works and sometimes it fails completely.  Also, it would be a big improvement if the Unix driver supported bulk loading.

[TBarker]

Yes, that's how we configured the odbc.ini file and how I've written the libname statements, but I'm still getting the errors, so there must be a setting in the odbc.ini file that is causing this to fail, since yours works.

[IP]
Driver=/sas94/sashome/AccessClients/9.4/SQLServer/lib/S0sqls27.so
Database=
HostName=swpdb

And these are my libname statements.

LIBNAME IP SQLSVR dsn=IP qualifier=IP schema=dbo USER=IPME PASSWORD=XXXX;

LIBNAME OCM SQLSVR dsn=IP qualifier=OCM schema=dbo USER=OCMME PASSWORD=XXXX;

We know the credentials are good and that the connection worked before we tried to accommodate multiple databases on it, so there must be something in our configuration that isn't quite right for this multi-db approach. Thank you for your help and confirming that it is possible. For now we're just going to make separate entries for each database so the user can get his work done and I'll reach out to SAS tech support for their insights. I'll post back here when I have more info from them.

As much as I'm sure SAS users connect to SQL databases via SAS, you'd think they'd make it a little more clear and user friendly.

[JackHamilton]

I think I included all the important settings from our configuration, but just in case I've attached a more complete version of our files (other entries are not included).  I added ".txt" to the name because this site doesn't allow the upload of .ini files.

We're on Solaris, so the path names might be structured differently.