04-19-2017 03:50 PM
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;
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?