Set up a connection to Oracle by using metauser/metapass

[metalray]

Hello,

we wonder if it is possible to set up a connection to Oracle by using
the meta data information? Right now, we have strong doubts that this is even possible.

The following example does not work even if metauser and metapass are the same
as the Oracle user and password.

We use SAS 9.2 and Oracle 11g, both on Unix.

/*&_metauser*/

proc sql ;

connect to oracle as oradb (user="&metauser" password="&metapass"

path='ewh01');

create view WORK.TESTVIEW as
select *
from connection to oradb
(
SELECT * FROM DUAL
);
disconnect from oradb;
quit;
run;

/* ERROR: ORACLE connection error: ORA-01017: invalid username/password; logon denied. /*

/*
proc options;
run;

%put &sysuserid;
*/

[Patrick]

You can find all about this in the SAS Data Admin Guide

http://support.sas.com/documentation/cdl/en/bidsag/61236/PDF/default/bidsag.pdf

[metalray]

Hi Patrick,

Sorry, but I dont think you can.

There is hardly any information on
metauser/password information passed to
the Oracle server via PASS THROUGH.

PASS THROUGH is only talked about in respect
to Composite Information Server.

Note, if I substitute the METAUSER and METERPASS with
with the static values, the connection works.

I think its a problem with METAUSER (which only is a single
time mentionded in the Admin Guide regarding PROC METALIB)

Thanks,
metalray

[Patrick]

That's because explicit pass-through doesn't use SAS Metadata information (it's explicit).

You say that you've defined a user with the same name and credentials on the Oracle database (nothing to do with SAS metadata - it just happens that this user has the same name and credentials).

If using SQL developer or the like: can you connect from your machine with these credentials to the database?

Have you checked that the path entry (the Oracle alias name) in TNSNAMES.ORA actually points to the right database?

And if the connection works with SQL developer: In case your SAS code runs not on your machine but on a server then make sure that the TNSNAMES.ORA on this server is correct (N.B: The machine with the SAS Application Server not the SAS Metadata Server).

What the Admin Guide for which I've posted the link shows you in detail is how to properly set-up connectivity to an Oracle Server and it's databases in SAS metadata. Once this is done you can use library definitions in EG, DIS and the like almost as if these were SAS libraries. And with newer SAS EG versions you can even convert the code to explicit pass-through (it will read metadata information and use it to generate the connection string).

[shivas]

Hi,

Try this...Hope it helps..

First need to create oracle server in SAS MC

Second create library and then define schema and register all the oracle tables in SAS MC

Then try this code to connect

Options Metaserver="xxx.xxx.xxx.xxx" Metarepository=Foundation metauser="sasdemo" metapass="xxx@123" metaport=8561;

libname sample META liburi="SASLibrary?@name='LibraryName'";

LibraryName is what you defined in the second step (library name).

Thanks,

Shiva

[PaulHomes · Accepted solution]

Hi,

If you place the Oracle credentials in metadata on the user identity, or a group that they are a member of, with a specific authentication domain (e.g. OracleAuth) you can then refer to that authentication domain in your SQL Pass Through code. SAS will then look up the credentials for the current metadata identity from metadata using the specified authentication domain when connecting to Oracle.

proc sql;

connect to oracle(authdomain="OracleAuth" ...

There's more info on this in the following SAS usage notes:

• SAS Problem Note 31103: The SQL procedure's Pass-Through facility cannot manage metadata credentials
• SAS Usage Note 38204: Using the AUTHDOMAIN= option with SAS/ACCESS® 9.2 engines

Cheers

Paul

[metalray]

Hello Patrick,
thanks for the reply.
Yes, the connection to the database works fine.
The Oracle connection is not a problem. The setup is completed.

"Once this is done you can use library definitions in EG"
I know that, and that works fine too. But I wont to have
a SQL PASS THROUGH code independent of libraries registered already.

--Shiva, thanks again but we are well aware how to set up Oracle connections
and SAS ACCESS libraries. That all works fine. But I dont want to use a already registered
library in my case.

--Patrick
"And with newer SAS EG versions you can even convert the code to explicit pass-through (it will read metadata information and use it to generate the connection string)."
That sounds interesting. I hope it does not involve SAS ACCESS because I dont want to go via libraries.

--Paul, thanks for that. That was what I was looking for. You can also find the logins
when you use EG  > Tools -> SAS EG Explorer - > File - > Manage Logins