Problem with SYMGET in the CONNECT Statement

[SASJedi]

When SYMGET is executing on the remote host and the macro variable was created in the local SAS session, the macro variable you are attempting to retrieve probably does not exist in the remote host symbol table. 

Check out %SYSLPUT and %SYSRPUT.  These statments allow passing macro values between local and remote host, and may help resolve your problem.

[FriedEgg]

The %syslput and %sysrput are for sharing variables between mp-connect processes.  Which I do not think the op is using?

[FriedEgg]

Sorry I forgot that one piece. During the call of &user or &pass you can replace with %sysfunc(symget(user)) etc. However all methods will still show masked data in the log if certain suboptions of sastrace are used.

options mlogic mprint macrogen symbolgen;

data _null_;

  input (uid pwd) ($);

  call symput('user',"'"||uid||"'");

  call symput('pass',"'"||pwd||"'");

  cards;

a1 abcde

;

run;

options nosymbolgen;

proc sql;

  connect to oracle user=&user /* or %sysfunc(symget(user)) */ pass=&pass /* or %sysfunc(symget(pass)) */ path=mypath;

create table want as select * from connection to oracle

  ( select *

      from mytable

     where uid=&user /* or %sysfunc(symget(user)) */ and pwd=&pass /* or %sysfunc(symget(pass)) */);

disconnect from oracle;

quit;

options symbolgen;

I do believe that any method of masking will be foiled by utilizing the sastrace option for any of it's routines that return submitted SQL statements.

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a000433982.htm

[Tom]

To solve this issue I usually encapsulate the step that uses the password inside of a macro.

That way you can turn off SYMBOLGEN, MLOGIC, MPRINT, MACROGEN while you are expanding the password.

You can store the passwords in a dataset or file and restrict access to the file using your operating system.

Something like this:

%macro oracle(passwordfile);

%local userid password optstore;

%let optstore = %sysfunc(getoption(symbolgen)) ;

options nosymbolgen ;

data _null_;

   infile "&passwordfile" ;

   input userid &$20. password &$20. ;

   call symputx('user',userid);

   call symputx('password',password);

run;

proc sql ;

   connect to oracle (user="&user" password="&password" path="mydatabase");

options &optstore;

%mend oracle;

[FriedEgg]

At the OS level just ensure that the password file is only readable by the user or group of users who are expected to utilize it, and you have as close to a secure facility as I know how to achieve for a connect statement.

[rv_sas_1990]

Hi, 

I too have a similar issue. Trying to avoid printing masked passwords in logs. It looks like authdomain= option is an effective solution since authdomain= option can be used instead of user name and password. But SAS admin needs to maintain the authentication domain in the metadata server.

it looks like this:

libname A1 saphana server=mysrv1 port=30015 authdomain="hanaauth";

Similarly in your Proc connect user & password can be replaced with authdomain =  option.

Regarding, SYMGET, Were you able to figure out how to use SYMGET in PROC CONNECT?  I think that works only with the DATA step.