BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
metalray
Calcite | Level 5

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;
*/

1 ACCEPTED SOLUTION

Accepted Solutions
PaulHomes
Rhodochrosite | Level 12

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:

Cheers

Paul

View solution in original post

6 REPLIES 6
metalray
Calcite | Level 5

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
Opal | Level 21

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
Pyrite | Level 9

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
Rhodochrosite | Level 12

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:

Cheers

Paul

metalray
Calcite | Level 5

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


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 6200 views
  • 8 likes
  • 4 in conversation