topic Re: Connecting to oracle with Libname odbc : Error :TNS:net service name is incorrectly specified in SAS Programming

Connecting to oracle with Libname odbc : Error :TNS:net service name is incorrectly specified

Sajid01 — Fri, 13 Mar 2020 19:37:11 GMT

Hello
We have a new RHEL 7 server and SAS 9.4 M6 and which we have the oracle instant client and unixODBC installed

When I try to create a library with libname statement as follows :

libname testlib odbc dsn=datasyn user='xxx' password='xxx';

I get the following error

ERROR: CLI error trying to establish connection: [unixODBC][Oracle][ODBC][Ora]ORA-12162: TNS:net service name is incorrectly
specified
ERROR: Error in the LIBNAME statement.

I tried changing the case, adding single and double quotes around password and dsn values but there is no impact.
Oracle dba says there is no issue from their side.

I have seen these posts and they do not help
https://communities.sas.com/t5/SAS-Programming/Connecting-to-Oracle-via-libname-odbc-Schema-spec-doesn-t-work/td-p/17859,

https://communities.sas.com/t5/SAS-Programming/Writing-to-Oracle-database-using-ODBC-connection/td-p/35326

and https://communities.sas.com/t5/SAS-Communities-Library/A-quick-guide-to-connecting-to-Oracle-from-SAS/ta-p/331849

Wondering if anybody had similar issue and how they solved it.

Re: Connecting to oracle with Libname odbc : Error :TNS:net service name is incorrectly specified

Reeza — Fri, 13 Mar 2020 20:31:36 GMT

Are you trying to connect from a SAS Server or desktop? If it's a desktop try and see if you can connect via the ODBC control panel first.

Re: Connecting to oracle with Libname odbc : Error :TNS:net service name is incorrectly specified

SASKiwi — Fri, 13 Mar 2020 22:05:22 GMT

I suggest you review the definition of datasyn in your Oracle tnsnames.ora configuration file. Also try the SQLPLUS Oracle client on your SAS server to see if it connects to datasyn OK.

Re: Connecting to oracle with Libname odbc : Error :TNS:net service name is incorrectly specified

Sajid01 — Fri, 13 Mar 2020 22:16:47 GMT

Thanks for the response, SAS EG / Server.

There is no Base SAS installed on the Desktop.

Re: Connecting to oracle with Libname odbc : Error :TNS:net service name is incorrectly specified

Sajid01 — Fri, 13 Mar 2020 22:18:44 GMT

I have verified the DSN name and it is correct.

I don't have no access to sqlplus. Not authorized to connect from Desktop using SQL developer.

Re: Connecting to oracle with Libname odbc : Error :TNS:net service name is incorrectly specified

SASKiwi — Fri, 13 Mar 2020 22:34:09 GMT

@Sajid01 - This might be closer to the mark: http://www.dba-oracle.com/t_ora_12162_tns_net_service_name.htm

Or it is possible the ORACLE_HOME and ORACLE_SID settings are correct but SAS has not been configured for them:

https://documentation.sas.com/?docsetId=bidsag&docsetTarget=p1w3v98qca3sfzn1rzty2tngrfyq.htm&docsetVersion=9.4&locale=en

Re: Connecting to oracle with Libname odbc : Error :TNS:net service name is incorrectly specified

Reeza — Sat, 14 Mar 2020 14:33:21 GMT

You don't need sql plus, go to control panel and ODBC connections to see if the DSN entry is there.

@Sajid01 wrote:

I have verified the DSN name and it is correct.

I don't have no access to sqlplus. Not authorized to connect from Desktop using SQL developer.

Re: Connecting to oracle with Libname odbc : Error :TNS:net service name is incorrectly specified

Tom — Sat, 14 Mar 2020 17:19:19 GMT

@Sajid01 wrote:

I have verified the DSN name and it is correct.

I don't have no access to sqlplus. Not authorized to connect from Desktop using SQL developer.

Is SAS running on Unix or Windows? If you don't know the macro variables SYSSCP and SYSSCPL will show you.

Have someone that CAN run commands on the server confirm that the DSN name works.

Re: Connecting to oracle with Libname odbc : Error :TNS:net service name is incorrectly specified

SASKiwi — Sat, 14 Mar 2020 21:04:09 GMT

@Reeza - The OP is running SAS on a linux server (RHEL = Red Hat Enterprise Linux), so there is no Control Panel or ODBC Administrator (Windows only). In my experience, when the Oracle client software (SQL*NET) is installed to support Oracle connections sqlplus is part of this. It is always possible this has changed though.

Re: Connecting to oracle with Libname odbc : Error :TNS:net service name is incorrectly specified

Reeza — Sun, 15 Mar 2020 21:55:53 GMT

No, I connect to Oracle DB and no sqlplus is required on the Unix box, just the correct drivers and tsn.

OP updated their post, it wasn't clear earlier if it was server install or desktop.

Re: Connecting to oracle with Libname odbc : Error :TNS:net service name is incorrectly specified

Sajid01 — Mon, 16 Mar 2020 00:05:03 GMT

Thanks everybody.

More details are as follows.
We are building a new server with RHEL 7 and SAS 9.4M6. This is to replace an older server with RHEL 6 and SAS 9.4M4.
Everything works well on the old machine.
The older installation has been replicated on the new server including folder names and paths; tnsnames.ora, /etc/odbc.ini and sasenv_local.

The error is a bit surprising.

Wanted to have an idea if anybody else faced a similar issue.

Will be taking with DBA's.

Thanks once again.

Re: Connecting to oracle with Libname odbc : Error :TNS:net service name is incorrectly specified

Patrick — Mon, 16 Mar 2020 00:35:25 GMT

Have you also seen these instructions already? How to configure SAS/ACCESS Interface to ODBC on UNIX Platforms

This article and the linked ones on top of the article appear to pretty much cover what needs to be done - and what you could verify if it's this way in your environment.

One of the things from the article: "SAS attempts to use the first 64-bit ODBC driver manager that it finds." Did the sequence eventually change?

Re: Connecting to oracle with Libname odbc : Error :TNS:net service name is incorrectly specified

Sajid01 — Sat, 04 Apr 2020 14:13:36 GMT

Thanks everybody. The

database team reinstalled the client.

And everything worked.

Thanks