topic Connecting to Oracle failed in VDI environment in SAS Data Management

Connecting to Oracle failed in VDI environment

jan_seidel — Tue, 15 Jul 2014 12:59:39 GMT

Hi all,

I'm trying to connect to a Oracle data base using SAS 9.3 with a 4.3 EG client within a VDI environment. I'm using the following LIBNAME statement

libname MYLIB oracle

path='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=XXX)(PROTOCOL=TCP)(HOST=HostXXX)(PORT=1522)))(CONNECT_DATA=(SID=XXX)))'

user=xxx

password='xxx'

preserve_tab_names=yes

preserve_col_names=yes

schema=ORASCHEMA;

and get the following error

ERROR: ORACLE connection error: ORA-12170: TNS:Connect timeout occurred.

I've tried to connect via SQL Developer from within the same VDI environment and it worked (and I also can see data). Does the VDI environment cause the problem and if yes, how to come around?

Any ideas, comments are appreciated.

Thanks,

Jan

Re: Connecting to Oracle failed in VDI environment

jakarman — Tue, 15 Jul 2014 15:07:40 GMT

Why are you using the schema definition and the full path?
The schema definition is the one related to the TNSNAMES file that should be configured to your sas sessions (environment variable).
The full path definition is the line that should exactly match what is known in the TNSNAMES, effectively replacing all that.
A TNSNAMES file is not necessary in this way of coding. Every typo will cause an error.

Strange to see al those XXX there, that cannot be correct. Are you sure about 1522 normally seen is 1521. (oracle server default).

Re: Connecting to Oracle failed in VDI environment

jan_seidel — Wed, 16 Jul 2014 07:14:49 GMT

I've tried it using the TNS name, but still getting the same error. However, when using the SQL Developer the connection via TNS doesn't work either, with error message: No ocijdbc11 in java.library.path

But it did work when I've used the full path specification. So it maybe has something to do with java?

The XXX are just placeholders.
Yes, port 1522 is correct!

Re: Connecting to Oracle failed in VDI environment

jakarman — Wed, 16 Jul 2014 11:10:01 GMT

The string in SAS should be exactly the same as the one you got working in SQL developer. That are a lot of placeholders any typo will a possible cause for a failing connect.

Did you remove the schema specification with your libname?

This layout worked... (not java related. Ora client may be. Not really different

path=´(DESCRIPTION= (ADDRESS = (PROTOCOL = TCP) (HOST = <dns oracle-server>) (PORT = <port oracle-server>) ) (CONNECT_DATA = (SID=<ora sid>) ) )´

Re: Connecting to Oracle failed in VDI environment

jan_seidel — Wed, 16 Jul 2014 12:02:11 GMT

I've removed schema specification and tried the layout you've suggested, without success. However, meanwhile I had chat with the guys who're running the SAS environment. Currently they see two issues: 1. the SAS server "doesn't know" the data base connection, 2. the data base is sitting behind a firewall

The latter one, I thought, is not a problem, because I'm executing SAS EG client from within a VDI environment that is actually located *behind* the firewall. But that's of course not true. In a client server set-up it doesn't matter where the client is located. My major learning for today 🙂

Though problem is not solved yet, I'll close this discussion here. I think the SAS environment guys and the Oracle guys have to sort that out.

Thanks for your comments Jaap!