DefaultAuth Authentication Domain to Pass-Through from Oracle

DanZ — Wed, 19 Apr 2017 19:50:04 GMT

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?

Re: DefaultAuth Authentication Domain to Pass-Through from Oracle

LinusH — Thu, 20 Apr 2017 04:42:22 GMT

Just checking, are you logged into the metadata server in this session?

Re: DefaultAuth Authentication Domain to Pass-Through from Oracle

DanZ — Thu, 20 Apr 2017 13:07:29 GMT

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

topic Re: DefaultAuth Authentication Domain to Pass-Through from Oracle in SAS Data Management

DefaultAuth Authentication Domain to Pass-Through from Oracle

Re: DefaultAuth Authentication Domain to Pass-Through from Oracle

Re: DefaultAuth Authentication Domain to Pass-Through from Oracle