DATA Step, Macro, Functions and more

Set up a connection to Oracle by using metauser/metapass

Accepted Solution Solved
Reply
Regular Contributor
Posts: 207
Accepted Solution

Set up a connection to Oracle by using metauser/metapass

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


Accepted Solutions
Solution
‎04-17-2012 09:35 AM
PROC Star
Posts: 426

Re: Set up a connection to Oracle by using metauser/metapass

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


All Replies
Respected Advisor
Posts: 4,173

Re: Set up a connection to Oracle by using metauser/metapass

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

Regular Contributor
Posts: 207

Re: Set up a connection to Oracle by using metauser/metapass

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

Respected Advisor
Posts: 4,173

Re: Set up a connection to Oracle by using metauser/metapass

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

Super Contributor
Posts: 349

Re: Set up a connection to Oracle by using metauser/metapass

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

Solution
‎04-17-2012 09:35 AM
PROC Star
Posts: 426

Re: Set up a connection to Oracle by using metauser/metapass

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

Regular Contributor
Posts: 207

Re: Set up a connection to Oracle by using metauser/metapass

Posted in reply to PaulHomes

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


🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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