My SAS and Oracle user names/passwords are the same.
I'm trying to use the authdomain="DefaultAuth" option in a PROC SQL pass-through:
proc sql;
connect to oracle as currcon (authdomain="DefaultAuth" path="EDWPRO");
create table myoutput as
select * from connection to currcon
(SELECT * FROM mylib.myinput t);
disconnect from currcon ;
quit;
Log:
24 proc sql;
25 connect to oracle as currcon (authdomain="DefaultAuth" path="EDWPRO");
WARNING: No login information was available for authdomain DefaultAuth.
ERROR: ORACLE connection error: ORA-12162: TNS:net service name is incorrectly specified.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
26 create table mytable as
27 select * from connection to currcon
28 (SELECT * FROM mylib.myinput t);
NOTE: Statement not executed due to NOEXEC option.
29 disconnect from currcon ;
NOTE: Statement not executed due to NOEXEC option.
30 quit;
NOTE: The SAS System stopped processing this step because of errors.
It's returning with "No login information was available for authdomain DefaultAuth."
Everything I have found says to ask a SAS admin to create a new Authentication Domain and name it "OracleAuth", but this is not an easy thing to get done in my organizaion. I don't understand why I can't extract the password from DefaultAuth to connect to Oracle.
I used this code to try to pull them manually, but still can't extract the password:
%macro Auth(AuthenticationDomain);
%global Password UserID;
data _null_;
length uri UserId Password UserId AuthId AuthenticationDomain $256;
rc=metadata_getnobj("omsobj:AuthenticationDomain?@Name='&AuthenticationDomain'",1,uri);
rc=metadata_getattr(uri,"Id",AuthId);
rc=1;
tn=1;
do while(rc>0);
rc=metadata_getnasn("OMSOBJ:AuthenticationDomain\"!!AuthId,"Logins",tn,uri);
if rc>0 then do;
arc=metadata_getattr(uri,"UserID",UserID);
arc=metadata_getattr(uri,"Password",Password);
call symput("UserID",compress(UserID));
call symput("Password",compress(Password));
end;
tn=tn+1;
end;
run;
%put NOTE: For AuthenticationDomain &AuthenticationDomain., the UserID &UserID has password &Password.. Executed as &sysuserid..;
%mend;
%Auth(DefaultAuth)
The UserId comes back, but the password is coming through with "."
Do I need to invoke a system option to make this work, or can DefaultAuth flat out just not be used to log into Oracle. And if so, why does the password not return with the %Auth macro?
