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

[Sajid01]

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-doe...,

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

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

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

[Reeza]

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.

[Sajid01]

Thanks for the response, SAS EG / Server.

There is no Base  SAS installed on the Desktop.

[SASKiwi]

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.

[Sajid01]

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.

[SASKiwi]

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

[Reeza]

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.

---

[SASKiwi]

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

[Reeza]

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.

[Tom]

---

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

[Sajid01]

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.

[Patrick]

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?

[Sajid01 · Accepted solution]

Thanks everybody. The

database team reinstalled the client.

And everything worked.

Thanks