BookmarkSubscribeRSS Feed
DanZ
Obsidian | Level 7

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?

2 REPLIES 2
LinusH
Tourmaline | Level 20
Just checking, are you logged into the metadata server in this session?
Data never sleeps
DanZ
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 2684 views
  • 0 likes
  • 2 in conversation