SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

DefaultAuth Authentication Domain to Pass-Through from Oracle

Reply
Contributor
Posts: 38

DefaultAuth Authentication Domain to Pass-Through from Oracle

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?

Super User
Posts: 5,257

Re: DefaultAuth Authentication Domain to Pass-Through from Oracle

Just checking, are you logged into the metadata server in this session?
Data never sleeps
Contributor
Posts: 38

Re: DefaultAuth Authentication Domain to Pass-Through from Oracle

I'm on a grid and connected to one of the nodes in this session. 

Ask a Question
Discussion stats
  • 2 replies
  • 174 views
  • 0 likes
  • 2 in conversation